Selective querying

[friz]

Sorry if the topic title is unclear, I didn't know how to desribe this problem :o

I'm trying to make a gig list where I can see when an info request came in, when and if we answered the request, when and if the gig is confirmed.

I'm using 2 tables:
Trouwmuziek
trouw_id
datum
contact
tel
email
hoe
adres
gemeente
aanwezig
aanvang
bid

trouwstatus
id
trouw_id
status
datum

When an info request is submitted an entry is made in the first and second table (trouwstatus > status > "aanvraag").

I've also made a page where I can change to status to "gereageerd" (replied) and "bevestigd" (confirmed).
I want to keep the date/time when we received the info request, when we replied to it and when it's confirmed. So, some entries look like

id trouw_id status datum
6 5 Gereageerd 2009-11-12 08:00:00
5 5 Aanvraag 2009-11-12 06:00:00
9 5 Bevestigd 2009-11-12 19:34:56

Now I want an overview of all the requests and a seperate one of all the replied ones. This is the code I have so far:

//List requests
$query = "SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente, DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id, b.status, b.id, UNIX_TIMESTAMP(b.datum) AS dedag2
FROM trouwmuziek a, trouwstatus b
WHERE a.datum >= curdate()
AND a.trouw_id = b.trouw_id
AND b.status ='aanvraag'
ORDER BY a.datum ASC";
$result = mysql_query($query);
if ( !$result ) {
echo "Fout bij openen van database voor weergave...<br>";
} else {
$numrows = mysql_num_rows($result);
if ( 0 == $numrows ) {
echo "geen trouwen in het vooruitzicht...<p>&nbsp;</p>";
} else {
setlocale(LC_TIME, 'dutch');
echo "<table class='tabel'>";
echo "<thead><tr><th scope='col'>#</th>
<th scope='col'>Datum</th>
<th scope='col'>Uur</th>
<th scope='col'>Gemeente</th>
<th scope='col'>Status</th>
<th scope='col'>Aanvullende info</th>
<th scope='col'>Bewerken</th>
</tr></thead>";

for ( $i=0; $i < $numrows; $i++ ) {

$datum = mysql_result ( $result, $i, "dedag");
$datum2 = mysql_result ( $result, $i, "dedag2");
$gemeente = mysql_result ( $result, $i, "a.gemeente");
$id = mysql_result ( $result, $i, "a.trouw_id");
$status = mysql_result ( $result, $i, "b.status");
$aanvang = mysql_result ( $result, $i, "aanvang");
$aantal = $i+1;
$dedag = strftime("%a %d %b %Y", $datum);
$dedag2 = strftime("%d %b %Y", $datum2);

echo "<tr><td>$aantal</td>
<td>$dedag</td>
<td>$aanvang</td>
<td>$gemeente</td>
<td>$status op $dedag2</td>
<td><a href=# onclick=InfoPopup('infopop.php?id=$id&tabel=$tabel') /><img src='../images/external.png' width='10' height='10' border='0' alt=''></a></td>
<td><a class='statuslink' href=trouwaanvragen_edit.php?trouw_id=$id>Bewerken</a><br />
<a class='statuslink' href=trouwaanvragen.php?trouw_id=$id&action=reactie>Gereageerd</a><br />
<a class='statuslink' href=trouwaanvragen.php?trouw_id=$id&action=bevestigd>Bevestigd</a><br />
<a class='statuslink' href=trouwaanvragen.php?trouw_id=$id&action=delete>Geannuleerd</a></td>
</tr>";

}
}
echo "</table><br><br>";


//List replied

echo "<h1>Gereageerd</h1>";

$query = "SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente, DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id, b.status, b.id, UNIX_TIMESTAMP(b.datum) AS dedag2
FROM trouwmuziek a, trouwstatus b
WHERE a.datum >= curdate()
AND a.trouw_id = b.trouw_id
AND b.status ='gereageerd'
ORDER BY a.datum ASC";
$result = mysql_query($query);
if ( !$result ) {
echo "Fout bij openen van database voor weergave...<br>";
} else {
$numrows = mysql_num_rows($result);
if ( 0 == $numrows ) {
echo "geen trouwen in het vooruitzicht...<p>&nbsp;</p>";
} else {
setlocale(LC_TIME, 'dutch');
echo "<table class='tabel'>";
echo "<thead><tr><th scope='col'>#</th>
<th scope='col'>Datum</th>
<th scope='col'>Uur</th>
<th scope='col'>Gemeente</th>
<th scope='col'>Status</th>
<th scope='col'>Aanvullende info</th>
<th scope='col'>Bewerken</th>
</tr></thead>";

for ( $i=0; $i < $numrows; $i++ ) {

$datum = mysql_result ( $result, $i, "dedag");
$datum2 = mysql_result ( $result, $i, "dedag2");
$gemeente = mysql_result ( $result, $i, "a.gemeente");
$id = mysql_result ( $result, $i, "a.trouw_id");
$status = mysql_result ( $result, $i, "b.status");
$aanvang = mysql_result ( $result, $i, "aanvang");
$aantal = $i+1;
$dedag = strftime("%a %d %b %Y", $datum);
$dedag2 = strftime("%d %b %Y", $datum2);

echo "<tr><td>$aantal</td>
<td>$dedag</td>
<td>$aanvang</td>
<td>$gemeente</td>
<td>$status op $dedag2</td>
<td><a href=# onclick=InfoPopup('infopop.php?id=$id&tabel=$tabel') /><img src='../images/external.png' width='10' height='10' border='0' alt=''></a></td>
<td><a class='statuslink' href=trouwaanvragen_edit.php?trouw_id=$id>Bewerken</a><br />
<a class='statuslink' href=trouwaanvragen.php?trouw_id=$id&action=bevestigd>Bevestigd</a><br />
<a class='statuslink' href=trouwaanvragen.php?trouw_id=$id&action=delete>Geannuleerd</a></td>
</tr>";


}
echo "</table><br><br>";
}
}
mysql_close();
}
?>

The problem is that the "Request List" still shows replied entries, and the "Replied List" still shows the confirmed entries, which is normal of course with this code.

My questions:
- How should I change the code so the request list doesn't show the replied entries, and the replied list doesn't show the confirmed entries
- I would like the replied list to show the date when we replied (like it does now), but also when the request came in.

I'm guessing this should be done with a query in a query. Something like:
SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente, DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id, b.status, SELECT b.id FROM trouwstatus b WHERE status !="aanvraag", UNIX_TIMESTAMP(b.datum) AS dedag2
FROM trouwmuziek a, trouwstatus b
but I totally forgot how to do that :confused:

Thank you in advance for the help!!

[Old Pedant]

Are you saying that you want your Request List to *ONLY* show those entries that have *NOT* been answered or confirmed??

And for the other list, you want to show ONLY those entries you have answered but that are NOT yet confirmed??

If that's what you mean then:

SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente,
DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id,
b.status, b.id, UNIX_TIMESTAMP(b.datum) AS dedag2
FROM trouwmuziek a INNER JOIN trouwstatus b
ON ( a.trouw_id = b.trouw_id AND b.status ='aanvraag' )
LEFT JOIN trouwstatus c
ON ( b.trouw_id = c.trouw_id AND c.status <> 'aanvraag' )
WHERE a.datum >= curdate()
AND c.trouw_id IS NULL
ORDER BY a.datum ASC";

It's a sneaky trick: First you do the inner join to the status table to find a record with status of "aanvraag" then you do *another* join (but this time an outer join) to the *same* table looking for a record where the status is *NOT* "aanvraag".

Then the WHERE clause rejects the "a" record if you DID find a status record that is not "aanvraag". So the result is that you only get "a" records that have *ONLY* "aanvraag" status records.

You should be able to use similar logic for the other table display, no?

[Old Pedant]

Get those two queries working, first, and then I'll help you on the other problem (the two dates). That will need to JOIN to the status table *THREE* times (two INNER and on LEFT OUTER).

[friz]

Thank you!!
I didn't quite understand the left join part, but I've figured it out (I think) :D

ONLY answered entries but NOT yet confirmed:
SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente, DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id, b.status, b.id, UNIX_TIMESTAMP(b.datum) AS dedag2
FROM trouwmuziek a INNER JOIN trouwstatus b
ON ( a.trouw_id = b.trouw_id AND b.status ='gereageerd' )
LEFT JOIN trouwstatus c
ON ( b.trouw_id = c.trouw_id AND c.status <> 'gereageerd' AND c.status <> 'aanvraag')
WHERE a.datum >= curdate()
AND c.trouw_id IS NULL
ORDER BY a.datum ASC

[Old Pedant]

Yes. A *tiny* big simpler would be:

SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente, DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id, b.status, b.id, UNIX_TIMESTAMP(b.datum) AS dedag2
FROM trouwmuziek a INNER JOIN trouwstatus b
ON ( a.trouw_id = b.trouw_id AND b.status ='gereageerd' )
LEFT JOIN trouwstatus c
ON ( b.trouw_id = c.trouw_id AND c.status NOT IN('gereageerd','aanvraag') )
WHERE a.datum >= curdate()
AND c.trouw_id IS NULL
ORDER BY a.datum ASC

Back in a while...

[Old Pedant]

See if this works:
SELECT UNIX_TIMESTAMP(a.datum) AS dedag, a.trouw_id, a.gemeente,
DATE_FORMAT(a.aanvang, '%H h %i') as aanvang, b.trouw_id, b.status, b.id,
UNIX_TIMESTAMP(b.datum) AS gereageerd_dedag,
UNIX_TIMESTAMP(aa.datum) AS aanvraag_dedag
FROM trouwmuziek AS a INNER JOIN trouwstatus AS b
ON ( a.trouw_id = b.trouw_id AND b.status ='gereageerd' )
INNER JOIN trouwstatus AS aa
ON ( a.trouw_id = aa.trouw_id AND aa.status ='aanvraag' )
LEFT JOIN trouwstatus c
ON ( b.trouw_id = c.trouw_id AND c.status = 'bevestigd' )
WHERE a.datum >= curdate()
AND c.trouw_id IS NULL
ORDER BY a.datum

[friz]

Perfect!! :thumbsup:

Thank you so much!!!