Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question query from two tables

    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

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    A UNION should work, post your code that you tried using a union.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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

  • #4
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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:
    Code:
    <!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

  • #5
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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 for more information on Database Normalization.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #6
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i thought of that but i could not figure out a way for the following code to work if i did that:
    Code:
    //	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

  • #7
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    You would need to run 3 queries rather than 2 tables.

    PHP Code:
    $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:

    PHP Code:
    $rowcountTOT mysql_num_rows($queryresult);
    $rowcountVER mysql_num_rows($queryVER2);
    $rowcountUNV mysql_num_rows($queryUNV3);

    and 

    if( 
    $rowcountTOT 
    You would need to simply look at the other variables, rather than multiple queries to the database.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #8
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i will try that and let you know how i make out thank you for your help

  • #9
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

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

  • #10
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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...

    PHP Code:
    $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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #11
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  • #12
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #13
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    also i checked by database and even the people that checked verifier as yes are in as 0

  • #14
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    its not stoping at >5 either

  • #15
    New Coder
    Join Date
    Aug 2007
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    anybody have any ideas on how i can get this to work. thanks


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •