p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Subqueries


Message #1 by WH Micro <wh_micro@y...> on Mon, 15 Apr 2002 14:25:36 -0700 (PDT)
Hello;

Sorry for the multiple questions in this post:

> > PostgreSQL.
Is PostgreSQL free? How difficult, compared to mySQL,
is it to use?

> 
> Don't know what to do about the foreign keys but you
> can
> simulate sub queries with the programming language
> (php
> most probably in this case ;), i.e, do a query,
> store the
> results in a php variable, then do another query
> with this
> variable.  

Ok! This is a good idea but, how am I going to do
this.
This is what I am trying to accomplish with
subqueries:

select * from stats
where userID = ( select userID from user 
where fname = 'John' and lname = 'Smith' )

This is I think I need to do please correct if wrong:

//connected to db
$result1 = mysql_query( "select userID, fname, lname
from user where fname='John' and lname='Smith'");

$rec = mysql_fetch_array( $result1 );

$result2 = mysql_query( "select * from stats where
userID=$result1[0]");

//since theres suppose to be one record...
$record = mysql_fetch_array( $result2 );

print( "Stats for" . $rec["fname"] . $rec["lname"] . "
are:\n" . $record["ab"] . " " . $record["hits"] ....
etc.... );

Would this output what's expected?

Thanks everyone in advance for all your help.



=====
www.whmicro.com

__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
Message #2 by "Nikolai Devereaux" <yomama@u...> on Mon, 15 Apr 2002 14:39:26 -0700
> select * from stats
> where userID = ( select userID from user 
> where fname = 'John' and lname = 'Smith' )

Try this:

SELECT stats.* FROM stats, user
 WHERE user.fname='John'
   AND user.lname='Smith'
   AND user.userID = stats.userID

Or this:

SELECT * FROM stats
  JOIN user ON stats.userID = user.userID


nik
Message #3 by "Nikolai Devereaux" <yomama@u...> on Mon, 15 Apr 2002 17:46:46 -0700
> Or this:
>
> SELECT * FROM stats
>   JOIN user ON stats.userID = user.userID

I just realized that I didn't write the second query specific to John Smith.
Heads up if that's the version you try to use, ok?

Take care,

Nik


  Return to Index