CODEWITHSUNDEEP
phpmysql
IseNgaRt
IseNgaRt

Reputation: 609

Can't select both 2 fields from 2 different tables with the same name

$query = 'SELECT * 
          FROM #__virtuemart_products AS a 
          LEFT JOIN #__virtuemart_products_en_gb as b ON a.virtuemart_product_id = b.virtuemart_product_id
          INNER JOIN #__virtuemart_product_categories as c ON a.virtuemart_product_id=c.virtuemart_product_id
          INNER JOIN #__virtuemart_categories_en_gb as d ON c.virtuemart_category_id = d.virtuemart_category_id 
          WHERE b.slug LIKE "'.$current.'%" 
            AND a.product_parent_id = 0
            AND d.category_name="'.$query_title.'"' ;

$db->setQuery($query);
$options=$db->loadObjectList();

This is the query I use to parse some products from my database.

The problem is:

Table virtuemart_products_en_gb has a column named slug, and table virtuemart_categories_en_gb also has a column named slug.

When I used $row->slug, it parsed the virtuemart_categories_en_gb.slug column.

So after I var_dumped the ObjectList, I see that there is only 1 column named slug. After I used the same query in phpmyadmin, it returns 2 columns named slug.

I think I could fix that selecting every single record individual and setting first slug as slug1 and second as slug2.

For example:

SELECT 
    id, username, password, 
    b.slug as slug1, c.slug as slug2

etc. Is there any better way? Because I need to parse really many columns, and that would make the query really huge.

And why the php query returns only 1 field named slug while phpmyadmin returns both ?

Thanks in advance.

Upvotes: 0

Views: 43

Answers (1)

Raging Bull
Raging Bull

Reputation: 18747

You can specify * after selecting the columns with the same name. For example,

SELECT *, b.slug as slug1,c.slug as slug2
FROM #__virtuemart_products as a 
LEFT JOIN #__virtuemart_products_en_gb as b ON a.virtuemart_product_id = b.virtuemart_product_id
INNER JOIN #__virtuemart_product_categories as c ON a.virtuemart_product_id=c.virtuemart_product_id
INNER JOIN #__virtuemart_categories_en_gb as d ON c.virtuemart_category_id = d.virtuemart_category_id 
WHERE b.slug LIKE "'.$current.'%" AND a.product_parent_id = 0
AND d.category_name="'.$query_title.'"

Upvotes: 1

Related Questions