Posted by Ja NE on 03/02/06 12:53

Michael Fesser <netizen@gmx.de> wrote:

> .oO(Ja NE)
>
> >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.
>
> 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.
>

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

[Back to original message]