Page 1 of 1

query help

Posted: Tue Sep 26, 2006 7:25 am
by speedy33417
How do I get the number items I query without looping out my result?

This problem is coming up for me as I'm working on a photo album. I display a thumb for each album that the user can select and list how many pictures are in that album, but obviously I wouldn't want display anything from those albums I only need the number of pictures.

Code: Select all

$sql = "SELECT picture_id FROM pictures
     WHERE picture_albumid = something";
$results = mysql_query($sql);
How do put the number of results in a variable?

Posted: Tue Sep 26, 2006 7:41 am
by Rovas
First read the PHP manual here http://php.net/manual/en/function.mysql-num-rows.php or you can use the COUNT statement in your query.

Posted: Tue Sep 26, 2006 7:41 am
by volka
Take a look at http://www.w3schools.com/sql/func_count_ast.asp
edit:
with a GROUP BY and JOIN you can pull the album + number of pictures all together with only one query.

Posted: Tue Sep 26, 2006 7:56 am
by speedy33417
Awesome! That's exactly what I was looking for.

Thanks a bunch guys.

Posted: Tue Sep 26, 2006 8:28 am
by speedy33417
Sorry. I gave it a try...

Code: Select all

$sql = "SELECT COUNT(*)
		FROM pictures
		WHERE picture_albumid = 1";
		
	$numofpics = mysql_query($sql);
	
	echo $numofpics;
And I get this echoed to the screen:

Resource id #7

What am I doing wrong?

Posted: Tue Sep 26, 2006 8:45 am
by feyd
Echoing a result resource. mysql_fetch_assoc() and it's siblings should be of interest.

Posted: Tue Sep 26, 2006 9:50 pm
by shneoh
an easy function will help: mysql_num_rows().

It will shows number of rows in your query. Use it right after your query as following example:

Code: Select all

$sql = mysql_query("Select * from tableA where columnA = 1");

$nums = mysql_num_rows($sql);
echo $nums;