CODEWITHSUNDEEP
phppdo
Stephen Last
Stephen Last

Reputation: 5781

PHP PDO Select Query Results

This seems like it should be a very easy task, but I'm not sure it is.

I would like to use PDO to do a SELECT query, and then immediately after find out if there are any results (rows), and if so how many.

I'd like to fetch the result as an object not an array, as I like the $obj->col_name syntax, and it just feels a bit wrong to return an array just to find the above out.

$qryh = $conn->query("SELECT ...");

Then use $qryh to first find out if there are any rows, and if so how many.

Can this be done without falling back to using arrays..?

UPDATE:

I know about rowCount(), but I think it only works on UPDATE, INSERT and DELETE - my question relates to SELECT.

UPDATE 2:

I'm using SQL Server and MS Access, not MySQL, and rocount() does not work.

SOLUTION FOUND

The accepted answer lead to the solution. Here is what I found:

I was setting the PDO::ATTR_CURSOR option to PDO::CURSOR_SCROLL when creating the PDO object (new PDO(...), which returned -1 on a rowCount(). However if I set the cursor within ->prepare(... using an array (array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)) it works and returns the row count.

http://technet.microsoft.com/en-us/library/ff628154(v=sql.105).aspx

So, the example on the above link works, setting the cursor when you create the PDO object doesn't work. Also note that in the 'remarks' section on the above link, it mentions PDO::CURSOR_SCROLLABLE which doesn't for me (I get Undefined class constant 'CURSOR_SCROLLABLE').

Upvotes: 0

Views: 726

Answers (5)

meda
meda

Reputation: 45490

You're right, PDO::rowCount does not work properly with SELECT

One workaround is to Add the count to your query

$qryh = $conn->query("SELECT COUNT(*) as numRows FROM Table");
$result = $qryh->(PDO::FETCH_OBJ);;
$count = $result->numRows;

Upvotes: 0

Jason Silberman
Jason Silberman

Reputation: 2491

This is what I do in my database class:

$stmt = $conn->prepare('SELECT ...');
$stmt->execute();

$arrayOfObjects = $stmt->fetchALL(PDO::FETCH_OBJ);
$numberOfRows = count($arrayOfObjects);
print $numberOfRows;

By passing PDO::FETCH_OBJ each object is an stdClass meaning you can use the -> syntax.

I believe this is what you are looking for, and it just uses the PHP count() function instead of mySQL rowCount().

If you want more context, here is the DB class I use in all of my projects: https://gist.github.com/pxlsqre/9f6471220ef187343f54

Upvotes: 0

Takoyaro
Takoyaro

Reputation: 946

Try this way:

$query = "select * from ...";
$stmt = $conn->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
print $stmt->rowCount()

Should work

Upvotes: 1

colburton
colburton

Reputation: 4715

$stmt = $conn->query('SELECT ...');
// number of rows:
$rows = $stmt->rowCount();

// get an array with objects:
$objects = $stmt->fetchAll();

// another way to count the results:
$rows = count($objects);

Upvotes: 1

Ashwin Sekar
Ashwin Sekar

Reputation: 180

$qryh->fetchColumn()

Returns the number of rows selected through a SELECT query

Upvotes: -1

Related Questions