Jump to content

Archived

This topic is now archived and is closed to further replies.

amylou

msql query

Recommended Posts

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

Share this post


Link to post
Share on other sites

try UNION

 

$query = "(Select * from timoff WHERE todaysDate = '$date') UNION (Select * from verifieroff WHERE todaysDate = '$date')";

Share this post


Link to post
Share on other sites

thank you that seems to work. But i have found that it only works part of the time any ideas??

Share this post


Link to post
Share on other sites

 

 

 

 

 



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.