Page 1 of 1

My SQL query is acting wierd

Posted: Tue Feb 03, 2004 5:34 pm
by trvo
Hi All,

I am using this query to find out the total number of entries in my MYSQL database:

SELECT COUNT( * ) FROM images

But it returns this every time:

Resource id #2

Instead of 4 (what I want at the moment)

At the moment I only have 4 entries in the database as it is new. I am using other queries on exactly the same database using the same connection scripts which are working. I have tried the above query in PHPMyAdmin and it comes up with 4 (which is what I want). Can anyone suggest what I am doing wrong?

Thanks and Kind Regards - Trvo

Posted: Tue Feb 03, 2004 5:37 pm
by infolock
how are you trying to return the value? could you show us some code? (preferably only the section where you are doing the query and then calling the result ).

Posted: Tue Feb 03, 2004 5:53 pm
by DuFF
Here is how I do it:

Code: Select all

<?php
$query = "SELECT COUNT( * ) FROM images";
$result = mysql_query($query) or die("MySQL query failed: " . mysql_error());
$dbArray = mysql_fetch_assoc($result);
echo $dbArray['COUNT(*)'];
?>
If you ever get a "Resource id #X" error then it means you have to do a mysql_fetch_array (or mysql_fetch_assoc) on the $result.

Posted: Wed Feb 04, 2004 1:46 am
by trvo
Hi,

Thanks for your reply, the code doesn't seem to output anything at all?

Here is what I had put in:

Code: Select all

$sql = "SELECT COUNT( * ) FROM images"; 
$result2 = mysql_query($sql) or die("MySQL query failed: " . mysql_error()); 
$dbArray = mysql_fetch_assoc($result2); 
echo $dbArray['COUNT(*)'];
(it is the same as above but with some variables renamed because I was using them already).

Can anyone see what I am doing wrong here?

Thanks and kind regards - Trvo

?>

Posted: Wed Feb 04, 2004 2:39 am
by timvw
I know a workaround though:

SELECT COUNT(*) AS count and then use $dbArray['count'];

Posted: Wed Feb 04, 2004 3:14 pm
by trvo
Hi,

I have just tried timvw's suggestion, I am still getting no output at all. Don't worry about it, something is obviously wrong in my code somewhere (as all your queries work in phpMyAdmin). I shal just stick to manually entering the number of lines in the database.

One last quick question though, I need to know how to find the last entry in my database. IE, the entry with the greatest value in the id column of my table. (I just need the digit in the id column returned to my page).

Thanks for all your help

Kind Regards - Trvo

Posted: Wed Feb 04, 2004 3:28 pm
by Stoker

Code: Select all

<?php
  $db = mysql_connect('localhost','user','pass');
  if (!$db) die ('Unable to connect to db');
  if (!mysql_select_db('dbname')) die ('Unable to select db');
  $result = @mysql_query('SELECT count(*) FROM table');
  if (!$r) die ('Query for count failed: '.mysql_error());
  list($count) = mysql_fetch_row($r);
  echo 'I counted '.$count.' rows.<br />'."\n";
?>
Fetch assoc is great when getting named columns and explicit aliased results, when fetching single values like this, fetch row a tiny bit faster (index sorted)..

Posted: Wed Feb 04, 2004 3:30 pm
by Stoker
the highest id number can be found the exact same way, just change the query to

SELECT MAX(mytable_id) FROM tablename

Posted: Wed Feb 04, 2004 3:43 pm
by trvo
Hi,

Sorry to keep being a pain. Just tried it out and it returns this:

Query for count failed:

The .mysql_error() doesn't seem to be working either. Could it be because I am running this on my laptop (for trial purposes) with EasyPHP (It is Apache, MYSQL and PHP all in one for free)?

When I create tables, databases, edit them, browse them with SQL queries (from my PHP pages) it seems to work fine though???

EDIT: To make sure it wasn't conflicting with my page I was trying to put it into I created a new page and run it from there.

Im puzzled. I am sure you guys are right.

Kind Regards - Trvo

Posted: Wed Feb 04, 2004 4:05 pm
by Stoker
Oops, my fault, replace $r with $result

Posted: Wed Feb 04, 2004 4:09 pm
by trvo
Hi,

Thanks a million, everything now works, sorry for being such a pain, only been using PHP for 4 days now. I know a few other programming languages which have helped a lot though (SQL wasn't one of them).

Cheers - Trvo :D