PHP - PDO, how to return the number of rows in a select statement

Question

I have a simple statement but cant seem to get the number of rows from the select statement, really bugging me when its really simple. I keep getting empty string.

Here is the code

include "connect.php";

$userID = $_POST['userID'];
$threadID = $_POST['threadID'];

$sql1 = "select * from AT_AddThread where AT_ID = :threadID";
$stmt = $conn->prepare($sql1);
$stmt->execute(array(':threadID' => $threadID));
$result = $stmt->fetchColumn(7);
//echo $result;

$sql2 = "select * from TJ_ThreadJoined where TJ_T_ID = :threadID"
$q2 = $conn->prepare($sql2);
$q2->execute(array(':threadID' => $threadID));
$q2->execute();
$rows = $q2->fetchColumn();
echo $rows; //THIS IS where i want to return the number of rows from the select statement

See the manual for $conn->rowCount although its not completely reliable as it does not work will all database engines — RiggsFolly
– RiggsFolly, Commented Jun 22, 2015 at 13:34
There is a metod for that: php.net/manual/en/pdostatement.rowcount.php but it is not guaranteed to work with all db engines. — Koshinae
– Koshinae, Commented Jun 22, 2015 at 13:35
->rowCount() is technically for retrieving the last number of rows affected by an INSERT or UPDATE statement however some database servers will return it for a SELECT. fetchColumn according to the docs : Returns a single column from the next row of a result set so you couldn't really do a count on that anyway - it would always be 1 (i.e. the next row). You'd bet better of merely selecting only the columns you want in the SQL and then using fetch() in a loop or fetchAll() and counting the array size returned. — CD001
– CD001, Commented Jun 22, 2015 at 13:55

meda · Accepted Answer · 2015-06-22 14:00:34Z

2

In addition to fetchColumn you can try the following ways:

count the rows

$rows = $stmt->fetchAll(); echo "count = " . count($rows);
Use rowCount()

echo "count = " . $stmt->rowCount();
Add it to the query :

SELECT COUNT(*) as rowCount ...

echo $row['rowCount'];

answered Jun 22, 2015 at 14:00

meda

45.5k14 gold badges97 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alexandreferris · Accepted Answer · 2015-06-22 13:41:00Z

0

Try this:

echo $q2->rowCount();

or

$row = $q2->fetch(PDO::FETCH_NUM);
echo $row[0];

.

->rowCount(); doc

answered Jun 22, 2015 at 13:41

alexandreferris

6841 gold badge11 silver badges29 bronze badges

Comments

user2047861 · Accepted Answer · 2015-06-22 14:02:21Z

0

You can always get number of rows returned in last query. Just add SQL_CALC_FOUND_ROWS after SELECT statement

$sql2 = "select SQL_CALC_FOUND_ROWS * from TJ_ThreadJoined where TJ_T_ID = :threadID"

Then execute query SELECT FOUND_ROWS(), fetch row and you will have number of rows from last query.

answered Jun 22, 2015 at 14:02

user2047861

1251 silver badge10 bronze badges

Collectives™ on Stack Overflow

PHP - PDO, how to return the number of rows in a select statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related