MySQL Query is overwriting elements in array

Question

My SQL-Query is overwriting elements with the same name. But the names a coming from different tables and have different name, e.g. [articles][name], [companies][name].

I have these tables.

1.) Shop_Articles_Category_Allocation 2.) Portfolio 3.) Articles 4.) Companies 5.) Category

Example:

Shop_Category_Articles_Allocation
   id      | portfolio_id  | article_id
   Primary   To Protfolio    Articles

Portfolio
id | company_id | ....
     To Company

Company
id | name | ...

Articles
id | name | ...

And my Code:

        $em = $this->getDoctrine()->getManager();
        $sql =  " 
            SELECT *
            FROM shop_cat_art_alloc scaa 
            INNER JOIN portfolio p ON scaa.article_id = p.article_id
            INNER JOIN companies c ON p.company_id = c.id
            INNER JOIN articles a ON scaa.article_id = a.id


            WHERE scaa.shop_cat_id IN ($cat_id )
            AND a.state_id = (:state_id)
            AND p.state_id = (:state_id)
            AND scaa.state_id = (:state_id)
            AND c.state_id = (:state_id)
        ";

        $stmt = $em->getConnection()->prepare($sql);
        $stmt->bindParam(':state_id', $state_id);
        $stmt->execute();
        $result_articles = $stmt->fetchAll();

Now I get a result that is positive but in this case the name of the company is overwritten in the array. I get an array like this:

[id] => 1 [article_id] => 1 [name] => Chucks //this is the article name but there is no company name anymore.

How can I make the query that I can get to the company name too and still be able to get to the article name?

Thanks!

c3cris · Accepted Answer · 2014-06-04 17:59:47Z

1

Use aliases, substitute the wild card * and name your columns and give ones that collide aliases.

select user.name as username, account.name as accountname 
from table

More info on Aliases : http://dev.mysql.com/doc/refman/4.1/en/identifiers.html

answered Jun 4, 2014 at 17:59

c3cris

1,3263 gold badges16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query is overwriting elements in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related