PDO version of mysql_num_rows($result)==0)

Question

Possible Duplicate:
Alternative for mysql_num_rows using PDO

^ I believe it isn't the same question - The other authors code is different to mine, which needed a different answer. I successfully got my answer from this post and marked it as answered. Everything is working fine now (no help from the other 'duplicate' thread.

I want to display a "No Client Found" message if no results are found, Is there a PDO method to the following code?:

$result = mysql_query($sql) or die(mysql_error()."<br />".$sql);
if(mysql_num_rows($result)==0) {
    echo "No Client Found";

I tried the following...

<?php                               
$db = new PDO('mysql:host=localhost;dbname=XXXXXXXXXXXX;charset=utf8','XXXXXXXXXXXX', 'XXXXXXXXXXXX');

    $query = $db->query('SELECT * FROM client');

    if ($query == FALSE) {
      echo "No Clients Found";
    }
    else
    {
    foreach($query as $row)
    {
     <some code here>
    }   
    }           
    ?>

Am I missing something?

I've read: http://php.net/manual/en/pdostatement.rowcount.php but hasn't helped

SIFE · Answer 1 · 2013-01-26T18:28:46.493

3

<?php                               
$db = new PDO('mysql:host=localhost;dbname=XXXXXXXXXXXX;charset=utf8','XXXXXXXXXXXX', 'XXXXXXXXXXXX');

    $query = $db->query('SELECT * FROM client WHERE ID = 10');

    if ($query->rowCount() != 1) {
      echo "No Clients Found";
    }
    else
    {
    foreach($query as $row)
    {
     <some code here>
    }   
    }           
    ?>

In PDO, rowCount method is used to count the returned results. Your query must select some thing unique, like an email address or username if you want to check for unique existence, else, if you want at least find one row, change the condition to this:

if ($db->rowCount() == 0)

There is a tutorial: PDO for MySQL developers.

edited Jan 26 '13 at 18:28

answered Jan 26 '13 at 13:36

SIFE

5,567
7
32
46

Using this code I get: `Fatal error: Call to undefined method PDO::rowCount()` :/ – potts Jan 26 '13 at 13:38
@potts It should be `$query->rowCount()`, not `$db->rowCount()`. Still same warning applies, `rowCount()` is documented to be not reliable for `SELECT`, it may work on MySQL, but may cause you problems if you're changing RDBMS later. – Joachim Isaksson Jan 26 '13 at 13:50
probably won't be changing anytime soon. I do have one more issue. How do I enter an array in the where clause? `$query = $db->query('SELECT * FROM client where client =($ids)');` like that? – potts Jan 26 '13 at 13:58
@potts You could have a look [here](http://stackoverflow.com/questions/6498518/difficulty-using-an-array-as-part-of-pdo-in-query). – Joachim Isaksson Jan 26 '13 at 14:02
I had a look but, i'm at a loss! Here's my code to date: http://davidpottrell.co.uk/paste/where.txt – potts Jan 26 '13 at 14:13
Ah fixed my mistake - It was: `$query = $db->query("SELECT * FROM client WHERE client ='$id'");` i got to add `$id = $_GET["client"];` above it – potts Jan 26 '13 at 14:28

david strachan · Accepted Answer · 2013-01-26T22:23:58.410

1

PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement in some databases. Documentation The code below uses SELECT COUNT(*) and fetchColumn(). Also prepared statements and try & catch blocks to catch exceptions.

<?php
// Get parameters from URL
$id = $_GET["client"];
try {
    $db = new PDO('mysql:host=localhost;dbname=XXXX;charset=utf8', 'XXXX', 'XXXX');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Prepare COUNT statement
    $stmt1 = $db->prepare("SELECT COUNT(*) FROM client WHERE client = ?");
     // Assign parameters
    $stmt1->bindParam(1,$id);
    $stmt1->execute();
    // Check the number of rows that match the SELECT statement 
    if($stmt1->fetchColumn() == 0) {
        echo "No Clients Found";
    }else{
        //echo "Clients Found";
        // Prepare Real statement
        $stmt2 = $db->prepare("SELECT * FROM client WHERE client = ?");
     // Assign parameters
        $stmt2->bindParam(1,$id);
        $stmt2->setFetchMode(PDO::FETCH_ASSOC);
        $stmt2->execute();
        while($row = $stmt2->fetch()) {
            //YOUR CODE HERE  FROM
             // Title
             echo '<div id="portfolio_detail">';
             //etc.etc TO
             echo '<div><img src="'."/client/".$row[client].'_3.png"/></div>';
             echo '</div>'; 
        }//End while
    }//End if else
 }//End try 
 catch(PDOException $e) {
    echo "I'm sorry I'm afraid you have an Error.  ". $e->getMessage() ;// Remove or modify after testing 
    file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", myfile.php, ". $e->getMessage()."\r\n", FILE_APPEND);  
 }
//Close the connection
$db = null; 
?>

edited Jan 26 '13 at 22:23

answered Jan 26 '13 at 14:04

david strachan

7,174
2
23
33

Try prepared statement `//prepare statement $stmt = $db->prepare(('SELECT * FROM client WHERE name = ? AND other = ? ' ); // Assign parameters $stmt->bindParam(1,$name); $stmt->bindParam(2,$other);//Execute query $stmt->setFetchMode(PDO::FETCH_ASSOC); $stmt->execute();` – david strachan Jan 26 '13 at 14:23
Is there a } missing at the end of your code? – potts Jan 26 '13 at 14:35
my foreach has a bracket to open it - where would it close? `foreach($query as $row) {` – potts Jan 26 '13 at 14:37
I added closing bracket & brckets for foreach. – david strachan Jan 26 '13 at 14:54
Strange - I've copied your code exactly and placed mine inside - looking at it it should work but I get a blank page... my code is here: http://davidpottrell.co.uk/paste/error.txt – potts Jan 26 '13 at 15:11
You will need to remove ` if ($query->rowCount() != 1) { echo "No Clients Found"; } else { foreach($query as $row) {` – david strachan Jan 26 '13 at 16:22
@potts I have modified code. It has been tested with my own database. Try it out just echoing title & id before adding your code between the positions noted. – david strachan Jan 26 '13 at 22:30

PDO version of mysql_num_rows($result)==0)

2 Answers2

Linked