Restricting query results (WHERE ... IN, WHERE EXISTS)
Posted: Fri Aug 06, 2004 2:17 am
Hello together,
i have two database tables containing event information; the first one stores metadata (date, time, ...) and a room id, the second one keeps the room id and the room name.
"My" users can fill out a three-step form to insert (new) events; thereby, the metadata appear in the first, speaker & room information in the second and release notes (release date, release range etc.) in the third step.
Currently, the list of available rooms appearing in the second step is fed by the 'rooms' table:
To avoid double-booked rooms, i thought about restricting the room list above via date and time information entered in the first step: a first query
should extract the room id's booked at the specified time, and the second query should display the room list like in the first example except those booked rooms.
And here lies my problem - just saying "WHERE events.locID != eventloc.locID" doesn't seem to work. And using
produced the error:
My database server is a SunFire V240 (Solaris 9, PHP 4.3.4, MySQL 4.0.18)
Did anyone of you had a similar problem and found a solution?
Greez,
- bluenote
i have two database tables containing event information; the first one stores metadata (date, time, ...) and a room id, the second one keeps the room id and the room name.
"My" users can fill out a three-step form to insert (new) events; thereby, the metadata appear in the first, speaker & room information in the second and release notes (release date, release range etc.) in the third step.
Currently, the list of available rooms appearing in the second step is fed by the 'rooms' table:
Code: Select all
<?php
$locationquery = "SELECT eventloc.location FROM eventloc ORDER BY eventloc.location ASC";
$locationerg = MYSQL_QUERY($locationquery);
$locationnumrows = MYSQL_NUM_ROWS($locationerg);
if ($locationnumrows > '0') {
echo "<select name="location" class="formKontakt">";
switch ($LANG) {
case 'en': echo "<option value="0" selected>Not specified</option>"; break;
case 'ge': echo "<option value="0" selected>Nicht ausgewählt</option>"; break;}
$locationc = 0;
while ($locationc < $locationnumrows) {
$location = mysql_result($locationerg,$locationc,"location");
echo "<option value="$location">$location</option>";
$locationc++;}
echo "</select>";}
?>Code: Select all
<?php
$location_01query = "SELECT events.locID FROM events WHERE events.year = '$year_entered' AND events.month = '$month_entered' AND events.day = '$day_enetered' AND events.time = '$time_entered' GROUP BY events.locID ASC";
$location_01erg = MYSQL_QUERY($location_01query);
$location_01numrows = MYSQL_NUM_ROWS($location_01erg);
if ($location_01numrows > '0') {
$location_01 = 0;
$location_blocked = mysql_result($location_01erg,$location_01,"locID");
?>And here lies my problem - just saying "WHERE events.locID != eventloc.locID" doesn't seem to work. And using
Code: Select all
<?php
SELECT eventloc.locID FROM eventloc WHERE eventloc.locID IN (SELECT events.locID FROM events WHERE events.year = '$year_entered' AND events.month = '$month_entered' AND events.day = '$day_enetered' AND events.time = '$time_entered' GROUP BY events.ort ASC);
?>Code: Select all
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECTevents.locID
FROM eventsDid anyone of you had a similar problem and found a solution?
Greez,
- bluenote