Jump to content

MySQL Query Results


omahakid

Recommended Posts

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 pk
topic date location
meetid fk time city
locationid fk state
zip

The 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 Park
14010 FNB Parkway
Omaha NE Gray Box Testing

AND

2005-06-28 12:00:00 FNB Business Park
14010 FNB Parkway
Omaha NE Officer Elections

I 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;?>&nbsp;<?echo $state;?></td>
<td class="text"><?echo $topics;?></td>
</tr>

<?
$i++;
}
?>

</table>

Any suggestions?
Link to comment
Share on other sites

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 like

Meeting 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?
Link to comment
Share on other sites

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;?>&nbsp;<?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?
Link to comment
Share on other sites

[code]

[b]Basic SQL Contruct[/b]
select t.topic from topics t
inner join meetings m on m.meetid = t.meetid
where 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 ;)
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.