double query

[ec]

Anyone got any idea what is wrong with this?  I am trying to search for a list of detention dates which the pupil are in and compare them to the dates which are available generating a list of dates where the pupil is available.  Any ideas?

 

 

<?php

$query1 = mysql_query("SELECT detentiondate FROM detention WHERE '".$_SESSION['pupilno']."' = pupilno ");
$row1 = mysql_fetch_assoc($query1);


$query2 = mysql_query("SELECT detentiondate FROM detentiondates WHERE detentiondate != $row1.detentiondate ");

echo '<form>';

while ($row = mysql_fetch_assoc($query2)){
   print "$row[detentiondate]";
   echo "<input type='radio' name='choice' value='{$row['detentiondate']}'><br>";

?>

[craygo]

this line

$query2 = mysql_query("SELECT detentiondate FROM detentiondates WHERE detentiondate != $row1.detentiondate ");

 

should be

 

$query2 = mysql_query("SELECT detentiondate FROM detentiondates WHERE detentiondate != '".$row1['detentiondate']."'");

 

Ray

[soycharliente]

<?php
$query1 = mysql_query("SELECT detentiondate FROM detention WHERE '".$_SESSION['pupilno']."' = pupilno ");
$row1 = mysql_fetch_assoc($query1);

$query2 = mysql_query("SELECT detentiondate FROM detentiondates WHERE detentiondate != $row1.detentiondate ");
?>

 

Is it possible to combine those queries?

<?php
$sql = "SELECT detentiondate FROM detentiondates ds
JOIN detention d ON ds.detentiondate!=d.detentiondate
WHERE pupilno='{$_SESSION['pupilno']}'";
?>

Mostly just curious. I'm always trying to better understand JOIN and write less queries.

[craygo]

Do you have a field which links the 2 tables??

[soycharliente]

He was searching for detentiondate in both tables. I only assumed it was the same date.

[craygo]

theoretically your query above should work. Probably not the best practice to link tables on a date field but if it works... 

 

Ray

[ec]

hi guys

 

sorry for taking so long....

craygo: the line you gave me does work but there is still a problem;  there is more then one value for the answer of the first search but at the minute it only elmintates 1 of the detentiondates.

 

 

[sasa]

SELECT detentiondate FROM detentiondates WHERE NOT (detentiondate in(SELECT detentiondate FROM detention WHERE '".$_SESSION['pupilno']."' = pupilno))

 

(i not try this)

[ec]

no...sorry

 

still not working

[craygo]

SELECT detentiondate FROM detentiondates WHERE detentiondate NOT IN (SELECT detentiondate FROM detention WHERE '".$_SESSION['pupilno']."' = pupilno)

[ec]

Thanks that bit of it is working now.