SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join three tables and display info form all three

    I am just learning PHP for fun and am trying to join and display info from three tables.
    Three tables are set like this
    Code:
    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

    PHP Code:
    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. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,414
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    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 mysqli or PDO.

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    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 mysqli or PDO.

    Thanks fretburner!

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

    PHP Code:
    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
    Code:
    appointmentId: 1
    dogId: 1
    clientId: 1
    start: 19:00:00
    month: 07
    day: 13
    year: 2103

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    748
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?php
        $num_rows 
    $result->num_rows;
        
    $plural = ($num_rows == "1" '' "s");
     echo 
    "$num_rows appointment$plural scheduled for today.<br />";
    ?>

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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. #6
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    748
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Taking out the WHERE condition, do you get all records? Are they shown by time?

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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. #8
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    748
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dxm31 View Post
    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

    Code:
    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?
    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. #9
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    $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
    Code:
    $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. #10
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    748
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Try
    date = '$today'

    Otherwise it will be treated as math 2013minus07minus13

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #12
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    748
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Cool. Happy coding.

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #14
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,414
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    You could do something like this:
    PHP Code:
    $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. #15
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #16
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,414
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by dxm31 View Post
    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"?
    Almost - DATETIME is YYYY-MM-DD HH:MM:SS.
    Your DB query wouldn't change that much. If appointments.start was a DATETIME column:
    Code MySQL:
    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:
    PHP Code:
    $date = new DateTime($row['start']);
    echo 
    $date->format('g:i A'); 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •