counting total in/after while loop

(as first, I have to apologize for my clumsy English...)

here is my situation:
// I'm stupid... sort of. ;)

I would like to see how many pictures have "lost" their albums - I have
table "album" with some id, I have table "photo" with pict's id and
album's id so I can know where the picture belong, but sometimes some
users delete their album form database without moving pictures to
different (existing) album.
for the beginning I would like my members to see how many of those
so, I have those few queries:

<?
$q_total = "SELECT count(*) FROM photo";
$r_total = mysql_query($q_total) or die("q_total died [$q_total]");
list($total) = mysql_fetch_array($r_total);

$q_album = "SELECT id FROM album";
$r_album = mysql_query($q_album) or die("q_album died [$q_album]");
while(list($al_id) = mysql_fetch_array($r_album)) {
$q_exist = "SELECT count(*) FROM photo WHERE album=$al_id";
$r_exist = mysql_query($q_exist) or die("q_exist died [$q_exist]");
list($exist) = mysql_fetch_array($r_exist);
}

$lost = $total - $exist;
?>

I am aware that with this last line of code I'm getting only number of
existing pics in very last album, not in all albums... I know that I
should use something like foreach(but what?)

so... can, please, anyone help me with that?
tnx

--
Ja NE
http://fotozine.org/?omen=janimir
--

re: counting total in/after while loop

..oO(Ja NE)
[color=blue]
>I would like to see how many pictures have "lost" their albums - I have
>table "album" with some id, I have table "photo" with pict's id and
>album's id so I can know where the picture belong, but sometimes some
>users delete their album form database without moving pictures to
>different (existing) album.[/color]

With FOREIGN KEY constraints such inconsistencies could be prevented.
The database could reject any DELETE attempt as long as the album is not
empty. Or it could silently delete the images as well.

Micha

re: counting total in/after while loop

Michael Fesser <netizen@gmx.de> wrote:
[color=blue]
> .oO(Ja NE)
>[color=green]
> >I would like to see how many pictures have "lost" their albums - I have
> >table "album" with some id, I have table "photo" with pict's id and
> >album's id so I can know where the picture belong, but sometimes some
> >users delete their album form database without moving pictures to
> >different (existing) album.[/color]
>
> With FOREIGN KEY constraints such inconsistencies could be prevented.
> The database could reject any DELETE attempt as long as the album is not
> empty. Or it could silently delete the images as well.
>[/color]

no... I don't wont to delete pictures, I would rather like to remind
members that they have some nonviewable (is this a word?) pictures...
anyway, I think I finaly did a math:

<?
$q_total = "SELECT count(*) FROM photo";
$r_total = mysql_query($q_total) or die("q_total died [$q_total]");
list($total) = mysql_fetch_array($r_total);

$q_album = "SELECT id FROM album";
$r_album = mysql_query($q_album) or die("q_album died [$q_album]");
while(list($al_id) = mysql_fetch_array($r_album)) {
$q_exist = "SELECT count(*) FROM photo WHERE album=$al_id";
$r_exist = mysql_query($q_exist) or die("q_exist died [$q_exist]");
list($exist) = mysql_fetch_array($r_exist);

$exist_array[$al_id]=$exist;
sort($exist_array);
}

$existing = array_sum($exist_array);
$lost = $total - $existing;
?>

it is working... maybe is there some shorter and more eficient way, but
I just don't understood arrays yet/still...

--
Ja NE
http://fotozine.org/?omen=janimir
--

re: counting total in/after while loop

..oO(Ja NE)
[color=blue]
>no... I don't wont to delete pictures[/color]

OK, but you should prevent DB inconsistencies. It should not be possible
to delete albums that still contain images. The DB can do this for you.

Micha

re: counting total in/after while loop

Ja NE wrote:[color=blue]
> it is working... maybe is there some shorter and more eficient way, but
> I just don't understood arrays yet/still...[/color]

What database are you using? The following works in PostgreSQL 8.1:

'SELECT * FROM photo
WHERE album_id NOT IN (SELECT album_id FROM album)'

This will return all of the records in table "photo" that belong to
an album that doesn't have a corresponding ID in table "album".

- Ryan