Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-22
9
Medium Priority
?
1,199 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 1600 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 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 44

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 44

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

963 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