Alternative for MySQL_Num_Rows Using Pdo

Alternative for mysql_num_rows using PDO

$res = $DB->query('SELECT COUNT(*) FROM table');
$num_rows = $res->fetchColumn();

or

$res = $DB->prepare('SELECT COUNT(*) FROM table');
$res->execute();
$num_rows = $res->fetchColumn();

You can use this to ask if data exists or is selected, too:

$res = $DB->query('SELECT COUNT(*) FROM table');
$data_exists = ($res->fetchColumn() > 0) ? true : false;

Or with your variables:

$res = $DB->query('SELECT COUNT(*) FROM table');
$message = ($res->fetchColumn() > 0) ? array('status' => 'ok') : array('status' => 'error');

PHP PDO - Num Rows

According to the manual, there is a PDOStatement->rowCount method ; but it shouldn't be used (quoting) :

For most databases,
PDOStatement::rowCount() does not
return the number of rows affected by
a SELECT statement.
Instead, use
PDO::query() to issue a SELECT COUNT(*) statement with the same
predicates as your intended SELECT
statement, then use
PDOStatement::fetchColumn() to
retrieve the number of rows that will
be returned.
Your application can then
perform the correct action.

If you already have a recordset, and want to know how many lines are in it, you'll have to fetch the data, using one of the fetch* methods ; and use count -- like you suggested.

MySQL equivalent of mysql_num_rows. Which is better?

I wouldn't say it is "a good alternative". It is something completely different.

    SELECT data FROM table

fetches data, that you can use. You can then use mysql_num_rows() (or rowCount() in the PDO case) to get the number of records (for rendering, pagination or whatever).

If you only want the number of records (and not the actual data), you should use

    SELECT COUNT(data) FROM table

as you are only selecting the number of records (to be precise, you are counting non-NULL values of data, use SELECT COUNT(*) to get all records).

If you would be using the latter in combination with the former to get both, you could get into trouble when the number of records change between the two queries.

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

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; 
?>

Alternative method in PDO for mysql_result() function

As I suspected and alluded to in the comments, your original use of mysql_result() was unnecessary. What you had in the old class was a collection of C-style incremental for loops, which are rarely appropriate in PHP. Instead it's much more common to iterate arrays with foreach and when dealing with database APIs, to do something like:

// This is the normal and easy way to get an associative array of results:
while ($row = mysql_fetch_assoc()) {
  $results[] = $row;
}

...where you don't need to worry about the number of rows or columns.

In any case, all of that is entirely unnecessary now and can be replaced with a single call to fetchAll() in PDO, since all it did was retrieve all rows into a column-name indexed array. That is about as standard as a fetch operation gets.

   // ALL of this...
   // $res = array();
   // for ($i = 0; $i < $numRows; $i++) {
   //     $res[$i] = array();
   //     for ($j = 0; $j < count($fields); $j++)
   //         $res[$i][$fields[$j]] = mysql_result($query, $i, $j);
   // }

   // ...may be replaced with simply:
   $res = $query->fetchAll(PDO::FETCH_ASSOC);

This also means that you don't really need the two preceding blocks which retrieve the number of rows and columns. All of that is implicit in the call to fetchAll(). The only parts you actually now need are the query and the fetch:

public function MySQLtoJSON($input_query)
{
    // Do your query...
    $query = $this->connection->query($input_query) or die("Unable to execute the query");

    // Fetch the rows as associative arrays
    $result = $query->fetchAll(PDO::FETCH_ASSOC);

    // And encode it as JSON
    $json = json_encode($result);
    return $json;
}

Note that since you are executing arbitrary SQL statements in the $query parameter, you will need to be cautious about what gets passed in there to avoid SQL injection. If you are accepting any user input rather than static queries, you'll need to look into adapting this to use PDO::prepare() and execute them as parameterized queries.

Row count with PDO

$sql = "SELECT count(*) FROM `table` WHERE foo = ?"; 
$result = $con->prepare($sql); 
$result->execute([$bar]); 
$number_of_rows = $result->fetchColumn(); 

Not the most elegant way to do it, plus it involves an extra query.

PDO has PDOStatement::rowCount(), which apparently does not work in MySql. What a pain.

From the PDO Doc:

For most databases,
PDOStatement::rowCount() does not
return the number of rows affected by
a SELECT statement. Instead, use
PDO::query() to issue a SELECT
COUNT(*) statement with the same
predicates as your intended SELECT
statement, then use
PDOStatement::fetchColumn() to
retrieve the number of rows that will
be returned. Your application can then
perform the correct action.

EDIT: The above code example uses a prepared statement, which is in many cases is probably unnecessary for the purpose of counting rows, so:

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); 
echo $nRows;

---

Related Topics