tristanoneil Posted January 22, 2010 Share Posted January 22, 2010 I'm trying to figure out how to list a whole bunch of fields from one MySQL database using a while loop and within in list all of the fields in another table that are associated with the main table, they are associated with each other via a unique id. So this is how I want the hierarchy to go. Main Table Content -> Associated Content -> Associated Content The trick is that there are multiple associated entries for each main entry. I've tried it using a left join, but it lists the main content multiple times not once with all of the associated content underneath. What is the best way to go about something like this. This is what my code looks like right now. <html> <head> <title>Ad List</title> <style type="text/css" media="all">@import "style.css";</style> </head> <body> <?php include 'database.inc.php'; $query = "SELECT * FROM ads LEFT JOIN notes ON ads.id = notes.aid"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)) { echo '<div id="container">'; echo '<h2>'.$row['advertiser'].'</h2>'; echo '<p>'.$row['salesExecutive'].'</p>'; echo '<form action="notes.php" method="post">'; echo '<input type="hidden" name="aid" value="'.$row['id'].'">'; echo '<label for="notes">Notes</label> <br />'; echo '<textarea name="note" rows="2" cols="100">'; echo '</textarea><br /><br />'; echo '<input value="Submit" type="submit">'; echo '</form>'; echo '<div id="notes">'.$row['note'].'</div>'; echo '</div>'; } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/189465-sql-join/ Share on other sites More sharing options...
jl5501 Posted January 22, 2010 Share Posted January 22, 2010 You cannot do what you want, with a single query, as all the rows from on query will have to have the same structure, and therefore you get the main table content in each row. This is a situation where you are best of with 2 queries, where with the first you are looping over the main table entries, and for each iteration you do a query on the notes table with the appropriate id in the where clause, and then iterate over those results before continuing with the main loop. Quote Link to comment https://forums.phpfreaks.com/topic/189465-sql-join/#findComment-1000164 Share on other sites More sharing options...
sasa Posted January 23, 2010 Share Posted January 23, 2010 try <html> <head> <title>Ad List</title> <style type="text/css" media="all">@import "style.css";</style> </head> <body> <?php include 'database.inc.php'; $query = "SELECT *, GROUP_CONCAT(`note` SEPARATOR '</div><div id=\"notes\">') AS note1 FROM ads LEFT JOIN notes ON ads.id = notes.aid GROUP BY ads.id"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)) { echo '<div id="container">'; echo '<h2>'.$row['advertiser'].'</h2>'; echo '<p>'.$row['salesExecutive'].'</p>'; echo '<form action="notes.php" method="post">'; echo '<input type="hidden" name="aid" value="'.$row['id'].'">'; echo '<label for="notes">Notes</label> <br />'; echo '<textarea name="note" rows="2" cols="100">'; echo '</textarea><br /><br />'; echo '<input value="Submit" type="submit">'; echo '</form>'; echo '<div id="notes">'.$row['note1'].'</div>'; echo '</div>'; } ?> </body> </html> btw in HTML id attribute is unique Quote Link to comment https://forums.phpfreaks.com/topic/189465-sql-join/#findComment-1000325 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.