My SQL query is acting wierd

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
trvo
Forum Newbie
Posts: 7
Joined: Tue Feb 03, 2004 5:34 pm

My SQL query is acting wierd

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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 ).
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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.
trvo
Forum Newbie
Posts: 7
Joined: Tue Feb 03, 2004 5:34 pm

Post 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

?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I know a workaround though:

SELECT COUNT(*) AS count and then use $dbArray['count'];
trvo
Forum Newbie
Posts: 7
Joined: Tue Feb 03, 2004 5:34 pm

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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)..
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

the highest id number can be found the exact same way, just change the query to

SELECT MAX(mytable_id) FROM tablename
trvo
Forum Newbie
Posts: 7
Joined: Tue Feb 03, 2004 5:34 pm

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

Oops, my fault, replace $r with $result
trvo
Forum Newbie
Posts: 7
Joined: Tue Feb 03, 2004 5:34 pm

Post 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
Post Reply