Does fetchAll fetch ALL? :)

Question

Simple question really.. If I have this:

$sql = 'SELECT id, name, address, city, phone FROM users';
$STH = $conn->query($sql);

$row = $STH->fetchAll();

if (count($row) > 0) {

    $id = $row[0]['id'];

}

Will fetchAll ignore what I specifically chose to select and instead select EVERYTHING from the row, including large text fields etc.?

If so, what could be a workaround? I also need the row count

`fetchAll` converts the query results to PHP variables that you can use. But you could have answered your own question simply by seeing what `$row` contains. — Jon, Jul 11 '14 at 13:10
If you just need the row count, why do you select data and not just use `SELECT COUNT(*) ...`? — Sirko, Jul 11 '14 at 13:10
It does what it says in the documentation: "Returns an array containing all of the result set rows". So, it uses the result set—as described by your query—as its basis. But yes, if you just want to count the rows, using `COUNT` seems like the best option. — Matt Gibson, Jul 11 '14 at 13:11
http://stackoverflow.com/questions/883365/row-count-with-pdo — ka_lin, Jul 11 '14 at 13:11
Your SQL syntax is incorrect, there shouldn't be a comma after phone — Mark Baker, Jul 11 '14 at 13:12
@Sirko I just tried using that and dropped it, because then I can't use the same query for while loops etc. And I want to avoid having and extra query just for counting — mowgli, Jul 11 '14 at 13:12
How would you count this? (just rough example): "SELECT id, name, address, city, phone FROM users WHERE id>0 AND id<10 AND city=something ORDER BY RAND() LIMIT 10" — mowgli, Jul 11 '14 at 13:15
I don't just need the row count.. I always ALSO need the row count, from ANY query ;) — mowgli, Jul 11 '14 at 13:17
If you really want that, and you're *only* using MySQL, then consider using SQL_CALC_FOUND_ROWS in your query, and then query FOUND_ROWS() in a followup query to find the total. The count is then calculated on the server, and you don't need to resubmit a different query with the same criteria just to get hold of the row count. — Matt Gibson, Jul 11 '14 at 13:22
@mowgli Do you want to know how many rows your query would have had without the `LIMIT` clause? Have a look at `SQL_CALC_FOUND_ROWS` and the function [FOUND_ROWS](http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_found-rows) — VMai, Jul 11 '14 at 13:23
(At which point this is really just a duplicate of this question: http://stackoverflow.com/questions/460010/work-around-for-php5s-pdo-rowcount-mysql-issue) — Matt Gibson, Jul 11 '14 at 13:24
PDO is great, but to me and my use this num_rows issue is a serious let-down. I can't believe simple standard row-count is not regularly used by most people. I have to use unstable clumsy mysql-only hacks, muuhuuu ;) — mowgli, Jul 11 '14 at 13:25
Well.. For now I'm just going to check if the query returned ANY row. And if I need an accurate row number I'll use fetchall — mowgli, Jul 11 '14 at 13:33
It's not PDO's fault, by the way. It's basically down to what the specific database drivers can handle—PDO row counts on SELECT statements work for some other database engines. It's just that MySQL generally regards an up-front count of all rows for a result set as an expensive thing to do, and, because it's not usually necessary for a query, doesn't do it by default, but instead gives you a workaround for when you need it. — Matt Gibson, Jul 11 '14 at 13:48
@MattGibson Yes, well now that I'm thinking about, maybe I don't need specific row count all that often. It's also a habit. Wanting to get A NUMBER, and check on that, and maybe use it for something (I do that 40% of the time). mysql_num_rows was so easy I didn't think about it, it was always there — mowgli, Jul 11 '14 at 13:52

score 1 · Accepted Answer · answered Jul 11 '14 at 13:25

1

Fetch all simply means "fetch all rows into an array". It does not mean "fetch all columns, also the ones which I did not select" since this would be a quite awkward behaviour.

So, no, you need no workaround.

answered Jul 11 '14 at 13:25

gexicide

38,535
21
92
152

Straight answer, thanks. I asked because to me it seemed like maybe it DID select EVERYTHING – mowgli Jul 11 '14 at 13:30
Ok now I understand it better ;) So obviously using fetchAll is not the best to use for counting, for large amounts of rows/data – mowgli Jul 11 '14 at 13:44
@mowgli: If you really only need the count, then it is not, no. – gexicide Jul 11 '14 at 13:58
I just want to have row count on all my queries, automatically.. Guess it's not optimal. Too used to mysql_num_rows – mowgli Jul 11 '14 at 13:59
Unless I only use fetchAll when handling ONE row, then I guess it would be ok. But then I don't need extensive row_count ;) – mowgli Jul 11 '14 at 14:00

Does fetchAll fetch ALL? :)

1 Answers1