Solved

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

Posted on 2014-01-22
9
1,006 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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 30

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 108

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 42

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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 30

Expert Comment

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

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 30

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 30

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now