Solved

Get number of retuned results with prepared statement using PHP and MySQL

Posted on 2014-01-22
9
1,117 Views
Last Modified: 2014-01-24
I'm doing a query in Mysql via PHP using a prepared statement.  I'd like to get the number of results returned to show on the page.  I'm not having luck with the following code which executes the query and displays the results as desired.  I've omitted parts of the code for clarity:

<?
try {

$conn = new PDO($dsn, $username, $password);
 
$sql = "SELECT * FROM propertiesnew $whereClause  ORDER BY FIELD (type,'Home','Condo','Duplex','Townhouse'), type, development";

$sqlprep = $conn->prepare($sql);       

PREP CODE LEFT OUT 
  
if($sqlprep->execute()) {
  

$result = $sqlprep->fetchAll();
$number_of_rows = count($result);
echo 'Number of rows: ' . $number_of_rows;

//this code outputs the results formatted to my specifications
$current_cat = null;
  while($row = $sqlprep->fetch()) {
  if ($row["type"] != $current_cat) {
	  
	  
    $current_cat = $row["type"];
	
	
    echo "<a name=\"".catreplace($current_cat)."\"></a><br>
<div class=devtitlebox><h2>".catreplace($current_cat)."</h2></div>";
  }

  if ($row["development"] != $sub_cat) {
    $sub_cat = $row["development"];
	if ($sub_cat != "Duplex" && $sub_cat != "Townhouse" && $sub_cat != "Single Family Home"){
  
	$sub_cat2 = str_replace(' ','_',$sub_cat);
	echo '<div class="devdesc developments">';
	include ($sub_cat2.'.php');
	echo "</div>";
	}
  }
  echo "<div id=propertylist_item>";
 
  $city2 = ltrim($row[city]);
  echo "<div class=devphotos>
    <div class=\"foo bar2 white-cardprop\"><a href=\"Vacation-Rentals/".ltrim(str_replace(' ','-',$city2))."/".ltrim(str_replace(' ','-',$type))."/".$row[id].".htm\"><IMG SRC=propertypics/photos/".$row["pic1"]."></a></div></div>";
  
   $end = getLeadingSentences($row["description"], 2);
    echo("<div class=devabout2><div class=prop_name_list><h4>".$row["propname"]."</h4></div><div class=list_details><strong>Sleeps:</strong> ".$row["sleeps"]." <BR><strong>Beds:</strong> ".$row["beds"]."<BR><strong>Baths:</strong> ".$row["bathrooms"]."<BR><strong>View:</strong> ".$row["view"]."</div>");
	echo "<div class=listdesc><p>$end <a href=\"Vacation-Rentals/".ltrim(str_replace(' ','-',$city2))."/".ltrim(str_replace(' ','-',$row[type]))."/".$row[id].".htm\">[more]</a></p>  </div>";
	echo "</div></div>";
	echo "<div class=clear></div>";
  }


 }

	  
  $conn = null;       
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Open in new window


In the code above, when I include this code to count the results:

$result = $sqlprep->fetchAll();
$number_of_rows = count($result);
echo 'Number of rows: ' . $number_of_rows;

Open in new window

The data from the database isn't shown (the script just stops processing.)  When I remove that code, everything works as it should, but alas, I get no count of the number of records returned.

How do I get both to work concurrently?
0
Comment
Question by:mynameisjustin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 400 total points
ID: 39801085
To get the number of rows use another query:
$sql = "SELECT COUNT(*) FROM propertiesnew $whereClause";
$sqlprep = $conn->prepare($sql);       
$sqlprep->execute();
$number_of_rows = $sqlprep->fetchColumn();

Open in new window

0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 50 total points
ID: 39801573
The PDOStatement object has the rowCount() method for this.

The problem with this strategy is that you have fetched all of the rows and PDO does not have a data_seek() or rewind() ability with MySQL.

// GETS ALL OF THE ROWS, AND THEN THEY ARE ALL GONE!
$result = $sqlprep->fetchAll();
$number_of_rows = count($result);
echo 'Number of rows: ' . $number_of_rows;

Open in new window

Go to this article and search for "Find the Number of Rows"
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 50 total points
ID: 39801619
Because you've already fetched all the rows into the $result variable, just loop through the $result variable to output your rows (using foreach), rather than calling fetch().

$result = $sqlprep->fetchAll();
$number_of_rows = count($result);
echo 'Number of rows: ' . $number_of_rows;

$current_cat = null;

foreach ($result as $row):

     $current_cat = $row["type"];
     //etc..

endforeach;

Open in new window

0
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

Author Comment

by:mynameisjustin
ID: 39803636
Thank you for the help.  I'm currently testing out the suggestions to see if any work.  Where exactly would the other query be placed in the code?  Unfortunately, the rowCount(0) method has been tried and simply produces 0 for the count.
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 39803792
I would put it immediately after the connection and before the query which retrieves data.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39803807
From the PHP Docs:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.

What other query are you talking about - I only see one in your code.

As I said in my previous statement, you're fetchAll() to pull the records into an array, so just use the PHP count() function on the array and that will give you the the row count. As the results are already pulled into an array, there's no need to then call fetch() - just simply loop through the array with a foreach() block:

if($sqlprep->execute()) {
   $result = $sqlprep->fetchAll();
   $number_of_rows = count($result);
   echo 'Number of rows: ' . $number_of_rows;

   //this code outputs the results formatted to my specifications
   $current_cat = null;
   foreach ($result as $row):  
      if ($row["type"] != $current_cat) {
         ...
     }
   endforeach;

etc...

Open in new window

0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 39803810
But are you saying that you did

$conn = new PDO($dsn, $username, $password);
 
$sql = "SELECT * FROM propertiesnew $whereClause  ORDER BY FIELD (type,'Home','Condo','Duplex','Townhouse'), type, development";

$sqlprep = $conn->prepare($sql);       

PREP CODE LEFT OUT 
  
if($sqlprep->execute()) {
  $count = $sqlprep->rowCount();

Open in new window


and you got 0 as result?
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 39803844
I read now Chris' solution: it's sure the simplest and I would use that.

Cheers
0
 

Author Comment

by:mynameisjustin
ID: 39807053
Unfortunately I was not able to get Chris' solution to work for me.  I ended up using Marco's suggestion of another query which looked like this:

try {

  $conn = new PDO($dsn, $username, $password);
 
$whereClause = "WHERE";

...all of the whereClause variables....

$sql2 = "SELECT COUNT(*) FROM propertiesnew $whereClause";
$sqlprep2 = $conn->prepare($sql2);

...all of the bind values...

$sqlprep2->execute();

$number_of_rows = $sqlprep2->fetchColumn();
echo $number_of_rows;

//Had to add this to allow the next query to run
$sqlprep2->closeCursor();  

Open in new window


Thank you for everyone's help!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question