Page 1 of 1

[SOLVED] query problem again..

Posted: Wed Aug 11, 2004 10:57 pm
by g3ckO
This is my first query:

Code: Select all

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

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


   $row_array=extract_user();   
   $Access=$row_array['AccType'];
   $staffNO=$row_array['StaffNo'];
  
   echo"$staffNO";
   echo"$Access";

?>
I want to add another query after the first query:

Code: Select all

<?php

   $q="SELECT * FROM leave WHERE staffSuperior ='$staffNO'";
   $result=mysql_query($q); 
   $row_array=mysql_fetch_array($result); 
   return $row_array; 
   
   $row_array=????   
   $name=$row_array['Nama'];
 
   echo"$name";
?>
The question is how can I add the second query after the first query and echo the result?

What should I change in the second query?

Posted: Wed Aug 11, 2004 11:06 pm
by jslick
I think it's fine.

Posted: Wed Aug 11, 2004 11:07 pm
by g3ckO
What I want to know is how to put one after another?

Posted: Wed Aug 11, 2004 11:10 pm
by feyd
try this as the combo of both your queries..

Code: Select all

SELECT * FROM `leave` a INNER JOIN `employee` b ON b.`StaffNo` = a.`staffSuperior` WHERE b.`username` = '$user'

Posted: Wed Aug 11, 2004 11:17 pm
by g3ckO
Great!! Its execute perfectly... :) Hmm.. but I cannot understand how the 'combo' works.
Can you explain it a little bit or suggest a refference.

Posted: Wed Aug 11, 2004 11:21 pm
by feyd
http://dev.mysql.com/doc/mysql/en/JOIN.html

depending on how you join two or more tables, you can get down to extremely fine details of what you are looking for, or the more useful, combining the information from 1 table with details of another to complete the data you want, within only 1 query so as not to waste resources creating your own filtering and sorting.

Posted: Wed Aug 11, 2004 11:22 pm
by g3ckO
Ok.. Thanks

Posted: Thu Aug 12, 2004 1:21 am
by g3ckO
One more things.

If I want to add another condition to the query like I only want the record from table leave with the value of Status = Pending Review

How will the query look like??

I wrote like below but it return an error:

Code: Select all

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

Posted: Thu Aug 12, 2004 1:56 am
by feyd

Code: Select all

SELECT * FROM `leave` a INNER JOIN `employee` b ON b.`StaffNo` = a.`staffSuperior` WHERE b.`username` = '$user' AND a.`Status` = 'Pending Review'

Posted: Thu Aug 12, 2004 2:04 am
by g3ckO
This is the code:

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'"; 
   $result=mysql_query($query); 

   $num=mysql_numrows($result);

$i=0;
while ($i < $num) 
   {
  
   $name=mysql_result($result,$i,"Nama");
      
    echo"$name";
  

 $i++;
}
?>
and it return the error message:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in c:\apache\htdocs\leaveprocess.php on line 11

It can execute correctly when I delete AND a.'Status' = 'Pending Review' from the query.

Posted: Thu Aug 12, 2004 2:11 am
by feyd
Status should be inside backquotes, not single quotes.

additionally, switch your query call to:

Code: Select all

$result=mysql_query($query) or die(mysql_error());

Posted: Thu Aug 12, 2004 2:16 am
by g3ckO
One more additional question. Simple one :)
Why backquotes?

Posted: Thu Aug 12, 2004 2:24 am
by feyd
backquotes are used by mysql to signal which string is a field/table/database name, versus a value

Posted: Thu Aug 12, 2004 2:31 am
by g3ckO
Problem solved.. Ok, thats all for today.. getting sleepy already :wink:

Thanks feyd