SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  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,446
    Mentioned
    45 Post(s)
    Tagged
    13 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
    772
    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
    772
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Taking out the WHERE condition, do you get all records? Are they shown by time?


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
  •