amylou Posted August 13, 2007 Share Posted August 13, 2007 i have 2 tables in my database that i want to query so that a person can choose a date and it will show from both tables who has that date off. the tables are" timoff and verifieroff both have the same table structure of the following requestId(auto incrimented)PK first last shift timeOff sat ver todaysDate requestedDate requestedTime numberRequestedHours reascheduleDate extraDays rescheduleTime numberRescheduleHours reason i have tried a couple of different ways but to no avail any help would be great thanks amy Quote Link to comment https://forums.phpfreaks.com/topic/64651-msql-query/ Share on other sites More sharing options...
pikemsu28 Posted August 13, 2007 Share Posted August 13, 2007 try UNION $query = "(Select * from timoff WHERE todaysDate = '$date') UNION (Select * from verifieroff WHERE todaysDate = '$date')"; Quote Link to comment https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-322430 Share on other sites More sharing options...
amylou Posted August 13, 2007 Author Share Posted August 13, 2007 thank you that seems to work. But i have found that it only works part of the time any ideas?? Quote Link to comment https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-322704 Share on other sites More sharing options...
pikemsu28 Posted August 13, 2007 Share Posted August 13, 2007 give an example of when it works and when it doesnt (show the queries for each) Quote Link to comment https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-322725 Share on other sites More sharing options...
amylou Posted August 14, 2007 Author Share Posted August 14, 2007 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <?php /*this program was designed to eliminate paper work for the sift supervisors and the csps that work at Sitel here at loring. this program was written and developed by Amy Coppola on July 2007 */ ?> <?PHP include'mysqlconnectsitel.inc.php';?> <title>datesoff</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link href="links.css" rel="stylesheet" type="text/css"/> <link href="general.css" rel="stylesheet" type="text/css"/> </head> <body> <h1> <img src="33720001.jpg" alt="sitel" width="300" height="100" /> <?PHP extract ($_POST); if(isset($_POST['action'])) { $actions= $_POST['action']; } else { $actions=""; } if ( $actions == "" ) { ?> <form name='datesoff' method='post' action='datesoff.php'> <?PHP $sqlquery = "(SELECT * FROM timeoff) UNION (SELECT * FROM verifiertimeoff )ORDER BY 'requestedDate' ASC "; //$sqlquery = "SELECT * FROM verifiertimeoff "; $queryresult= mysql_query($sqlquery) or die("No Dates found. To return to previous page <a href=datesoff.php> Click here.</a>"); $rowcount = mysql_num_rows($queryresult); if ($rowcount== 0) { $actions=""; die("No Dates found. To return to previous page <a href=datesoff.php> Click here.</a> "); } ?> <select name="requestedDate"> <?PHP for( $i = 0; $i < $rowcount; $i++ ) { $info = (mysql_fetch_row($queryresult)); $requestedDate = "$info[8]"; echo"<option value =\"$info[8]\">$requestedDate</option>"; } ?> <input type= "hidden" name= "action" value= "requestedDate"> <input type = "submit" name = "select_date" value= "Select Date"> <?PHP ?> </select> </form> <?PHP } else if( $actions == "requestedDate" ) { ?> <form name='datesoff' method='post' action='datesoff.php'> <?PHP $myrequestedDate = !isset($_POST['requestedDate'])? NULL : $_POST['requestedDate']; $sqlquery = "(SELECT * FROM timeoff WHERE requestedDate = '$myrequestedDate') UNION (SELECT * FROM verifiertimeoff WHERE requestedDate = '$myrequestedDate')"; //$sqlquery = "SELECT * FROM verifiertimeoff WHERE requestedDate = '$myrequestedDate'"; $queryresult= mysql_query($sqlquery) or die("No shifts found. To return to dates off <a href='datesoff.php'> Click here</a> ."); $rowcount = mysql_num_rows($queryresult); if ($rowcount== 0) {die("No dates found. To return to dates off <a href='datesoff.php'> Click here</a> ."); } $info = (mysql_fetch_row($queryresult)); $requestedDate = "$info[8]"; echo "<p>$requestedDate</p>"; echo "<input type='hidden' name='requestedDate' value='$requestedDate'>"; $sqlquery = "(SELECT * FROM timeoff WHERE requestedDate = '$requestedDate') UNION (SELECT * FROM verifiertimeoff WHERE requestedDate = '$requestedDate') "; //$sqlquery = "SELECT * FROM verifiertimeoff WHERE requestedDate = '$myrequestedDate'"; $queryresult= mysql_query($sqlquery) or die("No dates found. To return to dates off<a href='datesoff.php'> Click here</a> ."); $rowcount = mysql_num_rows($queryresult); if ($rowcount== 0) {die("No dates found. To return to dates off<a href='datesoff.php'>Click here</a> ."); } echo "<p>To select a different date <a href='datesoff.php'>click here</a>.</p>"; ?> <select name="shift"> <?PHP for( $i = 0; $i < $rowcount; $i++ ) { $info = (mysql_fetch_row($queryresult)); echo"<option value =\"$info[3]\">$info[3]</option>"; } ?> </select> <input type = "hidden" name= "action" value= "shift"> <input type = "submit" name= "select_shift" value= "Select Shift"> </form> <?PHP } else if( $actions == "shift" ) { ?> <?PHP $myShift = !isset($_POST['shift'])? NULL : $_POST['shift']; $myrequestedDate = !isset($_POST['requestedDate'])? NULL : $_POST['requestedDate']; echo "<p>$myrequestedDate</p>"; $sqlquery = "(SELECT * FROM timeoff WHERE requestedDate = '$myrequestedDate' AND shift = '$myShift') UNION (SELECT * FROM verifiertimeoff WHERE requestedDate = '$myrequestedDate' AND shift = '$myShift')"; //$sqlquery = "SELECT * FROM verifiertimeoff WHERE requestedDate = '$myrequestedDate' AND shift = '$myShift'"; $queryresult= mysql_query($sqlquery) or die("No shifts found. To return to dates off <a href='datesoff.php'> Click here</a> ."); $rowcount = mysql_num_rows($queryresult); if ($rowcount== 0) { die("No dates found. To return to dates off <a href='datesoff.php'> Click here</a> ."); } echo "<p>$myShift</p>"; ?> <table border='1'> <tr> <th></th> <th>Request ID</th> <th>First Name</th> <th>Last Name</th> <th>Shift</th> <th>Saturday</th> <th>Verifier</th> <th>Paid/Unpaid</th> <th>Todays Date</th> <th>Date Requested Off</th> <th>Time Requested Off</th> <th>Number of Hours</th> <th>Date Rescheduled</th> <th>Time Rescheduled</th> <th>Rescheduled Hours</th> <th>Reason</th> </tr> <?PHP for( $i = 1; $i <= $rowcount; $i++ ) { $info = (mysql_fetch_row($queryresult)); echo"<tr><td>$i</td><td>$info[0]</td><td>$info[1]</td><td>$info[2]</td><td>$info[3]</td><td>$info[4]</td><td>$info[5]</td><td>$info[6]</td><td>$info[7]</td><td>$info[8]</td><td>$info[9]</td><td>$info[10]</td><td>$info[11]</td><td>$info[12]</td><td>$info[13]</td><td>$info[14]</td> </tr>"; } ?> </table> <?PHP echo "<p>To select a different date <a href='datesoff.php'>click here</a>.</p>"; } ?> </head> </body> </html> [/code> Quote Link to comment https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-323091 Share on other sites More sharing options...
amylou Posted August 14, 2007 Author Share Posted August 14, 2007 the code that was submitted is the code used for the query. i don't know why it would work once and then not again. i did it where in the timeoff table requestedDate was dec 31,2007 and the verifiertimeoff was the same. but after that it did not work. any ideas. i could do two different ones i guess but i would like to use one Quote Link to comment https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-323094 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.