Join three tables and display info form all three

PHP
1

I am just learning PHP for fun and am trying to join and display info from three tables.
Three tables are set like this


Client
     clientId
     firstName
     lastName
     phone
     email

dog
     dogId
     name
     behavior
     sex
     age

appointments
     appointmentId
     dogId
     clientId
     start
     month
     day
     year

I’m wanting to make like a day at a glance type deal, on my main admin page. And am trying to pull the info like this


include("include/config.php");
date_default_timezone_set('America/Denver');
	$m = date("m");
	$d = date("d");
	$y = date("Y");
$result = mysql_query("SELECT client.lastName, dog.name, appointments.start FROM appointments INNER JOIN client ON appointments.clientId = client.clientId INNER JOIN dog ON appointments.dogId = dog.dogId WHERE appointments.mounth= $m AND appointments.day = $d AND appointments.year = $y ORDER BY apointments.start ASC");
$num_rows = mysql_num_rows($result);
	if($num_rows == "0") {
 echo "$num_rows appointments scheduled for today.<br />";
 }
	elseif($num_rows == "1"){
	echo "$num_rows appointment scheduled for today.<br />";
	}
	else{
	echo "$num_rows appointments scheduled for today.<br />";
	}
	
		if($num_rows > "0") {
		
		echo '<table width="400">
				<tr><th>Client</th><th>Dog</th><th>Start</th><th>Select</th></tr>';
		while($row = mysql_fetch_array($result)){
		echo '<tr><td>' . $row['lastName'] .	 '</td><td>' . $row['name'] . '</td><td>' . $row['start'] . '</td><td><a href="viewappointment.php?id=' . $row['appointmentId'] . '">View</a></td></tr>';
		
		
		}
		echo '</table>';
		}

Any help would be awesome. Thanks.

2

Hi,

I notice you’ve got a couple typos in your SQL. The column month is misspelt in your WHERE clause and appointments in your ORDER BY clause.

Be aware the mysql extension has been depreciated and is going to be removed from PHP. You’d be better off switching to [fphp]mysqli[/fphp] or [fphp]PDO[/fphp].

3

Thanks fretburner!

I corrected the spelling errors and switched to mysqli. My php code is now


include("include/config.php");
date_default_timezone_set('America/Denver');
	$m = date("m");
	$d = date("d");
	$y = date("Y");
	
	$sql = <<<SQL
    SELECT client.lastName, dog.name, appointments.start FROM appointments INNER JOIN client ON appointments.clientId = client.clientId INNER JOIN dog ON appointments.dogId = dog.dogId WHERE appointments.month = $m AND appointments.day = $d AND appointments.year = $y ORDER BY appointments.start ASC 
SQL;

if(!$result = $db->query($sql)){
    die('There was an error while running the query [' . $db->error . ']');
}
	
	$num_rows = $result->num_rows;
	if($num_rows == "0") {
 echo "$num_rows appointments scheduled for today.<br />";
 }
	elseif($num_rows == "1"){
	echo "$num_rows appointment scheduled for today.<br />";
	}
	else{
	echo "$num_rows appointments scheduled for today.<br />";
	}
	
		if($num_rows > "0") {
		
		echo '<table width="400">
				<tr>
					<th>Client</th>
					<th>Dog</th>
					<th>Start</th>
					<th>Select</th>
				</tr>';
		while($row = $result ->fetch_assoc()){
		echo '<tr><td>' . $row['lastName'] .	 '</td><td>' . $row['name'] . '</td><td>' . $row['start'] . '</td><td><a href="viewappointment.php?id=' . $row['appointmentId'] . '">View</a></td></tr>';
		
		
		}
		echo '</table>';
		}
?>

I am no longer getting any errors and have tested that by inserting a typo in the query to see if it catches it, it does. event though I have a couple rows of “appointments” in the appointments table. The result of the query is still zero.

Appointments table


appointmentId: 1
dogId: 1
clientId: 1
start: 19:00:00
month: 07
day: 13
year: 2103
4

Might I suggest LEFT JOIN on client and dog instead as I believe inner join will require all info. You should see any appointments, then if client name, dog id etc is available it will be shown.

///
You can also shorten that if/else section.

<?php
    $num_rows = $result->num_rows;
	$plural = ($num_rows == "1" ? '' : "s");
 echo "$num_rows appointment$plural scheduled for today.<br />";
?>
5

Thank you Drummin! That solved most of it and I am now seeing one of the appointments for the day. However when I add more appointments for the day it only pulls one of them and it is not in the correct order. If I have one that starts at say 19:00:00 and one that starts at like 10:30:00 or something it still only pulls the one at 19:00:00 and doesn’t even count the one that starts at 10:00:00. I’ll post the code as I have it now below.


<?php
include("include/config.php");
date_default_timezone_set('America/Denver');
	$m = date("m");
	$d = date("d");
	$y = date("Y");
	
	$sql = <<<SQL
    SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId LEFT JOIN dog ON appointments.dogId = dog.dogId WHERE appointments.month = $m AND appointments.day = $d AND appointments.year = $y ORDER BY appointments.start ASC 
SQL;

if(!$result = $db->query($sql)){
    die('There was an error while running the query [' . $db->error . ']');
}
	
	 $num_rows = $result->num_rows; 
    $plural = ($num_rows == "1" ? '' : "s"); 
 echo "$num_rows appointment$plural scheduled for today.<br />";
	
		if($num_rows > "0") {
		
		echo '<table width="400">
				<tr>
					<th>Client</th>
					<th>Dog</th>
					<th>Start</th>
					<th>Select</th>
				</tr>';
		while($row = $result ->fetch_assoc()){
		echo '<tr><td>' . $row['lastName'] . ', ' . $row['firstName'] .	 '</td><td>' . $row['name'] . '</td><td>' . $row['start'] . '</td><td><a href="viewappointment.php?id=' . $row['appointmentId'] . '">View</a></td></tr>';
		
		
		}
		echo '</table>';
		}
?>

I’m thinking it has to do with my query still but not sure.

6

Taking out the WHERE condition, do you get all records? Are they shown by time?

7

Yes thanks again Drummin that works and I get all of the appropriate records in the right order.

Right now I have my appointments table set-up like this


appointmentId    int(11), PK, AI
dogId               int
clientId            int
start               time
month             varchar(2)
day                varchar(2)
year               varchar(4)

At first I tried just having a ‘date’ column and having that indexed as ‘date’ which I thought was formatted like ‘YYYY-MM-DD’ but i was never able to pull any records that way when I set a php variable to something like

$today = date(“Y-m-d”);

Even before I tried to JOIN tables. So I changed my table to how it is now. Is there an easier way to store a date in a table?

8

I would stick with date for your table, ‘YYYY-MM-DD’.

Make sure you define date called in the same format.
$today = date(‘Y-m-d’);

You were probably storing month as ‘7’ instead of ‘07’ or something… don’t know.

9

Thanks for you continued help Drummin. Yes that is one of the of the few things I thought was happening as well. I have double checked and looked at the entries with mysql workbench and all the dates are correct like ‘2013-07-13’ when I try using the variable $today = date(‘Y-m-d’); in my query


$today = date('Y-m-d');
$sql = <<<SQL
    SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId LEFT JOIN dog ON appointments.dogId = dog.dogId WHERE date = $today ORDER BY appointments.start ASC
SQL;

I pull zero records when I remove the where condition I still get all records sorted by start time, even if it doesn’t have the same date as today on the record. I have been messing around with it and have finally getting the right results using this method


$m = date("m");
	$d = date("d");
	$y = date("Y");
	
	$sql = <<<SQL
    SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId LEFT JOIN dog ON appointments.dogId = dog.dogId WHERE appointments.month = $m AND appointments.day = $d AND appointments.year = $y ORDER BY appointments.start ASC
SQL;

but I would much rather have the date stored in a single column using DATE instead of the three individual columns for month, day, year.

10

Try
date = ‘$today’

Otherwise it will be treated as math 2013minus07minus13

11

Thanks for everything Drummin! Adding the ’ ’ to $today fixed it. All is working the way I want and have learned allot, and that is all I am wanting out of this script. This is the first time using mysqli and joining tables and actually look forward to switching all of my previous mysql query’s over to mysqli in the rest of the project’s scripts.

12

Cool. Happy coding.

13

Looking for on other hopefully quick fix. I store time in the table using the TIME index (19:00:00) How can I convert that to 7:00 PM when I display the start time?

14

You could do something like this:


$date = DateTime::createFromFormat('H:i:s', $row['start']);
echo $date->format('g:i A');

Is there a particular reason you’re storing all the data and time segments as separate columns, rather than using a DATETIME column?

15

No not really I havent done much where I’m trying to pull a specific date then organize by time. I’m quessing DATETIME is formatted like “YYYY-MM-DD HH-MM-SS” right? how would I have if search the table form “YYYY-MM-DD” first then by “HH-MM-SS”?

16

Almost - DATETIME is YYYY-MM-DD HH:MM:SS.
Your DB query wouldn’t change that much. If appointments.start was a DATETIME column:

SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId
FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId
LEFT JOIN dog ON appointments.dogId = dog.dogId 
WHERE appointments.start > CURDATE()
ORDER BY appointments.start ASC

You can then format any part of the date or time using the DateTime object:


$date = new DateTime($row['start']);
echo $date->format('g:i A');