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
g3ckO
Forum Contributor
Posts: 117 Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:
Post
by g3ckO » Thu Aug 26, 2004 3:50 am
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());
?>
delorian
Forum Contributor
Posts: 223 Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland
Post
by delorian » Thu Aug 26, 2004 7:05 am
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';
g3ckO
Forum Contributor
Posts: 117 Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:
Post
by g3ckO » Thu Aug 26, 2004 11:10 pm
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>
?>
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Aug 26, 2004 11:17 pm
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')
Lord Sauron
Forum Commoner
Posts: 85 Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL
Post
by Lord Sauron » Fri Aug 27, 2004 1:07 am
Wouldn't this one work?
$sql = "SELECT * FROM employee e, leave l WHERE e.username ='$user'" AND l.Dept = '$Dept'";
g3ckO
Forum Contributor
Posts: 117 Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:
Post
by g3ckO » Fri Aug 27, 2004 2:29 am
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());
Lord Sauron
Forum Commoner
Posts: 85 Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL
Post
by Lord Sauron » Fri Aug 27, 2004 3:19 am
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.
g3ckO
Forum Contributor
Posts: 117 Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:
Post
by g3ckO » Fri Aug 27, 2004 3:39 am
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?
Lord Sauron
Forum Commoner
Posts: 85 Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL
Post
by Lord Sauron » Fri Aug 27, 2004 3:43 am
What is the table 'leave' exactly used for?
g3ckO
Forum Contributor
Posts: 117 Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:
Post
by g3ckO » Fri Aug 27, 2004 3:49 am
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.
Lord Sauron
Forum Commoner
Posts: 85 Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL
Post
by Lord Sauron » Fri Aug 27, 2004 4:14 am
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
g3ckO
Forum Contributor
Posts: 117 Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:
Post
by g3ckO » Fri Aug 27, 2004 4:42 am
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());
?>
Lord Sauron
Forum Commoner
Posts: 85 Joined: Tue Apr 20, 2004 5:53 am
Location: Tilburg, NL
Post
by Lord Sauron » Fri Aug 27, 2004 5:02 am
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;";