4 Replies - 2825 Views - Last Post: 24 January 2013 - 06:42 PM Rate Topic: -----

#1 midasxl  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 195
  • Joined: 03-December 08

Display data from two tables - one to many relationship

Posted 24 January 2013 - 05:31 PM

Hello and thanks for your time. I am not well schooled in PHP or mySQL so please bare with me.

I have two tables:

Events:
eventid
title
description
location

Dates:
eventid
eventdate
starttime
endtime

The event could have multiple dates. So in the events table I could have:

eventid:1, title:Company Seminar, description:Company Seminar, location:Headquarters

And in the dates table the related dates (indicated by the same eventid) could be:

eventid:1, eventdate:2013-01-02, starttime:04:00, endtime:05:00
eventid:1, eventdate:2013-01-03, starttime:06:00, endtime:07:00
eventid:1, eventdate:2013-01-04, starttime:04:00, endtime:07:00

What I would like to display in the browser is something like this:

Event Title: Company Seminar
Event Description: Company Seminar
Event Location: Headquarters
Dates:
Day 1: 2013-01-02
Start Time: 04:00
End Time: 05:00
Day 2: 2013-01-03
Start Time: 06:00
End Time: 07:00
Day 3: 2013-01-04
Start Time: 04:00
End Time: 07:00

Next Event from the tables..., etc.

I have tried several query combinations but nothing that has produced the above. I'm sure it's some kind of loop perhaps with an inner loop inside.

Here's an example of some of the stuff I've been trying:

$sql = "SELECT * FROM events as x LEFT JOIN dates as y on x.eventid=y.eventid ORDER BY x.eventid";
$result = mysql_query($sql);
if($result){
    $currGroup = -1;
    while($row=mysql_fetch_array($result)){
       if($row['eventid']!=$currGroup){
         $currGroup = $row['eventid'];
         echo $currGroup."\n";
       }
echo "<p>";
echo "<b>Event ID:</b> ".$row['eventid'] . "</br>";
    }
}



I could provide 3 or 4 others that didn't work either. Any help will be greatly appreciated! Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Display data from two tables - one to many relationship

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,589
  • Joined: 12-December 12

Re: Display data from two tables - one to many relationship

Posted 24 January 2013 - 05:49 PM

You are not selecting any fields from the 'dates' table:

"SELECT events.eventid, events.title, dates.eventdate, dates.starttime FROM events LEFT JOIN dates .."


[It is preferable not to use '*' anyway, particularly when you only have a limited number of fields.]

Your use of $currGroup is good though :)

THE MYSQL LIBRARY IS DEPRECATED. USE MYSQLI OR PDO.
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Display data from two tables - one to many relationship

Posted 24 January 2013 - 06:17 PM

Actually, SELECT * selects all columns from all tables in the query. If he was just selecting from the events table it would have been SELECT events.*

$sql = "SELECT * FROM events as x LEFT JOIN dates as y on x.eventid=y.eventid ORDER BY x.eventid";
$result = mysql_query($sql);
if($result)
{
    $currGroup = -1;
    while($row = mysql_fetch_array($result))
	{
       if($row['eventid'] != $currGroup)
		{
			$currGroup = $row['eventid'];
			$day = 1;
			echo 'Event Title: ' . $row['title'] . '\n';
			echo 'Event Description: ' . $row['description'] . '\n';
			echo 'Event Location: ' . $row['location'] . '\n';
			echo 'Dates:\n';
		}
		echo 'Day ' . $day . ': ' . $row['eventdate'];
		echo 'Start Time: ' . $row['starttime'];
		echo 'End Time: ' . $row['endtime'];
		$day++;
    }
}



That code would work, but you might want to look into your SQL query a little. Selecting all the rows is generally a very bad idea, and there are numerous reasons for this. Firstly, if you are only using 3 rows, but selecting say 8, then you are unnecessarily incurring extra work for the SQL server and for PHP. Secondly, when two columns have the same name, such as events.eventid and dates.eventid, then the latter clobbers the former. This generally isn't much of a problam, but in this case it probably is. Because you have used a LEFT JOIN, then if there are no dates, the dates.eventid field will be null, meaning that you'll get an incorrect value when comparing with if($row['eventid'] != $currGroup). I'll leave that for you to fix though.

This post has been edited by e_i_pi: 24 January 2013 - 06:18 PM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3838
  • View blog
  • Posts: 13,589
  • Joined: 12-December 12

Re: Display data from two tables - one to many relationship

Posted 24 January 2013 - 06:20 PM

Quote

Actually, SELECT * selects all columns from all tables in the query. If he was just selecting from the events table it would have been SELECT events.*


Oops! Sorry, I missed that. Andy.
Was This Post Helpful? 0
  • +
  • -

#5 midasxl  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 195
  • Joined: 03-December 08

Re: Display data from two tables - one to many relationship

Posted 24 January 2013 - 06:42 PM

Thanks for the suggestions, I have put them to use and have come up with the following. I changed/added a couple of column names and re-named the table names. Hope that doesn't confuse viewers in relation to the earlier posts.

$sql = "SELECT x.eventid, title, discipline, body, eventlocation, date, imagepath, eventdate, starttime, endtime FROM cae_events_info as x INNER JOIN cae_event_dates as y on x.eventid=y.eventid ORDER BY x.eventid";
$result = mysql_query($sql);
if($result)
{
    $currGroup = -1;
    while($row = mysql_fetch_array($result))
	{
       if($row['eventid'] != $currGroup)
		{
		echo '<hr>';
			$currGroup = $row['eventid'];
			$day = 1;
			echo 'Event ID: ' . $row['eventid'] . "</br>";
			echo 'Event Title: ' . $row['title'] . "</br>";
			echo 'Discipline: ' . $row['discipline'] . "</br>";
			echo 'Event Description: ' . $row['body'] . "</br>";
			echo 'Event Location: ' . $row['eventlocation'] . "</br>";
			echo 'Date Added: ' . $row['date'] . "</br>";
			echo 'Image Path: ' . $row['imagepath'] . "</br>";
			echo 'Dates: </br>';
		}
		echo 'Date ' . $day . ': ' . $row['eventdate'] . "</br>";
		echo 'Start Time: ' . $row['starttime'] . "</br>";
		echo 'End Time: ' . $row['endtime'] . "</br>";
		$day++;
    }
}



With the two test events currently in the database, the above produces the following:

Event ID: 18354
Event Title: x
Discipline: x
Event Description: x
Event Location: x
Date Added: 2013-01-25
Image Path: event_images/exampleGIF.gif
Dates:
Date 1: 2013-01-06
Start Time: 02:00:00
End Time: 02:30:00
Date 2: 2013-01-05
Start Time: 01:00:00
End Time: 01:30:00
Date 3: 2013-01-04
Start Time: 12:00:00
End Time: 12:30:00

Event ID: 22185
Event Title: t
Discipline: t
Event Description: t
Event Location: t
Date Added: 2013-01-25
Image Path: event_images/exampleGIF.gif
Dates:
Date 1: 2013-01-26
Start Time: 01:00:00
End Time: 02:00:00

This is great! A very promising start. I now have to work on formatting for display in the browser. Within the multi-date event I would like to order the dates properly. Right now they're listed in reverse order.

If anyone sees room for improvement, I will welcome more suggestions.

Thank you all!

Cheers!

This post has been edited by midasxl: 25 January 2013 - 08:11 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1