Jump to content

msql query

amylou

By amylou
in Microsoft SQL - MSSQL

Recommended Posts

amylou Member

amylou

Posted
Posted

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
https://forums.phpfreaks.com/topic/64651-msql-query/
Share on other sites
amylou Member

amylou

Posted
  • Author
Posted

 

 

 

 

 



<!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
https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-323091
Share on other sites
amylou Member

amylou

Posted
  • Author
Posted

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
https://forums.phpfreaks.com/topic/64651-msql-query/#findComment-323094
Share on other sites

Archived

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

Go to topic listing
×
×
  • 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.