2 SQL querys, one is counting.

inkfish

Hello,

Am I not allowed to use two sql statements after eachother or what? I did this:

	$show_photos_query = "SELECT * FROM photos_cat WHERE id = '$photoscat_id'";
$show_photos_result = mysql_query($show_photos_query) or die (mysql_error());
$row = mysql_fetch_array($show_photos_result);

$count_query = "SELECT COUNT(*) FROM photos WHERE category = '$photoscat_id'";
$count_result = mysql_query($count_query) or die (mysql_error());

And this resulted in that nothing showed up.
One more thing how do i display the number of records that the...

$count_query = "SELECT COUNT(*) FROM photos WHERE category = '$photoscat_id'";

...query has found?

Thank you!

Weedpacket

Well, $count_result is a result set the same as $result is; it will have one row, and one field. So, to answer your second question: the same way you'd display any other field from any other record from any other result set. If memory serves, the field would be called "count", although SQL allows you to name such fields however you please: "SELECT COUNT(*) AS count_photos FROM ...". And then the field would be called count_photos.

The obvious (too obvious) reason why nothing will show up is that there's no command given to do so (no doubt you've cropped that part of the code). How are you trying to display it?

inkfish

I do display it futher down in the code.
Well when i have this:

$show_photos_query = "SELECT * FROM photos_cat WHERE id = '$photoscat_id'";
$show_photos_result = mysql_query($show_photos_query) or die (mysql_error());
$row = mysql_fetch_array($show_photos_result);

$count_query = "SELECT COUNT(*) FROM photos WHERE category = '$photoscat_id'";
$count_result = mysql_query($count_query) or die (mysql_error());

nothing shows. but if i remove the last:

$count_result = mysql_query($count_query) or die (mysql_error());

then everything shows as normal. but i want to use the second sql query to know how many photos i have so i can do. Images: 7 (for example).

inkfish

Okey im attaching a picture to this post, this is what i want to acomplish.

Using: $show_photos_query = "SELECT * FROM photos_cat WHERE id = '$photoscat_id'";

Gallery: Name
Date: date

Using: $count_query = "SELECT COUNT(*) FROM photos WHERE category = '$photoscat_id'";

Images: 8 (for example).

You see Size: in the picture too but ignore that, that shouldn't be there.

Weedpacket

Originally posted by inkfish
$count_result = mysql_query($count_query) or die (mysql_error());
[/b]

Okay, so at this point $count_result is a MySQL result set (not a number). ... then what are you doing?

inkfish

Hello,

Sorry for the delay, here comes an description of what I am doing:

I am making a gallery page. When i click on a link a gellery apears and first im going into the database tabel (photos_cat) and get some information about that gallery. But in photos_cat i can't get the information: how many photos is it in this category. BUT in the database tabel "photos" all my photos (or path to photos) are located. Every photo has a field named "category" where they have the category's id. Now with the second SQL string i gave u i want to get out how many photos there are in the gallery and then just display it on the page like on the picture:

Images: 6 (for example)

I hope this tells u what i am doing, if not ask again and i'll try a second time =P.

Thanks!

herve

Hi,

Modify the second request as Weedpacket tells you :

$count_query = "SELECT COUNT(*) as count_photos FROM photos WHERE category = '$photoscat_id'";

$count_result = mysql_query($count_query) or die (mysql_error());

then add :

$myrow = mysql_fetch_array($count_result)
$count_photos = $myrow["count_photos"];

that way, in $count_photos you have the number
you need.

Hervé.

inkfish

Hello,

Okey, thanks! I will test it when i come home. Hope i don't need to come back to this thread =P.

inkfish

Sorry, I'm back =/
I get an error:

Unknown column 'category' in 'where clause'

....unknown column 'category' <-- i understand that.
....in 'where clause' <-- IN WHAT?

inkfish

Hello "again". =)
Sorry for my dumbness, the thing was that my field wasn't called category it was called gallery. now everything works fine.

Thank you all (two) for the help =)