2 into 1

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

2 into 1

Post by g3ckO »

How can I write this two query into just one query (combo). So I can use the data from the two table.

Code: Select all

<?php

$user=addslashes($_SESSION[username]);

function extract_user() 
{ 
   $user=addslashes($_SESSION[username]); 
   $q="SELECT * FROM employee WHERE username ='$user'"; 
   $r=mysql_query($q); 
   $row_array=mysql_fetch_array($r); 
   return $row_array; 
}
   $row_array=extract_user(); 
   $Dept=$row_array['EmpDept'];

   $sql="SELECT * FROM leave WHERE Dept = '$Dept'";
   $rs=mysql_query($sql) or die(mysql_error());
?>
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

You should provide more information about the table fields, but I think that following query should work:

SELECT * FROM employee AS a INNER JOIN leave AS b ON a.EmpDept=b.Dept WHERE username='$user';
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

That anything wrong here. Coz I dont get the output. Only the button appear.

Code: Select all

<?php

session_start(); 
include("database.php");


   $user=addslashes($_SESSION[username]);

function extract_user() 
{ 
   $user=addslashes($_SESSION[username]); 
   $q="SELECT * FROM employee WHERE username ='$user'"; 
   $r=mysql_query($q); 
   $row_array=mysql_fetch_array($r); 
   return $row_array; 
}
   $row_array=extract_user(); 
   $Dept=$row_array['EmpDept'];
   $access =$row_array["AccType"]; 
   $ack_by =$row_array["EmpName"];


   $sql="SELECT * FROM leave WHERE Dept = '$Dept' AND Status = 'Pending Review' OR Status = 'Acknowledge'";
   $rs=mysql_query($sql) or die(mysql_error());

   while ($row = mysql_fetch_array($rs))

	{


   	$refid=$row["RefID"];
   	$nama = $row["Nama"];
   	$jawatan = $row["Jawatan"];
  	$jenis = $row["Jenis"];
  	$mula = $row["DateMula"];
   	$tamat = $row["DateTamat"];
   	$mohon = $row["DateMohon"];
   	$refid = $row["RefID"];
   	$status = $row["Status"];      
	

        echo"<form action=leave_app_2.php?$approved method=POST>";
	
?>
	
	<div align="left">
   	<table border="0" cellpadding="2" cellspacing="1" width="100%">
    	<tr>
        <td align="left" valign="bottom" width="2%"
            bgcolor="lightskyblue" valign="TOP" marginwidth="12" marginheight="12">
          
            <?echo"<input type=checkbox name=approved[] value=$refid>";?>
        </td>
        <td align="left" valign="bottom" width="25%"
            bgcolor="lightskyblue" valign="TOP" marginwidth="12" marginheight="12">
          
            <?echo"$nama";?>
        </td>
   	<td align="left" valign="top" width="18%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$refid";?>
        </td>
   	<td align="left" valign="top" width="17%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$mohon";?>
        </td>
   	<td align="left" valign="top" width="20%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$mula";?> hingga <?echo"$tamat"?>
        </td>
   	<td align="left" valign="top" width="18%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$status";?>
        </td>
    	</tr>
   	</table>
   	</div>
<?
	}
?>

	<input type="hidden" name="statusby" value="<? echo $ack_by; ?>" >

	<br>
	<div align="left">
   	<table width="100%">
    	<tr>
        <td align="right" width="50%">
                    
   	<input type="submit" value="APPROVED" name="app">            
        </td>
        <td align="left" width="50%">
             
   	<input type="submit" value="NOT APPROVED" name="notapp">
        </td>
    	</tr>
   	</table>
   	</div>
	</form>

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you select probably isn't returning rows.. your select currently has Dept and the first Status grouped.. you probably want the status' grouped..

Code: Select all

SELECT * FROM leave WHERE Dept = '$Dept' AND (Status = 'Pending Review' OR Status = 'Acknowledge')
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

Wouldn't this one work?

$sql = "SELECT * FROM employee e, leave l WHERE e.username ='$user'" AND l.Dept = '$Dept'";
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

Thats doesn't work.

I have 2 table:
  • employee
    username
    AccType
    EmpName
    EmpDept

    leave
    RefID
    Dept
    Status
The first query is to get the data from table employee where username = session_username and get the var $EmpDept

The second query is to get the data from table leave where the Dept = $EmpDept and Status = 'Pending' OR 'Acknowledge'

And I also need to use the data from both table.

So how to write the query?

Can you guys understand what I wrote here? :?

I try like this but its not working:

Code: Select all

<?php

session_start(); 
include("database.php");

   $user=addslashes($_SESSION[username]);

   $query="SELECT * FROM `leave` a INNER JOIN `employee` b ON b.`EmpDept` = a.`Dept` WHERE b.`username` = '$user' AND a.`Status`='Pending Review' OR a.`Status` =  'Acknowledged'";

   $result=mysql_query($query) or die(mysql_error());
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

You could try this one:

$sql = "SELECT e.empDept FROM employee e, leave l WHERE e.username ='$user'" AND (l.Dept = '$Dept' AND (l.status='Pending' OR l.status='Acknowledge'))";


But I don't think this will work, because there is no foreign key between the mentioned tables.
Last edited by Lord Sauron on Fri Aug 27, 2004 3:44 am, edited 1 time in total.
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

If I insert one more field in both table, example StaffID and make it as primary key for table employee and as foreign key in table leave, would it help? And how will the query will look like?
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

What is the table 'leave' exactly used for?
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

table leave is use to store data when the staff request for leave. It will store data like date request, the status of the request, who is requesting, who is approving it, the date leave start and end, and some info about the staff.
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

I guess I would use three tables

Staff
- primary key StaffID
- ....
- ....
Employee
- primary key EmployeeID
- ....
- ....
- foreign key StaffID
Agenda (leave is no entity, someones agenda is)
- primary key AgendaID
- ....
- ....
- foreign key EmployeeID
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

I need to change many things if I change the table. So please look at this again.

This work fine:

Code: Select all

<?php
<?

session_start(); 
include("database.php");


   $user=addslashes($_SESSION[username]);
 
   $query="SELECT * FROM `leave` a INNER JOIN `employee` b ON b.`StaffNo` = a.`Super` WHERE b.`username` = '$user'AND a.`Status` = 

'Pending Review' OR `Status` =  'Acknowledged'";
 
   $result=mysql_query($query) or die(mysql_error());

?>	   
	<div align="left">
   	<table border="0" cellpadding="2" cellspacing="1" width="100%">
    	<tr>
        <td align="left" valign="bottom" width="2%"
            bgcolor="lightskyblue" valign="TOP" marginwidth="12" marginheight="12">
          
        </td>
        <td align="left" valign="bottom" width="25%"
            bgcolor="lightskyblue" valign="TOP" marginwidth="12" marginheight="12">
          
            <b>NAMA</b>
        </td>
   	<td align="left" valign="top" width="18%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <b>REF. ID</b>
        </td>
   	<td align="left" valign="top" width="17%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <b>TARIKH PERMOHONAN</b>
        </td>
   	<td align="left" valign="top" width="20%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <b>TARIKH CUTI</b>
        </td>
   	<td align="left" valign="top" width="18%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <b>STATUS</b>
        </td>
    	</tr>
   	</table>
   	</div>

<?

   while ($row = mysql_fetch_array($result))

	{


   	$refid=$row["RefID"];
   	$nama = $row["Nama"];
   	$jawatan = $row["Jawatan"];
  	$jenis = $row["Jenis"];
  	$mula = $row["DateMula"];
   	$tamat = $row["DateTamat"];
   	$mohon = $row["DateMohon"];
   	$refid = $row["RefID"];
   	$status = $row["Status"];      
	
  	$access = $row["AccType"]; 
   	$ack_by = $row["EmpName"];

        echo"<form action=leave_app_2.php?$approved method=POST>";
	
?>
	
	<div align="left">
   	<table border="0" cellpadding="2" cellspacing="1" width="100%">
    	<tr>
        <td align="left" valign="bottom" width="2%"
            bgcolor="lightskyblue" valign="TOP" marginwidth="12" marginheight="12">
          
            <?echo"<input type=checkbox name=approved[] value=$refid>";?>
        </td>
        <td align="left" valign="bottom" width="25%"
            bgcolor="lightskyblue" valign="TOP" marginwidth="12" marginheight="12">
          
            <?echo"$nama";?>
        </td>
   	<td align="left" valign="top" width="18%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$refid";?>
        </td>
   	<td align="left" valign="top" width="17%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$mohon";?>
        </td>
   	<td align="left" valign="top" width="20%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$mula";?> hingga <?echo"$tamat"?>
        </td>
   	<td align="left" valign="top" width="18%"
            bgcolor="lightskyblue" marginwidth="12" marginheight="12">
	    
            <?echo"$status";?>
        </td>
    	</tr>
   	</table>
   	</div>
<?
	}
?>

	<input type="hidden" name="statusby" value="<? echo $ack_by; ?>" >

	<br>
	<div align="left">
   	<table width="100%">
    	<tr>
        <td align="right" width="50%">
                    
   	<input type="submit" value="APPROVED" name="app">            
        </td>
        <td align="left" width="50%">
             
   	<input type="submit" value="NOT APPROVED" name="notapp">
        </td>
    	</tr>
   	</table>
   	</div>
	</form>
?>
But when I change the query like below, it doesnt work:

Code: Select all

<?php
$query="SELECT * FROM `leave` a INNER JOIN `employee` b ON b.`EmpDept` = a.`Dept` WHERE b.`username` = '$user' AND a.`Status`='Pending Review' OR a.`Status` =  'Acknowledged'";

?>
What I want is the query something like this but I want it to be only in one query.

Code: Select all

<?php
function extract_user() 
{ 
   $user=addslashes($_SESSION[username]); 
   $q="SELECT * FROM employee WHERE username ='$user'"; 
   $r=mysql_query($q); 
   $row_array=mysql_fetch_array($r); 
   return $row_array; 
}
   $row_array=extract_user(); 
   $Dept=$row_array['EmpDept'];

  $sql="SELECT * FROM leave WHERE Dept = '$Dept'";
  $rs=mysql_query($sql) or die(mysql_error());

?>
User avatar
Lord Sauron
Forum Commoner
Posts: 85
Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL

Post by Lord Sauron »

Okay, the easiest way (short term only) will be to add employeeID as foreign key to the table leave and as primary key to the table employee. The following query should work:

$sql = "SELECT e.empDept FROM employee e, leave l WHERE e.username ='$user'" AND (l.Dept = '$Dept' AND (l.status='Pending' OR l.status='Acknowledge')) AND e.employeeID=l.employeeID;";
Post Reply