homepage Welcome to WebmasterWorld Guest from 54.227.171.163
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Query results
How to show the number of results from a query
AliTaylor4411



 
Msg#: 4188410 posted 10:28 pm on Aug 17, 2010 (gmt 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>";
}
?>

 

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4188410 posted 12:22 am on Aug 18, 2010 (gmt 0)

Try adding:

echo count($result);

Sp4rkyM4rk



 
Msg#: 4188410 posted 12:35 am on Aug 18, 2010 (gmt 0)

To return the amount of rows found:

mysql_num_rows($result)
rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4188410 posted 2:16 am on Aug 18, 2010 (gmt 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];

AliTaylor4411



 
Msg#: 4188410 posted 10:43 am on Aug 18, 2010 (gmt 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>";
}
?>

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4188410 posted 10:57 am on Aug 18, 2010 (gmt 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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4188410 posted 3:19 pm on Aug 18, 2010 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4188410 posted 3:21 pm on Aug 18, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved