Need help creating 3 table query
Moderator: General Moderators
Need help creating 3 table query
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";
$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";
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Need help creating 3 table query
What error are you getting?
Re: Need help creating 3 table query
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
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
Re: Need help creating 3 table query
Please post your code using [ code = php ] tags. Also, please post any error being returned by mysql_query().
Re: Need help creating 3 table query
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.
Re: Need help creating 3 table query
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
Re: Need help creating 3 table query
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
Parse error: syntax error, unexpected T_STRING in C:\xampp\htdocs\view_facilities.php on line 53
Re: Need help creating 3 table query
Please post the code, how can we fix what we cannot see?
Re: Need help creating 3 table query
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');
?>Re: Need help creating 3 table query
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:
Should be:
Note that you also have a @ sign in front of this mysq_query as well, suppressing any errors.
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.
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.
Re: Need help creating 3 table query
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.
Re: Need help creating 3 table query
never mind, I figured it out thx for the help.