Php pdo result of JOIN query

Question

Hello,

My problem is

I try to do a simple JOIN between two tables, that both have the id field. My result is an stdClass object, as I use PDO. Does anyone know how can I make a difference between the id of the first table and the id of the second table?

Code

bc. $sql = "SELECT *, FROM products AS p, products_categories as c WHERE c.id = p.category";

 $stmt = $__CONN__->prepare($sql);
 $stmt->execute();

 $products = array();

 while($product = $stmt->fetchObject())
  $products[] = $product;

 return $products;

If I try to use $products->id, it will show me the id of the category table. If it was an array, I could use $products['p.id'] , I need an alternative to this.

Thanks a lot.

Answer 1

You have to give a column alias to the id column from one table or the other to make the column names distinct.

This means you can't use "SELECT *", you have to spell out all the column names you want to fetch from that table. At least you can still query for category.* if you want all those columns without aliasing.

By the way, this sort of problem is a good reason to avoid using a generic name like "id" for your primary key in every table. Instead use a more descriptive names like product_id and category_id. Then you won't have the problem of conflicting column names.