Solved

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

Posted on 2014-01-22
9
1,025 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 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 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 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
 

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

919 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

14 Experts available now in Live!

Get 1:1 Help Now