Page 1 of 1

Restricting query results (WHERE ... IN, WHERE EXISTS)

Posted: Fri Aug 06, 2004 2:17 am
by bluenote
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:

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&auml;hlt</option>"; break;}
		
		$locationc = 0;
		
		while ($locationc < $locationnumrows) {
			
			$location = mysql_result($locationerg,$locationc,"location");
			
			echo "<option value="$location">$location</option>";
			
			$locationc++;}
			
			echo "</select>";}

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

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");

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

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

?>
produced the error:

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

Posted: Fri Aug 06, 2004 5:29 am
by bluenote
I worked around a little:

Code: Select all

<?php

$locID_01query = "SELECT events.locID FROM events WHERE events.year = '$new_year' AND events.month = '$new_month' AND events.day = '$new_day' AND events.time = '$new_time' GROUP BY events.locID ASC";

$locID_01erg = MYSQL_QUERY($locID_01query);

$locID_01numrows = MYSQL_NUM_ROWS($locID_01erg);

if ($locID_01numrows=='0') {
	
	$locID_02query = "SELECT eventloc.locID FROM eventloc ORDER BY eventloc.locID ASC";
	
	$locID_02erg = MYSQL_QUERY($locID_02query);
	
	$locID_02numrows = MYSQL_NUM_ROWS($locID_02erg);
	
	if ($locID_02numrows > '0') {
		
		$locID_02 = 0;
		
		while ($locID_02 < $locID_02numrows) {
			
			$locID_available = mysql_result($locID_02erg,$locID_02,"locID");
			
			echo "<h2>$locID_02: $locID_available</h2><br>\n";
			
			$locID_02++;}}}

?>
This works fine. The next step (1 result)

Code: Select all

<?php

else if ($locID_01numrows=='1') {
				
				$locID_01 = 0;
				
				$locID_blocked = mysql_result($locID_01erg,$locID_01,"locID");
				
				$locID_02query = "SELECT eventloc.locID FROM eventloc WHERE eventloc.locID != '$locID_blocked' ORDER BY eventloc.locID ASC";
				
				$locID_02erg = MYSQL_QUERY($locID_02query);
				
				$locID_02numrows = MYSQL_NUM_ROWS($locID_02erg);
				
				if ($locID_02numrows > '0') {
					
					$locID_02 = 0;
					
					while ($locID_02 < $locID_02numrows) {
						
						$locID_available = mysql_result($locID_02erg,$locID_02,"locID");
						
						echo "<h2>$locID_02: $locID_available</h2><br>\n";
						
						$locID_02++;}}}

?>
works also as expected. But the third step

Code: Select all

<?php

else if ($location_01numrows > '1') {
							
							$location_01 = 0;
							
							while ($location_01 < $location_01numrows) {
								
								$location_blocked = mysql_result($location_01erg,$location_01,"locID");
								
								$location_02query = "SELECT eventloc.locID FROM eventloc WHERE eventloc.locID != '$location_blocked' ORDER BY eventloc.locID ASC";
								
								$location_02erg = MYSQL_QUERY($location_02query);
								
								$location_02numrows = MYSQL_NUM_ROWS($location_02erg);
								
								if ($location_02numrows > '0') {
									
									$location_02 = 0;
									
									while ($location_02 < $location_02numrows) {
										
										$location_available = mysql_result($location_02erg,$location_02,"locID");
										
										echo "<h2>$location_02: $location_available</h2><br>\n";
										
										$location_02++;}}
										
										$location_01++;}}

?>
produces three lists (for testing purpose, i have entered three events appearing at the same both date and time in different locations), each with one room less.

- bluenote

Posted: Fri Aug 06, 2004 5:57 am
by timvw
You could also have a look in the mysql manual, and found how to comparison operators work.... Pretty sure it will tell you the syntax for not equal.

Posted: Fri Aug 06, 2004 6:42 am
by bluenote
You could also have a look in the mysql manual, and found how to comparison operators work.... Pretty sure it will tell you the syntax for not equal.
Yes it does, but that isn't my problem. My problem is that i propably have to compare more than one ID within one (the second) query; each ID to be compared as not equal is one result row of the first query.

Perhaps there is a quite simple solution and i just don't see it.

- bluenote

Posted: Sat Aug 07, 2004 6:09 am
by bluenote
Tonight i experimented with another way to do the job:

Code: Select all

<?php

function mysql_fetch_all_rooms($erg_01, $erg_type_01 = MYSQL_ASSOC) {
	
	$first = array();
	
	for($i=0;$i<@mysql_num_rows($erg_01);$i++) {
		
		array_push($first,@mysql_fetch_array($erg_01,$erg_type_01));}
		
		return $first;}
		
		$query_01 = "SELECT eventloc.locID AS location FROM eventloc ORDER BY eventloc.locID ASC";
		
		$erg_01 = mysql_query($query_01);
		
		$subrows_01 = mysql_num_rows($erg_01);
		
		$rows_01 = $subrows_01 - 1;
		
		$first = mysql_fetch_all_rooms($erg_01,MYSQL_ASSOC);
		
			function mysql_fetch_blocked_rooms($erg_02, $erg_type_02 = MYSQL_ASSOC) {
				
				$second = array();
				
				for($s=0;$s<@mysql_num_rows($erg_02);$s++) {
					
					array_push($second,@mysql_fetch_array($erg_02,$erg_type_02));}
					
					return $second;}
					
					$query_02 = "SELECT events.locID AS location FROM events WHERE ((events.locID != '') AND (events.locID != '0')) AND events.year = '$event_year' AND events.month = '$event_month' AND events.day = '$event_day' AND events.time >= '$event_start_time' AND events.time <= '$event_end_time' GROUP BY events.locID ASC";
					
					$erg_02 = mysql_query($query_02);
					
					$subrows_02 = mysql_num_rows($erg_02);
					
					$rows_02 = $subrows_02 - 1;
					
					$second = mysql_fetch_blocked_rooms($erg_02,MYSQL_ASSOC);
					
					$third = array_diff($first[$rows_01], $second[$rows_02]);

?>
This produces the follwing results:

Code: Select all

Array 1
(
    &#1111;0] =&gt; Array
        (
            &#1111;location] =&gt; Room 1
        )

    &#1111;1] =&gt; Array
        (
            &#1111;location] =&gt; Room 2
        )

    &#1111;2] =&gt; Array
        (
            &#1111;location] =&gt; Room 3
        )

    &#1111;3] =&gt; Array
        (
            &#1111;location] =&gt; Room 4
        )

    &#1111;4] =&gt; Array
        (
            &#1111;location] =&gt; Room 5
        )

    
)
Array 2
(
    &#1111;0] =&gt; Array
        (
            &#1111;location] =&gt; Room 1
        )

    &#1111;1] =&gt; Array
        (
            &#1111;location] =&gt; Room 2
        )

    &#1111;2] =&gt; Array
        (
            &#1111;location] =&gt; Room 3
			)

)

Array 3
(
    &#1111;0] =&gt; Array
        (
            &#1111;location] =&gt; Room 2
        )

    &#1111;1] =&gt; Array
        (
            &#1111;location] =&gt; Room 3
        )

    &#1111;2] =&gt; Array
        (
            &#1111;location] =&gt; Room 4
        )

    &#1111;3] =&gt; Array
        (
            &#1111;location] =&gt; Room 5
        )

)
Array one keeps all rooms, Array 2 keeps the rooms already in use and Array 3 *SHOULD* keep the rooms available (array 1 - array 2). It should, but only the first room was removed from the list ....