CODEWITHSUNDEEP
mysql
Andris
Andris

Reputation: 1442

mysql ColumnName AS 1, ColumnName AS 2, with WHERE, using UNION for all columns get AS 1

Have table like this

IdRows | UpperLevelIdRows | CategoriesName | 
-------------------------------------------------
  2    |   0              |  Transport
  4    |   2              |  Cars
  12   |   4              |  Alfa Romeo

Query 

SELECT IdRows AS IdRows1, CategoriesName AS CategoriesName1 FROM categories 
WHERE UpperLevelIdRows = ?

UNION 

SELECT IdRows AS IdRows2, CategoriesName AS CategoriesName2 FROM categories 
WHERE UpperLevelIdRows = ?

Data for placeholders is 

Array
(
[0] => 2
[1] => 4
)

So 

 SELECT IdRows AS IdRows1 .... WHERE UpperLevelIdRows = 2

and 

 SELECT IdRows AS IdRows2 .... WHERE UpperLevelIdRows = 4

As result expect get array like

    [0] => Array
    (
        [IdRows1] => 4
        [CategoriesName1] => Cars
    )

    [1] => Array
    (
        [IdRows2] => 12
        [CategoriesName2] => Alfa Romeo
    )

But get array like this

    [0] => Array
    (
        [IdRows1] => 4
        [CategoriesName1] => Cars
    )

    [1] => Array
    (
        [IdRows1] => 12
        [CategoriesName1] => Alfa Romeo
    )

Instead of IdRows2 see IdRows1

If i execute only the second SELECT IdRows AS IdRows2 ..., then see as expected [CategoriesName2] => Alfa Romeo

Where is my mistake? What need to correct?

From the data i want to create select/option boxes. Like

First select box 

echo '<select name="upper_level_id0" id="upper_level_id0" >
<option value="'.$IdRows1.'">'.$CategoriesName1.'</option>
</select>';

Second select box

echo '<select name="upper_level_id1" id="upper_level_id1" >
<option value="'.$IdRows2.'">'.$CategoriesName2.'</option>
</select>';

At the moment found solution using transaction. Loop through all SELECT ...

$db->beginTransaction();
foreach ( $sql_get_id_name as $k_sql => $val_sql ) {
$stmt_get_id_name = $db->prepare( $val_sql );
$stmt_get_id_name->execute( array( $data_get_id_name[$k_sql] ) );
$id_name[] = $stmt_get_id_name->fetchAll(PDO::FETCH_ASSOC);
}
$roll_back = $db->rollBack();

Upvotes: 1

Views: 21

Answers (1)

Augusto
Augusto

Reputation: 1336

Use this select

select cp.IdRows p_id, cp.UpperLevelIdRows p_parent_id, cp.CategoriesName p_name, 
       cc.IdRows p_id, cc.UpperLevelIdRows c_parent_id, cc.CategoriesName c_name
from categories cc left join categories cp on cp.IdRows = cc.UpperLevelIdRows 
where cc.UpperLevelIdRows = 4

This way you will get all the sons of a parent. The first 3 columns from the result are the parent (in your row data it will be the Cars) and the second its childs (in your row data the Alfa Romeo)

Upvotes: 1

Related Questions