Select in two mysql tables, Concat rows from 2nd table

mluci12
Posts: 39

Select in two mysql tables, Concat rows from 2nd table

I use mysql Inner join with php for a select from two data tables.But the resuts are duplicated because in one table i have more results with that id.

Code: Select all

$sql2 = "SELECT `products`.`id` , `products`.`id_user` , `products`.`name` , `products`.`description` , `products`.`cantitate` , `products`.`data_expirare`,`products`.`data_cumparare`, `upload_data`.`FILE_NAME`,`upload_data`.`product_id` FROM `products` , `upload_data` where `products`.`id`=`upload_data`.`product_id` AND `products`.`id_user` = '$id' "; $result2 = $conn->query($sql2); if ($result2->num_rows > 0) { while($row2 = $result2->fetch_assoc()) {
this is my mysql select.but in upload_data i have three file name with that id.And i get tree results returned.How can i foreach this values?or any other thing?

Admin
Hello,
What data are in the results set, and what data do you want to get?
What values do you want to foreach, or how do you want the final result?

mluci12
I to put all results Into a table, but i have multiple records in upload_data with product id, and i get :

Code: Select all

Name||description||cantitate||files Cisco||a good switch||5||1.pdf Cisco||a good switch||5||12.pdf
But i want:

Code: Select all

Cisco||a good switch||5||1.pdf,12.pdf

Admin
Try use mysql GROUP_CONCAT():

Code: Select all

$sql2 ="SELECT t1.id, t1.id_user, t1.name, t1.description, t1.cantitate, t1.data_expirare, t1.data_cumparare, GROUP_CONCAT(t2.FILE_NAME) AS file FROM products AS t1, upload_data AS t2 WHERE t1.id=t2.product_id AND t1.id_user = '$id'";

mluci12
Results are returned as a array? If are returned as aa aray what is the name?

mluci12
I tras în google and group_concat is for file.but în database i have a string not a file."1.pdf" is the name of file uploaded on server

Admin
The group_concat() is not for file, it is a mysql function that returns a string with concatenated non-NULL value from a group of rows (separated by ',').
More details and examples you can find to this page: MySQL GROUP_CONCAT() function

- Test the sql in phpmyadmin and see the results.

Similar Topics