Portal Home > Knowledgebase > Articles Database > Mysql question
Mysql question
Posted by bmicomp, 08-07-2008, 01:09 PM |
I'm trying to join multiple corresponding rows in one column from a related table into one cell in my output. Does anybody know how to do this without multiple select statements?
Products
==================================
|| ID || Title || Description ||
==================================
|| 1 || July Mailer || Premium Clients ||
|| 2 || Promotional Box || With widget inside ||
====================================
Components
========================================================
|| ID || Prod_ID || Description || Category ||
========================================================
|| 1 || 1 || 4-pg Introductory Brochure || Brochure ||
|| 2 || 1 || Outer Mailing Envelope || Envelope ||
|| 3 || 2 || Tuck Box || Box ||
|| 4 || 2 || Inner Widget || Widget ||
=========================================================
To be displayed as such
===============================================
|| Title || Description || Categories ||
===============================================
|| July Mailer || Premium Clients || Brochure, Envelope ||
|| Promotional Box || With widget inside || Box, Widget ||
===============================================
The only way I can think of doing this is having a nested select statement... but that would seem like a lot of sql queries.
$query1 = "SELECT title, description FROM products"
$mysql = mysql_query($query1);
while ($row assigned)
{
$row["title"] | $row["description"] |
$query2 = "SELECT category FROM components WHERE prod_id = '$row[id]"
$mysql = mysql_query($query2);
while ($row2 assigned)
{
echo $row2["category"]
}
|
}
|
Posted by qbert220, 08-07-2008, 02:10 PM |
This will give you:
|| ID || Title || Description || Category||
|| 1 || July Mailer || Premium Clients || Brochure ||
|| 1 || July Mailer || Premium Clients || Envelope ||
|| 2 || Promotional Box || With widget inside || Box
|| 2 || Promotional Box || With widget inside || Widget ||
From this you could iterate over the results. If ID changes, then print the Title and description if ID changes.
|
Add to Favourites Print this Article
Also Read
BetterLinux? (Views: 620)