Need help creating 3 table query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Need help creating 3 table query

Post by dblock »

Trying to get this query to work, any ideas?

$q = "SELECT f.facName,f.facAddress, CONCAT(f.facCity, ', ', f.facState) AS loc, f.facZip,u.refRateID,u.refFID,r.RateID,r.RateType FROM facility f LEFT JOIN Urating u INNER JOIN Ratetype r WHERE (f.facZip = '$zip' AND u.refFID = f.FID AND u.refRateID = r.RateID) LIMIT $start, $display";
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Need help creating 3 table query

Post by jaoudestudios »

What error are you getting?
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Re: Need help creating 3 table query

Post by dblock »

This is the error I'm getting.

Your search results:



Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\view_facilities.php on line 102

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\view_facilities.php on line 113
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Need help creating 3 table query

Post by Benjamin »

Please post your code using [ code = php ] tags. Also, please post any error being returned by mysql_query().
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Re: Need help creating 3 table query

Post by dblock »

OK I get the thing about posting the code, but I'm kind of new at this, so I posted the only error I saw. I'm not sure what you mean. Pleas excuse my noobness.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Need help creating 3 table query

Post by Benjamin »

This should fix it:

Code: Select all

 
SELECT
  f.facName,
  f.facAddress,
  CONCAT( f.facCity, ', ', f.facState ) AS loc,
  f.facZip, u.refRateID,
  u.refFID,
  r.RateID,
  r.RateType
FROM
  facility f
  LEFT JOIN Urating u ON u.refFID = f.FID
  INNER JOIN Ratetype r ON u.refRateID = r.RateID
WHERE
  f.facZip = '$zip'
LIMIT $start , $display 
 
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Re: Need help creating 3 table query

Post by dblock »

Now I'm getting a new error. Not sure what it means.

Parse error: syntax error, unexpected T_STRING in C:\xampp\htdocs\view_facilities.php on line 53
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Need help creating 3 table query

Post by Benjamin »

Please post the code, how can we fix what we cannot see?
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Re: Need help creating 3 table query

Post by dblock »

Here's the code

Code: Select all

<?php # Script 1.0 - view_facilities.php #2
// This script retrieves retrieves selected records from the facilities table.
 
$page_title = 'View the search results';
include ('includes/header2.html');
 
// Page header:
echo '<h1>Registered Facilities</h1>';
 
if (isset($_POST['szip']) && is_numeric($_POST['szip'])) {
 
    $zip = trim($_POST['szip']);
 
}
 
else {
    $zip= trim($_GET['szip']);
    }
    echo $zip;
require_once ('includes/mysqli_connect.php'); // Connect to the db.
 
// Number of records to show per page:
$display = 4;
 
// Determine how many pages there are...
if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined.
   $pages = $_GET['p'];
} else { // Need to determine.
    // Count the number of records:
   $q = "SELECT COUNT(FID) FROM facility";
   $r = @mysqli_query ($dbc, $q);
   $row = @mysqli_fetch_array ($r, MYSQLI_NUM);
   $records = $row[0];
   // Calculate the number of pages...
   if ($records > $display) { // More than 1 page.
      $pages = ceil ($records/$display);
   } else {
      $pages = 1;
   }
} // End of p IF.
 
// Determine where in the database to start returning results...
if (isset($_GET['s']) && is_numeric($_GET['s'])) {
    $start = $_GET['s'];
} else {
    $start = 0;
}
 
 
      
//Make the query:
SELECT
  f.facName,
  f.facAddress,
  CONCAT( f.facCity, ', ', f.facState ) AS loc,
  f.facZip, u.refRateID,
  u.refFID,
  r.RateID,
  r.RateType
FROM   facility f LEFT JOIN Urating u ON u.refFID = f.FID
INNER JOIN Ratetype r ON u.refRateID = r.RateID
WHERE
f.facZip = '$zip'
LIMIT $start , $display    
$r = @mysqli_query ($dbc, $q); // Run the query.
 
//Count the number of returned rows:
//$num = mysqli_num_rows($r);
 
   
?>
<div id="header">
      
      
</div>
      
      <div id="main-nav">
         
         <dl class="hidden">
            <dt id="about"><a href="#">About</a></dt>
            <dt id="services"><a href="#">Services</a></dt>
            <dt id="portfolio"><a href="#">Portfolio</a></dt>
            <dt id="contact"><a href="#">Contact Us</a></dt>
         </dl>
   </div>
   
      <div id="sidebar-a">
         <div class="padding">
            
            Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nullam gravida enim ut risus.
            Praesent sapien purus, ultrices a, varius ac, suscipit ut, enim. Maecenas in lectus.
            Donec in sapien in nibh rutrum gravida. Sed ut mauris. Fusce malesuada enim vitae lacus
            euismod vulputate. Nullam rhoncus mauris ac metus. Maecenas vulputate aliquam odio.
            Duis scelerisque justo a pede. Nam augue lorem, semper at, porta eget, placerat eget,
            purus. Suspendisse mattis nunc vestibulum ligula. In hac habitasse platea dictumst.
            
            </div>
            
 
         </div>
            
      <div id="content">
      <div class="padding">
<?php            
 
 
   // Print how many users there are:
   
   echo "<H2>Your search results:</H2>";
   echo "<br/>";
   echo "<br/>";
   
   // Fetch and print all the records:
   while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
      echo $row['facName']. "<br/>";
      echo $row['facAddress']. "<br/>";
      //echo $row['loc']. "<br/>";
      echo $row['facZip']. "<br/>";
      echo "<br/>";
      
   }
 
   
   
   mysqli_free_result ($r); // Free up the resources.   
   mysqli_close($dbc); // Close the database connection.
 
            
// Make the links to other pages, if necessary.
if ($pages > 1) {
   
   // Add some spacing and start a paragraph:
   echo '<br /><p>';
   
   // Determine what page the script is on:   
   $current_page = floor($start/$display) + 1; //strip decimal point if any
   
   // If it's not the first page, make a Previous button:
   if ($current_page > 1) {
      echo '<a href="view_facilities.php?s=' . ($current_page-1) . '&p=' . $pages . '&szip=' . $zip .'">Previous</a> ';
   }
   
   // Make all the numbered pages:
   for ($i = 1; $i <= $pages; $i++) {
      if ($i != $current_page) {
         echo '<a href="view_facilities.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&szip=' . $zip . '">' . $i . '</a> ';
      } else {
         echo $i . ' ';
      }
   } // End of FOR loop.
   
   
   // If it's not the last page, make a Next button:
   if ($current_page != $pages) {
      echo '<a href="view_facilities.php?s=' . ($current_page+1) . '&p=' . $pages . '&szip=' . $zip . '">Next</a>';
   }
   
   
   echo '</p>'; // Close the paragraph.
   
} // End of links section.            
   
      include ('includes/footer2.html');
?>
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Need help creating 3 table query

Post by Benjamin »

Ok on lines 31 and 32 you have the @ symbol in front of mysql_query, so you won't see any errors it outputs.

This code:

Code: Select all

 
SELECT
  f.facName,
  f.facAddress,
  CONCAT( f.facCity, ', ', f.facState ) AS loc,
  f.facZip, u.refRateID,
  u.refFID,
  r.RateID,
  r.RateType
FROM   facility f LEFT JOIN Urating u ON u.refFID = f.FID
INNER JOIN Ratetype r ON u.refRateID = r.RateID
WHERE
f.facZip = '$zip'
LIMIT $start , $display    
$r = @mysqli_query ($dbc, $q); // Run the query.
 
 
Should be:

Code: Select all

 
$q = "SELECT
        f.facName,
        f.facAddress,
        CONCAT( f.facCity, ', ', f.facState ) AS loc,
        f.facZip, u.refRateID,
        u.refFID,
        r.RateID,
        r.RateType
      FROM
        facility f
        LEFT JOIN Urating u ON u.refFID = f.FID
        INNER JOIN Ratetype r ON u.refRateID = r.RateID
      WHERE
        f.facZip = '$zip'
      LIMIT
        $start , $display";    
$r = @mysqli_query ($dbc, $q); // Run the query.
 
Note that you also have a @ sign in front of this mysq_query as well, suppressing any errors.
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Re: Need help creating 3 table query

Post by dblock »

ok I'm not getting any errors but I'm not getting the results I wanted either. Maybe I should explain my intentions. I want to list all records from the facility table where the facZip = the $zip value being passed from a form. I then want to list any records from the Urating table where facility.FID = Urating.refFID if any. I would then like to list all records from the Ratetype table where RateID = refRateID, if any. Does that make sense or am I confusing you as much as I'm confusing myself.
dblock
Forum Newbie
Posts: 7
Joined: Sat Jan 31, 2009 2:10 pm

Re: Need help creating 3 table query

Post by dblock »

never mind, I figured it out thx for the help.
Post Reply