Solved

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

Posted on 2014-01-22
9
1,067 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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 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 …

685 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