SQL Resource ID #4 from SELECT COUNT?

Question

Possible Duplicate:
How do i “echo” a “Resource id #6” from a MySql response in PHP?

I have a statement:

      $query1 = mysql_query("SELECT COUNT(author) FROM userpages WHERE `author` = '" . $userid . "'") or die();
      echo $query1;

Ignore the amazingly awful naming convention their, its just for testing it before i beef it up. Because im not actually getting data from the table i should just be able to echo the statement shouldnt i? However its giving me an error.

The error is: Resource id #4

Any advice?

Thanks.

@DanNewhouse I added or die() onto it, no change the error is just: Resource id #4 — Ian Taylor, Feb 17 '12 at 14:18

Mark Baker · Answer 1 · 2012-02-17T14:52:26.203

2

mysql_query returns a resultset resource, you have to loop through the resultset using

mysql_fetch_array($query1)

(or similar) to retrieve each record. It doesn't matter whether the query returns 1 row or 100,000 rows, the principle is the same

please read the relevant sections of the manual that explain this in great detail before feeling the need to ask for help.... reading the manual is your friend!

EDIT

while ($row = mysql_fetch_array($res,MYSQL_ASSOC)) { 
   echo $row['COUNT(author)'];   
}

or change your SQL query to

  $query1 = mysql_query("SELECT COUNT(author) AS authorcount FROM userpages WHERE `author` = '" . $userid . "'") or die();

and then

$row = mysql_fetch_array($res,MYSQL_ASSOC))

will return an array with an index of 'authorcount'

echo $row['authorcount'];

edited Feb 17 '12 at 14:52

answered Feb 17 '12 at 14:19

Mark Baker

209,507
32
346
385

And then perhaps consider using mysqli or pdo instead of the horrendously outdated mysql – Mark Baker Feb 17 '12 at 14:22
Ahh thank you! The php manual is really hard for me to understand i dont know why i never have been able to digest any of it :( thanks though i will have a look through – Ian Taylor Feb 17 '12 at 14:27
Just look at example #2 on the page I've linked – Mark Baker Feb 17 '12 at 14:31
Okay thanks, but isnt that returning data from different columns in the database? I just want a count, when i did it in my computing classes in comp sci just doing SELECT COUNT worked fine :/ i must be missing something huge :/ sorry for been dumb here ^^ – Ian Taylor Feb 17 '12 at 14:38
The result of a COUNT is still data, you're using a SELECT statement to retrieve it in exactly the same way you'd use SELECT userId FROM users to retrieve data – Mark Baker Feb 17 '12 at 14:43
So where i have while ($row = mysql_fetch_array($query1)) { $numOfPosts = $row['author']; } what would i have instead of ['author'] ? is this right? – Ian Taylor Feb 17 '12 at 14:48

Kuba · Answer 2 · 2012-02-17T14:37:17.250

1

I think, this will be helpfull

$query = "SELECT COUNT(author) as cnt FROM userpages WHERE `author` = '" . $userid . "'";
echo $query;
$res = mysql_query($query);
while ($row = mysql_fetch_array($res,MYSQL_ASSOC)) {
   print_r($row);  
}

edited Feb 17 '12 at 14:37

answered Feb 17 '12 at 14:19

Kuba

702
5
10

Thanks! getting closer, i get this now: Array ( [0] => 5 [COUNT(author)] => 5 ) – Ian Taylor Feb 17 '12 at 14:27
@KubaW Please, avoid direct variable assignment in if/while/etc.. It is better to use `while ( false!==($row = mysql_fetch_array($res)) )` – Timur Feb 17 '12 at 14:36
I've changed my snippet a little, try now. please red this: [function.mysql-fetch-array](http://www.php.net/manual/en/function.mysql-fetch-array.php) – Kuba Feb 17 '12 at 14:39

score 1 · Accepted Answer · answered Feb 17 '12 at 14:20

1

http://www.php.net/manual/en/function.mysql-query.php

Returned values:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

And:

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

answered Feb 17 '12 at 14:20

Timur

6,668
1
28
37

So, would that mean i have to put like while mysql_fetch_array and have $row = data kinda thing? I tried that but i dont know what the data would be due to the fact im not retrieving any :/ – Ian Taylor Feb 17 '12 at 14:25
But you are retrieving data, you're retrieving a count of the number of userpages for a specific author.... that's data as well – Mark Baker Feb 17 '12 at 14:32
You can do something like this: `$result = array(); while( false!==($row=mysql_fetch_assoc($query1)) )$result[] = $row;` to fetch the data and, then, `var_dump($result);` to see what data is fetched. Also, in your example, in query `SELECT COUNT(..) FROM...`, the only one row can be fetched. So, you can simply do `$row = mysql_fetch_assoc($query1);` and, then, `var_dump($row);` – Timur Feb 17 '12 at 14:33

SQL Resource ID #4 from SELECT COUNT?

3 Answers3