omahakid Posted March 8, 2006 Share Posted March 8, 2006 I have three MySQL tables, one contains meeting information, one contains meeting topics, and the other contains a list of locations. The structure is as follows:tblTopic tblMeeting tblLocation topicid pk meetid pk locationid pktopic date location meetid fk time city locationid fk state zipThe problem is that each meeting could have multiple topics so when I query the database using PHP for the date, time, location, city, state, zip, and topic I might get multiple rows that are identical except for the topics for instance:2005-06-28 12:00:00 FNB Business Park14010 FNB ParkwayOmaha NE Gray Box Testing AND2005-06-28 12:00:00 FNB Business Park14010 FNB ParkwayOmaha NE Officer ElectionsI separating this into tables so that it looks like this:---------------------------------------------------------------------------------| date | time | where | topics | --------------------------------------------------------------------------------- |2005-06-28 | 12:00:00 | 14010 FNB Parkway | Officer Elections || | |Omaha NE | Gray Box Testing |-----------------------------------------------------------------------------------This is my code:<td width="640" valign="top" ><table frame="hsides" class="meettable" width="640" border="1" cellspacing="0" cellpadding="5" valign="top" margin="5"><tr><td colspan="4" align="center" class="meettitle"><br><b>Future Meetings</b><br><br></td></tr><tr bgcolor="#bfbdbd" class="meettable"><th class="meetheader">Date</th><th class="meetheader">Time</th><th class="meetheader">Where</th><th class="meetheader">Topics</th></tr><?mysql_connect($dbhost,$username,$password);@mysql_select_db($database) or die( "Unable to select database");$query="SELECT date, time, address, location, city, state, topic FROM tblmeeting m LEFT OUTER JOIN tbltopic t ON m.meetid = t.meetid INNER JOIN tbllocation l ON l.locationid = m.locationid WHERE date >= sysdate()";$result=mysql_query($query);$num=mysql_numrows($result);mysql_close();$i=0;while ($i < $num) {$date=mysql_result($result,$i,"date");$time=mysql_result($result,$i,"time");$location=mysql_result($result,$i,"location");$address=mysql_result($result,$i,"address");$city=mysql_result($result,$i,"city");$state=mysql_result($result,$i,"state");$topics=mysql_result($result,$i,"topic");?><tr class="intable" valign="top"><td class="text"><?echo $date;?></td><td class="text"><?echo $time;?></td><td class="text"><?echo $location;?><br><?echo $address;?><br><?echo $city;?> <?echo $state;?></td><td class="text"><?echo $topics;?></td></tr><?$i++;}?></table>Any suggestions? Quote Link to comment Share on other sites More sharing options...
keeB Posted March 8, 2006 Share Posted March 8, 2006 Ok, you took the right approach, and you're definately there. Now it's just a matter of finesse. Basically you have redundant data right? And you want to format your output so it's likeMeeting 1; Topics: list of topics;Location:You already have the meetingId in the table, so I am not really understanding what the problem is?Eventhough you have redundant data in your output.. doing a simple query can just get you the topics for a given meeting and write a function that returns it as an array, print all of them in a row.. make sense? Quote Link to comment Share on other sites More sharing options...
omahakid Posted March 8, 2006 Author Share Posted March 8, 2006 Thanks for the post, that doesn't sound too difficult: I tried something else previously with little luck:mysql_connect($dbhost,$username,$password);@mysql_select_db($database) or die( "Unable to select database");$query="SELECT m.meetid, m.date, m.time, l.address, l.location, l.city, l.state FROM tblmeeting m INNER JOIN tbllocation l ON l.locationid = m.locationid WHERE m.date >= sysdate()";$result=mysql_query($query);mysql_close();$num=mysql_numrows($result);$i=0;while ($i < $num) {$meetid=mysql_result($result,$i,"m.meetid");$date=mysql_result($result,$i,"m.date");$time=mysql_result($result,$i,"m.time");$location=mysql_result($result,$i,"l.location");$address=mysql_result($result,$i,"l.address");$city=mysql_result($result,$i,"l.city");$state=mysql_result($result,$i,"l.state"); ?><tr class="intable" valign="top"><td class="text"><?echo $date;?></td><td class="text"><?echo $time;?></td><td class="text"><?echo $location;?><br><?echo $address;?><br><?echo $city;?> <?echo $state;?></td><td class="text"><?mysql_connect($dbhost,$username,$password);@mysql_select_db($database) or die( "Unable to select database");$query1="SELECT t.topic FROM tbltopic t WHERE t.meetid = $meetid";$result1=mysql_query($query);$num1=mysql_numrows($result1);$j=0;while ($j < $num1) {$topics=mysql_result($result1,$j,"t.topic");mysql_close();echo $topics;?><br></td></tr><? $j++; }$i++;}?>So I'm thinking that based upon what you said that the second query that I wrote specifically for the topics column could be put into a function? or should I scrap the second query bit and go a different direction with a function? Quote Link to comment Share on other sites More sharing options...
keeB Posted March 8, 2006 Share Posted March 8, 2006 [code] [b]Basic SQL Contruct[/b]select t.topic from topics tinner join meetings m on m.meetid = t.meetidwhere m.meetid = 1[/code]This will get you all topics for meeting #1.Write a function which then loops through the results, and echo's each row.. or put's it in an array (reusable)Good luck ;) Quote Link to comment Share on other sites More sharing options...
omahakid Posted March 8, 2006 Author Share Posted March 8, 2006 Thanks for your help. Quote Link to comment Share on other sites More sharing options...
keeB Posted March 8, 2006 Share Posted March 8, 2006 [!--quoteo(post=353005:date=Mar 8 2006, 09:28 PM:name=omahakid)--][div class=\'quotetop\']QUOTE(omahakid @ Mar 8 2006, 09:28 PM) [snapback]353005[/snapback][/div][div class=\'quotemain\'][!--quotec--]Thanks for your help.[/quote]Of course, anytime!! :D Quote Link to comment 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.