Jump to content


Photo

query help


  • Please log in to reply
5 replies to this topic

#1 speedy33417

speedy33417
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 26 September 2006 - 02:30 PM

Quick newbie question. I'm trying to get a count of albums in my table:

        $sql = "SELECT COUNT(*) 
                FROM pictures 
                WHERE picture_albumid = 1"; 
                
        $numofpics = mysql_query($sql); 
        
        echo $numofpics;

But this is what I get echoed:

Resource id #7

What am I doing wrong?

Thanks.

#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 26 September 2006 - 02:33 PM

From the manual: For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE on error.
http://no.php.net/mysql_query


$total = mysql_result(mysql_query("SELECT COUNT(*) FROM pictures WHERE picture_albumid = 1"),0);

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 26 September 2006 - 10:17 PM

You need to do something with your result resource to extract the data from it.

Lets look at your query...

$sql = "SELECT COUNT(*) FROM pictures WHERE picture_albumid = 1";

Now count() is what's known as a single row query, if you use count() in a statement, you're only expecting one row returned, so if you get your result resource like so...

$numofpics = mysql_query($sql);


We expect $numofpics to be one row, which it is.  BUT we can't just echo it out, as you've found out, we need to extract that data, so a search of the manual tells us we can use
mysql_fetch_row()
for getting a single row.

So lets give that a try...

$row = mysql_fetch_row($numofpics);


What this has done is put the row into an array, with each element representing a column, seen as though we only selected one column, our array only contains one element.  So to echo the first element in an array all we need is...

echo "$row[0]";


I hope this helped.  Remember this is only for a single row, if you have multiple rows you'll need something like mysql_fetch_array() and you'll need to loop through them.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 speedy33417

speedy33417
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 27 September 2006 - 09:09 PM

Thanks guys. It really helped me out!

I ended up using the first code

$total = mysql_result(mysql_query("SELECT COUNT(*) FROM pictures WHERE picture_albumid = 1"),0);

It works great, but now I need to use a variable instead of a plain number and I'm getting an error again.
The statement is supposed to find out how many albums I have to display. album_root can have a value of "main" or the name of a sub-album. If it's "main" that means it's displayed on the main page.

Here's the statement:

$album = "main";
$total = mysql_result(mysql_query("SELECT COUNT(*) FROM albums WHERE album_root = $album"),0);

Right now I'm manually feeding the variable $album. But it doesn't seem to work... ???

#5 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 27 September 2006 - 09:13 PM

put single quotes around $album --> '$album' inside the query

#6 speedy33417

speedy33417
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 28 September 2006 - 01:13 AM

Would you be surprised if I told you that I'm new to php?  ;D

Thanks!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users