Page 1 of 1
2 into 1
Posted: Thu Aug 26, 2004 3:50 am
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());
?>
Posted: Thu Aug 26, 2004 7:05 am
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';
Posted: Thu Aug 26, 2004 11:10 pm
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>
?>
Posted: Thu Aug 26, 2004 11:17 pm
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')
Posted: Fri Aug 27, 2004 1:07 am
by Lord Sauron
Wouldn't this one work?
$sql = "SELECT * FROM employee e, leave l WHERE e.username ='$user'" AND l.Dept = '$Dept'";
Posted: Fri Aug 27, 2004 2:29 am
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());
Posted: Fri Aug 27, 2004 3:19 am
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.
Posted: Fri Aug 27, 2004 3:39 am
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?
Posted: Fri Aug 27, 2004 3:43 am
by Lord Sauron
What is the table 'leave' exactly used for?
Posted: Fri Aug 27, 2004 3:49 am
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.
Posted: Fri Aug 27, 2004 4:14 am
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
Posted: Fri Aug 27, 2004 4:42 am
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());
?>
Posted: Fri Aug 27, 2004 5:02 am
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;";