query help

[speedy33417]

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

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

But this is what I get echoed:

Resource id #7

What am I doing wrong?

Thanks.

[alpine]

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);

[HuggieBear]

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

Lets look at your query...

[code=php:0]$sql = "SELECT COUNT(*) FROM pictures WHERE picture_albumid = 1";
[/code]

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...

[code=php:0]$numofpics = mysql_query($sql);[/code]


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 [code=php:0]mysql_fetch_row()[/code] for getting a single row.

So lets give that a try...

[code=php:0]$row = mysql_fetch_row($numofpics);[/code]


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...

[code=php:0]echo "$row[0]";[/code]


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

[speedy33417]

Thanks guys. It really helped me out!

I ended up using the first code

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

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:

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

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

[alpine]

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

[speedy33417]

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

Thanks!