shibbir ahmed shibbir ahmed - 6 months ago 14
MySQL Question

How to get single row from mysql databse table using inner join query?

I have following 2 tables in mysql database :

1) products

p_id p_title p_price u_id
----------------------------------
1 tile 123 25
2 tile 133 24
3 tile 143 25


2) product_images

id img_name p_id u_id
1 name 1 25
2 name 1 25
3 name 2 24


I want to get all row from
products
table and only one row from
product_images
table. So to get this I am using following sql query but it's return all rows from
product_images
table. Should be one !

My Query :

$get_menu = mysqli_query($conn,
"SELECT DISTINCT products.p_title, products.p_price,
product_images.p_list_image, chef_profile.live_at, chef_profile.fname,
chef_profile.lname FROM products LEFT JOIN product_images ON products.p_id
= product_images.p_id LEFT JOIN chef_profile ON products.u_id =
chef_profile.u_id GROUP BY product_images.p_list_image") or die('sorry');


I am using this query in php
while loop
so that I want to show all unique data from
products
table and only one image from
product_images
table.

$num_menu = mysqli_num_rows($get_menu);
while( $get_result = mysqli_fetch_array($get_menu) ) {
/*echo '<pre>';
print_r($get_result);*/
$menu_title = htmlspecialchars($get_result['p_title']);
$menu_price = htmlspecialchars($get_result['p_price']);
$menu_image = htmlspecialchars($get_result['p_list_image']) ? htmlspecialchars($get_result['p_list_image']) : "no-menu-preview.png";
$live_at = htmlspecialchars($get_result['live_at']);
$fname = htmlspecialchars($get_result['fname']);
$lname = htmlspecialchars($get_result['lname']);

// echo..........data.. her...
}

Answer

There you have it.

You only have to group by p_id to get 1 result for each product.

SELECT DISTINCT products.p_title, products.p_price, 
product_images.p_list_image, chef_profile.live_at, chef_profile.fname,
chef_profile.lname FROM products LEFT JOIN product_images ON products.p_id
= product_images.p_id LEFT JOIN chef_profile ON products.u_id = 
chef_profile.u_id GROUP BY products.p_id

Hope it helps ;)