ec Posted April 15, 2008 Share Posted April 15, 2008 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>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/ Share on other sites More sharing options...
craygo Posted April 15, 2008 Share Posted April 15, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517600 Share on other sites More sharing options...
soycharliente Posted April 15, 2008 Share Posted April 15, 2008 <?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. Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517621 Share on other sites More sharing options...
craygo Posted April 15, 2008 Share Posted April 15, 2008 Do you have a field which links the 2 tables?? Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517629 Share on other sites More sharing options...
soycharliente Posted April 15, 2008 Share Posted April 15, 2008 He was searching for detentiondate in both tables. I only assumed it was the same date. Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517636 Share on other sites More sharing options...
craygo Posted April 15, 2008 Share Posted April 15, 2008 theoretically your query above should work. Probably not the best practice to link tables on a date field but if it works... Ray Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517640 Share on other sites More sharing options...
ec Posted April 15, 2008 Author Share Posted April 15, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517786 Share on other sites More sharing options...
sasa Posted April 15, 2008 Share Posted April 15, 2008 SELECT detentiondate FROM detentiondates WHERE NOT (detentiondate in(SELECT detentiondate FROM detention WHERE '".$_SESSION['pupilno']."' = pupilno)) (i not try this) Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517796 Share on other sites More sharing options...
ec Posted April 15, 2008 Author Share Posted April 15, 2008 no...sorry still not working Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517816 Share on other sites More sharing options...
craygo Posted April 15, 2008 Share Posted April 15, 2008 SELECT detentiondate FROM detentiondates WHERE detentiondate NOT IN (SELECT detentiondate FROM detention WHERE '".$_SESSION['pupilno']."' = pupilno) Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517825 Share on other sites More sharing options...
ec Posted April 15, 2008 Author Share Posted April 15, 2008 Thanks that bit of it is working now. Quote Link to comment https://forums.phpfreaks.com/topic/101183-double-query/#findComment-517835 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.