PHP/MySQL people, help needed with query

Discussion in 'OT Technology' started by Dustro, Nov 7, 2005.

  1. Dustro

    Dustro New Member

    Joined:
    Nov 1, 2003
    Messages:
    2,123
    Likes Received:
    0
    Location:
    NC
    Ok im trying to make a query that pulls data from a scheduling program I installed. I have no problem pulling the data from a single table based on a date that is selected and matched to the rows. Im running into a problem with how to match up a field in that table to another table, then a field in that table to another table to actually get the name of the person who made the reservation. Here is a breakdown of what I have

    table im running the query on : reservations
    field I need to match up with 2nd table : resid
    2nd table : reservation_users
    field I need to match up with the 3rd table : memberid
    3rd table : login
    finally fields i need from 3rd table : fname, lname, phone

    anyone care to share knowldge on how to execute this? I'm fairly new to most of this and tend to learn by finding code that does what I want and figuring out how it works for next time.

    here is the code that im using currently, im sure its crappy because its adapted from a howto site
    Code:
    <?
        $usr = "*****";
        $pwd = "*********";
        $db = "*********";
        $host = "*********";
    
        # connect to database
        $cid = mysql_connect($host,$usr,$pwd);
        if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }
    
    ?>
    
    
    
    
       <?
        $category = $bdate;
    
        # setup SQL statement
        $SQL = " SELECT * FROM reservations ";
        $SQL = $SQL . " WHERE start_date = '$category' ";
    
    
    
    
    
        # execute SQL statement
        $retid = mysql_db_query($db, $SQL, $cid);
    
    
    
        # check for errors
        if (!$retid) { echo( mysql_error()); }
        else {
    
            echo ("<table Border=1></P>");
            echo("<tr bgcolor=c0c0c0><th>Date</th><th>Room</th><th>Start Time</th><th>End Time</th><th>Package</th><th>Additional Info</th></tr>");
    
            # display results
            
            while ($row = mysql_fetch_array($retid)) {
                                                    $date = $row["start_date"];
                                        $start = $row["startTime"];
                                        $end = $row["endTime"];
                                                    $package = $row["environment"];
                                                    $machid = $row["machid"];
                                                    $summary = $row["summary"];
                                                    $resid = $row["resid"];
    
                            
                                                    $date2 = date('m-d-Y', $date);
                                                    $start2 = $start / 60;
                                                    $end2 = $end /60;
    
    
    
    
    
    
    
       if ($machid == sc142fd3e0c27111) {
           $room = "room 1";}
       elseif ($machid == sc14301523f55741){
           $room = "room 2";}
       else{
           $room = error;}
    
    
    
    
    
                
                                                    echo ("<tr>\n");
                           echo ("<th>$date2 </th>\n");
                                                    echo ("<td>$room</td>\n");
                echo ("<td>$start2</td>\n");
                                                    echo ("<td>$end2</td>\n");
                                                    echo ("<td>$package</td>\n");
                                                    echo ("<td>$summary</td>\n");
                                                    echo ("</tr>\n");
    
            }
            echo ("</table></P>");
        }
    mysql_close($cid);
     
  2. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,914
    Likes Received:
    8
    Location:
    Los Angeles
    SELECT reservations.field1, reservations.field2, reservations.field3, login.fname, login.lname, login.phone FROM reservations, login WHERE reservations.resid = reservation_users.resid AND reservations.memberid = login.memberid

    Not the correct answer because I don't know the fieldnames of the reservation column, but that should get you started
     
  3. Dustro

    Dustro New Member

    Joined:
    Nov 1, 2003
    Messages:
    2,123
    Likes Received:
    0
    Location:
    NC
    so does this go in place of the $SQL in my code? I placed it there along with the WHERE $category and am getting "Unknown table 'reservation_users' in where clause" . Of course the table exists and is spelled correctly so im sure i have another problem.
     

Share This Page