Query results

How to show the number of results from a query

[AliTaylor4411]

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>";
}
?>

[Demaestro]

Try adding:

echo count($result);

[Sp4rkyM4rk]

To return the amount of rows found:

mysql_num_rows($result)

[rocknbil]

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];

[AliTaylor4411]

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>";
}
?>

[Matthew1980]

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

[LifeinAsia]

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.

[rocknbil]

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.