[SOLVED] query problem again..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

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

[SOLVED] query problem again..

Post 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?
jslick
Forum Commoner
Posts: 35
Joined: Wed Jul 21, 2004 11:18 am

Post by jslick »

I think it's fine.
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

What I want to know is how to put one after another?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

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

Post 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'"; 
?>
Last edited by g3ckO on Thu Aug 12, 2004 1:56 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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());
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

One more additional question. Simple one :)
Why backquotes?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

backquotes are used by mysql to signal which string is a field/table/database name, versus a value
User avatar
g3ckO
Forum Contributor
Posts: 117
Joined: Mon Jul 12, 2004 2:57 am
Location: Malaysia
Contact:

Post by g3ckO »

Problem solved.. Ok, thats all for today.. getting sleepy already :wink:

Thanks feyd
Post Reply