Welcome to WebmasterWorld Guest from 54.147.44.13

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Query results

How to show the number of results from a query

     
10:28 pm on Aug 17, 2010 (gmt 0)

New User

5+ Year Member

joined:Aug 3, 2010
posts: 33
votes: 0


Hi All,

I have a drop down lists which sends query and which shows the results fine. How can I put a sentence about the results say

"The number of Properties matching your search are ?"

Any ideas welcome

Ali

This is my code which produces the results

//Create the MySQL Command to retrieve the record
$sql= "SELECT * FROM `property`
WHERE `price` >= '".$_POST['minprice']."'
AND `price` <= '".$_POST['maxprice']."'
AND `bedrooms` >= '".$_POST['bedrooms']."'
AND `propertytype` = '".$_POST['propertytype']."'";

echo "$sql<br>";


//Connect code etc.....

//Execute the MySQL statement and convert the result to an array
$result = mysql_query($sql, $conn);
while ($array = mysql_fetch_array($result)) {
$propertyid = $array ['propertyid'];
$propertyaddress = $array['propertyadd1'].$array['propertyadd2'].$array['propertyadd3'].$array['postcode'];
$propertytype = $array['propertytype'];
$bedrooms = $array['bedrooms'];
$price = $array['price'];
$description = $array['description'];
echo "<p> <a href= propertydetailsbox.php?propertyid=$propertyid>$propertyaddress</a></br>$propertytype</br>$bedrooms</br>$price</br>$description</p>";
}
?>
12:22 am on Aug 18, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


Try adding:

echo count($result);
12:35 am on Aug 18, 2010 (gmt 0)

New User

5+ Year Member

joined:Apr 24, 2010
posts:23
votes: 0


To return the amount of rows found:

mysql_num_rows($result)
2:16 am on Aug 18, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Both of those will fail if you ever use a limit clause, which you will eventually need in pagination and in the context of properties. They will only return the number you're limiting to. So if you have 1000 records, your limit is 25 per page, you will always have "25". A more forward-thinking choice might be:

$sql= "select count(*) from `property`
WHERE `price` >= '".$_POST['minprice']."'
AND `price` <= '".$_POST['maxprice']."'
AND `bedrooms` >= '".$_POST['bedrooms']."'
AND `propertytype` = '".$_POST['propertytype']."'";
$result = mysql_query($sql, $conn);
$row = mysql_fetch_array($result);

$total_records = $row[0];
10:43 am on Aug 18, 2010 (gmt 0)

New User

5+ Year Member

joined:Aug 3, 2010
posts:33
votes: 0


Hi Guys thanks for the replies

Tried the first two and nothing happened!

Tried Rocknbil's suggestion and now not getting any output save for the echo $sql command.

This is what I have done - Have I put the code in wrong?

//Create the MySQL Command to retrieve the record
$sql= "SELECT count(*) FROM `property`
WHERE `price` >= '".$_POST['minprice']."'
AND `price` <= '".$_POST['maxprice']."'
AND `bedrooms` >= '".$_POST['bedrooms']."'
AND `propertytype` = '".$_POST['propertytype']."'";

echo "$sql<br>";

//Execute the MySQL statement and convert the result to an array
$result = mysql_query($sql, $conn);
$row = mysql_fetch_array($result);
$total_records = $row[0];
while ($array = mysql_fetch_array($result)) {
$propertyid = $array ['propertyid'];
$propertyaddress = $array['propertyadd1'].$array['propertyadd2'].$array['propertyadd3'].$array['postcode'];
$propertytype = $array['propertytype'];
$bedrooms = $array['bedrooms'];
$price = $array['price'];
$description = $array['description'];
echo "<p> <a href= propertydetailsbox.php?propertyid=$propertyid>$propertyaddress</a></br>$propertytype</br>$bedrooms</br>$price</br>$description</p>";
}
?>
10:57 am on Aug 18, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


Hi there AliTaylor4411,

>>and now not getting any output save for the echo $sql

That implies that you have broken the string :) If you remove the count(*) part (as this is the only thing that you have altered since the last revision), does the string come back (the echo to screen), if it does, then there something wrong... See what you say to that first :)

Cheers,
MRb
3:19 pm on Aug 18, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Note that the "SELECT count(*)" part will only return 1 row and 1 value: the number of records matching your WHERE parameters. propertyid, propertyadd1, etc. will NOT be returned in that query.

(You may also want to give it an explicit value that you can easily manipulate, like "SELECT count(*) AS NumRows")

If you want to get the data AND the number of rows returned, that's something different. I don't know if PHP offers a function to get that information or if you have to create a workaround.
3:21 pm on Aug 18, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Ali - that is a separate query, not meant to replace the original. Like this. Combining your question from your other thread, [webmasterworld.com]

<?php
//Open the connection
$conn = mysql_connect("*&*&*&*&","&*&*&**&*&", "*&*&*&*&");
//Select the Database
mysql_select_db("*&*&*&*", $conn);
$total_records=0; // initialize
$results = $countphrase = null;
//
// Get a count of total records.
$sql= "select count(*) from `property`
WHERE `price` >= '".$_POST['minprice']."'
AND `price` <= '".$_POST['maxprice']."'
AND `bedrooms` >= '".$_POST['bedrooms']."'
AND `propertytype` = '".$_POST['propertytype']."'";
$result = mysql_query($sql, $conn);
$row = mysql_fetch_array($result);
//
$total_records = $row[0];
//
if ($total_records > 0) {
//Create the MySQL Command to retrieve the records
$sql= "SELECT * FROM `property`
WHERE `price` >= '".$_POST['minprice']."'
AND `price` <= '".$_POST['maxprice']."'
AND `bedrooms` >= '".$_POST['bedrooms']."'
AND `propertytype` = '".$_POST['propertytype']."'";
//
while ($array = mysql_fetch_array($result)) {
$propertyid = $array ['propertyid'];
$propertyaddress = $array['propertyadd1'].
$array['propertyadd2'].
$array['propertyadd3'].
$array['postcode'];
$propertytype = $array['propertytype'];
$bedrooms = $array['bedrooms'];
$price = $array['price'];
$description = $array['description'];
$results .= "
<tr>
<td class=\"right-align\">Price: </td><td> $price </td>
<td class=\"right-align\">Property Type: </td><td> $propertytype </td>
<td class=\"right-align\">Property Address: </td>
<td>
<a href=\"propertydetailsbox.php?propertyid=$propertyid\">$propertyaddress</a>
</td>
<td class=\"right-align\">Number of Bedrooms: </td><td> $bedrooms </td>
<td class=\"right-align\" valign=\"top\">Description: </td><td> $description </td>
</tr>
";
}
if ($results) {
if ($total_records==1) {
$countphrase = "<p>Your lucky day! We have $total_records record matching your search.</p>";
}
else {
$countphrase = "<p>There were $total_records matching records.</p>";
}
echo "
$countphrase
<table id=\"search-result\">$results</table>
";
}
else { echo "<p>Something wrong, $total_records found but no results.</p>"; }
} // end $total_records > 0;
else { echo "<p>No results were found with that search.</p>"; }
?>


May not be copy and paste, I cobbled it together and may have errors but when debugged that will work nicely.

Note the different between this and your other thread - here we only execute the search if $total_records > 0.