...

View Full Version : query from two tables



amylou111770
09-03-2007, 07:01 PM
hey all,

i have two tables

table one
requestId(primarykey auto incremented)
timeOff
first
last
shift
saturday
verifier
todaysDate
requestedDate
NumberHoursRequested
totalHours
rescheduleDate
numberRescheduleDate

there are more fields but this gives an idea

the second table has the exact same fields. what i would like to do is have a query that will display in a table the requestDate, first, last, ect... from both tables. right now i can get it to do that with only one table. I have tried joins and unions and nothing seems to work. i want to be able to select a date from a dropdown box and after selecting that date it shows everything for that date.

I can post what i have for the one table if anyone wants

thanks for the help

amy

CFMaBiSmAd
09-03-2007, 07:08 PM
A UNION should work, post your code that you tried using a union.

guelphdad
09-03-2007, 07:16 PM
the second table has the exact same fields. it sounds like then that you should have a single table in the first place.

your table also doesn't look normalized, you should do a search on database normalization

amylou111770
09-04-2007, 01:16 AM
the reason for the different tables is that i can only have 6 people per shift off per day abd two of then can be verifiers the rest are not (4 +2 per shift)

and the only way that i could find to do that was to have 2 tables.

right now this is the code that works for the one 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" />
</h1>
<p id="nav"><a href="TimeOff.php" title= "TimeOff">to exit program</a>&nbsp;&nbsp;</p>
<?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 ";

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

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

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


it brings a select box at each step. if there is an easier way i am all for it

Daemonspyre
09-04-2007, 02:20 PM
Why not just add a field to your first table, called `verify` TINYINT UNSIGNED DEFAULT 0.

That way, you can look at the database and 'check' to see who is allowed to verify, and you don't have to have 2 tables.

Geulph is right -- your db is not normalized. Look here (http://en.wikipedia.org/wiki/Database_normalization) for more information on Database Normalization.

amylou111770
09-04-2007, 02:29 PM
i thought of that but i could not figure out a way for the following code to work if i did that:


// Need to query database to see if this request is valid

$query = "SELECT * FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate'";

$queryresult = mysql_query($query) or die("Query failed : " . mysql_error());
$rowcount = mysql_num_rows($queryresult);
if( $rowcount > 5 )




if the day is full it gives an error message and if its empty it will insert into database

How do i get it to stop at 4 for csp and at 2 for verifiers in one table

Daemonspyre
09-04-2007, 03:09 PM
You would need to run 3 queries rather than 2 tables.



$query = "SELECT * FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate'";

and

$queryVER = "SELECT COUNT(RequestID) 'countRequest' FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate' AND verify=1 GROUP BY RequestID";

and

$queryUNV = "SELECT COUNT(RequestID) 'countRequest' FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate' AND verify=0 GROUP BY RequestID";

Then change:



$rowcountTOT = mysql_num_rows($queryresult);
$rowcountVER = mysql_num_rows($queryVER2);
$rowcountUNV = mysql_num_rows($queryUNV3);

and

if( $rowcountTOT > 5 )


You would need to simply look at the other variables, rather than multiple queries to the database.

amylou111770
09-04-2007, 04:30 PM
i will try that and let you know how i make out thank you for your help

amylou111770
09-04-2007, 05:23 PM
Parse error: syntax error, unexpected T_LOGICAL_AND in C:\apache2triad\htdocs\RequestTimeOff2.php on line 58


that is the error i got when running the code with the code that you suggested below is the code above and below the error




$query = "SELECT * FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate'";

and

$queryVER = "SELECT COUNT(RequestID) 'countRequest' FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate' AND verify=1 GROUP BY RequestID";



line 58 is 'and'

any suggestions

Daemonspyre
09-04-2007, 05:54 PM
Oh... hehe...

The 'and's that I put in were not for you to use, but were for you to see the "and" characteristics of correct grammar...



$query = "SELECT * FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate'";
$queryVER = "SELECT COUNT(RequestID) 'countRequest' FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate' AND verify=1 GROUP BY RequestID";
$queryUNV = "SELECT COUNT(RequestID) 'countRequest' FROM timeoff WHERE shift = '$shift' AND requestedDate = '$requestedDate' AND verify=0 GROUP BY RequestID";


Just remove the 'and's in the code and you should be up and running.

amylou111770
09-04-2007, 06:19 PM
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2triad\htdocs\RequestTimeOff2.php on line 70 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2triad\htdocs\RequestTimeOff2.php on line 71


it ran and it gave me this warning but it worked i think

amylou111770
09-04-2007, 06:22 PM
also the way in know that the person filling out the form is a verifier they are to check a check box. but it will still let me keep checking past the two i need for verifier.

amylou111770
09-04-2007, 06:30 PM
also i checked by database and even the people that checked verifier as yes are in as 0

amylou111770
09-04-2007, 06:33 PM
its not stoping at >5 either

amylou111770
09-07-2007, 01:33 AM
anybody have any ideas on how i can get this to work. thanks

Daemonspyre
09-07-2007, 02:13 PM
Sorry - didn't realize Guelph moved this to the PHP forum.

You need to change the Verifiers in the database to be '1', not '0'.

Think of it as binary YES and NO, with YES == 1 and NO == 0.

Do a check:

if($verifier == 1) { //do let them verify }
elseif( $verifier == 0) { //don't let them verify }

You are getting the mysql_num_rows issue because I only gave you the roadmap, but it was up to you to do the work.



$rowcountTOT = mysql_num_rows($queryresult);
$rowcountVER = mysql_num_rows($queryVER2);
$rowcountUNV = mysql_num_rows($queryUNV3);


The above code SHOULD FAIL, because mysql_num_rows should be based off a resultset, not a query. Look at your code for examples, as you are on the right track.

Lastly, it's not stopping at 5 because a) you need to fix the mysql_num_rows issue first; and b) you haven't set your verifier records in the database yet (see first section).

HTH!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum