0

Hi i tried get a table count in php/mysql assignment, but it gives an error called

Resource id #5

My code

$table_count = mysql_query("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name LIKE 'table_%'");

echo($table_count);

can anybody help on this matter

Prog_Rookie
  • 438
  • 7
  • 23
  • You need to fetch the record to assign to a variable. Check for mysql_fetch_row , mysql_fetch_assoc etc. – Abhik Chakraborty Nov 11 '14 at 10:46
  • use a loop over result or extract result from query result mysql_fetch_assoc() – Rakesh Sharma Nov 11 '14 at 10:46
  • 1
    You don't get an error, but a resource with the id 5. Use something likee [`mysql_fetch_assoc()`](http://php.net/manual/en/function.mysql-fetch-assoc.php) to fetch the resource – kero Nov 11 '14 at 10:47
  • +1 for asking a question which many php beginners don't know! I too wasted my time solving this kind of errors!!Thank you – Sudhir kumar Nov 11 '14 at 11:04

3 Answers3

0

First of all, do not use mysql_* functions, becuase they are deprecated. Use mysqli_* or PDO instead.

Second: mysql_query gives you back a resource. You need to fetch the row(s) from that with mysql_fetch_assoc function.

So you need something like this:

$res = mysql_query("SELECT COUNT(*) AS cnt FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name LIKE 'table_%'");
$row = mysql_fetch_assoc($res);
var_dump($row["cnt"]);
vaso123
  • 12,347
  • 4
  • 34
  • 64
  • Hi thank you for all your answers but now it returns "Array ( [COUNT(*)] => 13 ) " which is correct, but isnt there a way i could get only "13" as the result – Prog_Rookie Nov 11 '14 at 11:10
  • `$row["cnt"]` will gives you back 13, not `Array` This is how the associative arrays works. – vaso123 Nov 11 '14 at 11:21
0

Try this format for getting count(column):-

$sql = "SELECT count(*) from table name where condition" ;  //your mysql query inside paranthesis
mysql_select_db(''); //your database name inside quotes

$retval = mysql_query($sql, $conn); 
$row = mysql_fetch_array($retval,MYSQL_BOTH); 

echo $row[0]; // your result

Hope this helps!!

Sudhir kumar
  • 549
  • 2
  • 8
  • 31
  • @user3584871 According to my knowledge the error is because you are trying to print the array but not the array[0] result.Hence the error "Resource id #5" will come. – Sudhir kumar Nov 11 '14 at 11:02
  • This will gives you back: `Array` – vaso123 Nov 11 '14 at 11:06
  • Hi thank you for all your answers but now it returns "Array ( [COUNT(*)] => 13 ) " which is correct, but isnt there a way i could get only "13" as the result – Prog_Rookie Nov 11 '14 at 11:07
  • @lolka_bolka :- Ya! Sorry I thought I have added row[0]! Thanks for the concern! Try to remove the comment I have edited the answer!! – Sudhir kumar Nov 11 '14 at 11:11
-1

Please note, mysql_* functions have been deprecated and will be removed in future. It is strongly advised to use PDO or mysqli extension.

`$table_count = mysql_query("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name LIKE 'table_%'");

echo(mysql_num_rows($table_count));`

Anand Shah
  • 14,575
  • 16
  • 72
  • 110
  • This is bad. It will always gives you back 1, because OP selects `COUNT(*)` not rows. – vaso123 Nov 11 '14 at 10:51
  • Hi thank you for all your answers but now it returns "Array ( [COUNT(*)] => 13 ) " which is correct, but isnt there a way i could get only "13" as the result – Prog_Rookie Nov 11 '14 at 11:11