Jump to content

running a query or queries from 2 different tables

amylou

By amylou
in MySQL Help

 Share

Recommended Posts

amylou Member

amylou

Posted
Posted

i have tried using union and joins and nothing seems to work,

 

i have 2 tables timeoff and verifiertimeoff both have the same exact field name. i have tried changing the verifiertimeoff fieldnames so that they have a v in front and that did not help. I have 2 php files that do the exact same thing for each of the tables is there a way to make it only one php page.

 

 

the first code is datesoff.php and this works the timeoff


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

<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 ORDER BY 'requestedDate' ASC ";

	$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'";
	$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' ";
	//$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'";
	//$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>";	


	echo"<p>To go back to the members page <a href='members.php'>click here</a>.</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>";
}
?>
<?php 
echo"<p>To go back to the members page <a href='members.php'>click here</a>.</p>";

?>
</head>
</body>
</html>

 

this second code datesoffverifer.php uses the verfiertimeoff table.

<!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='datesoffverifier.php'>

<?PHP
	$sqlquery = "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=datesoffverifier.php> Click here.</a>");

	$rowcount = mysql_num_rows($queryresult);
	if ($rowcount== 0) 
	{
		$actions="";
		die("No Dates found. To return to previous page <a href=datesoffverifier.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='datesoffverifier.php'>
<?PHP
	$myrequestedDate = !isset($_POST['requestedDate'])? NULL : $_POST['requestedDate'];

       $sqlquery = "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='datesoffverifier.php'> Click here</a> .");
	$rowcount = mysql_num_rows($queryresult);
	if ($rowcount== 0) {die("No dates found. To return to dates off <a href='datesoffverifier.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 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='datesoffverifier.php'> Click here</a> .");
	$rowcount = mysql_num_rows($queryresult);
	if ($rowcount== 0) {die("No dates found. To return to dates off<a href='datesoffverifier.php'>Click here</a> .");
	}
	echo "<p>To select a different date <a href='datesoffverifier.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 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='datesoffverifier.php'> Click here</a> .");
	$rowcount = mysql_num_rows($queryresult);
	if ($rowcount== 0)
	 {
	 die("No dates found. To return to dates off <a href='datesoffverifier.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='datesoffverifier.php'>click here</a>.</p>";
}
?>
</head>
</body>
</html>

 

any suggestions or comments would be great. thanks in advance for all your help

Link to comment
Share on other sites
radalin Member

radalin

Posted
Posted

something like:

SELECT v.*,t.* FROM verifiertimeoff v, timeoff t

may work. But it will create too much result rows, as this is a huge query....

 

Anyway you could try something like:

<?php
$my_table_name = ( $_GET["t"] ? "timeoff" : "verifiertimeoff" );

$query = "SELECT * FROM $my_table_name";
//Here comes additional code
?>

which is a bit better. But for the first thing you could try putting a where clause like:

SELECT v.*,t.* FROM verifiertimeoff v, timeoff t WHERE t.id = v.id;

It will reduce the result row count, however I do not know if it's usefull or not...

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.

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