Jump to content

msql query


amylou

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

Link to comment
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>

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.