CODEWITHSUNDEEP
phpsqljoinobjectpdo
BebliucGeorge
BebliucGeorge

Reputation: 751

Php pdo result of JOIN query

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

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

$stmt = $connection->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.

Upvotes: 2

Views: 12103

Answers (3)

Somnath Das
Somnath Das

Reputation: 64

$stmt = $db->prepare("SELECT c.car_id, c.car_name, d.dealers_name,m.maker_name
FROM car_details AS c, dealer_details AS d,maker_details AS m
WHERE c.dealer_id=d.dealer_id AND c.maker_id=m.maker_id");

$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<pre>';
print_r($rows);

Upvotes: 2

Laura
Laura

Reputation: 1

I used

select 
    clientes.nombre AS cn, 
    proyectos.nombre AS pn 
FROM 
    proyectos 
        LEFT JOIN clientes 
            ON proyectos.clienteId = clientes.id

This worked with PDO using fetch(PDO::FETCH_ASSOC)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562270

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.

Upvotes: 1

Related Questions