Query a Query

[lard]

Hi

 

I've tried to find an answer for this all over the place but can't find one, so I suspect it's not possible. I'm new to php and SQL and would have thought this should be fairly simple. I want to be able to query the results of a previous SQL query within PHP.

 

I know I can do something like this within a single query:

 

SELECT * FROM

(SELECT * FROM first_table WHERE column_A = 'WHATEVER')

derivedtable

WHERE column_B = 'SOMETHINGELSE'

 

That works, and it's fine to an extent. However, my queries are getting pretty complex with various INNERJOINs, ANDs + ORs working exclusively from each other and the whole shebang. They are also going to be evolving and used in several places in different ways so I want to keep everything as simple as possible.

 

What I would love to be able to do is something like this within php:

 

$sql="SELECT * FROM first_table WHERE column_A = 'WHATEVER'";

 

$result = mysql_query($sql);

 

$secondquery = "SELECT * FROM '$result' WHERE column_B = 'SOMETHINGELSE'";

 

$result2 = mysql_query($secondquery);

 

Whatever syntax I try it doesn't seem to work. Anyone got any ideas? It must be possible, mustn't it??!

 

Thanks

 

 

[JasonLewis]

You need to understand that mysql_query() returns a MySQL Result Resource. To retrieve the individual row values you need to either loop through with mysql_fetch_assoc() or just use mysql_fetch_assoc() in which case the first returned row will be displayed.

[trq]

Whatever syntax I try it doesn't seem to work. Anyone got any ideas? It must be possible, mustn't it??!

 

Its possible but far less efficient than getting the job done in a single query.

[lard]

Cheers for the responses so quick!

I didn't really understand what you mean thorpe, but thanks, I'll go and research the mysql_fetch_assoc(). Still a newbie

ProjectFear - do you mean less efficient therefore slower execution? I don't think that should be a problem for me as the database that I'm using isn't particuarly big (it will grow but never that much). Or did you mean that the coding gets long and complicated?

Thanks again I'm coming back here again!!

 

[lard]

Sorry - got the names the wrong way round!!

[trq]

Think you got our names mixed up hehe.

 

Yes I meant less effecient as in slow and combersome. Just because you assume your application will not grow, theres no reason to make poor design choices.

 

Its strange actually. Most new comers do it they way your asking about because they know no better, once they have more experience the learn to do it the right way (the way your currently doing it, ie; JOINS and such). It would seem your doing a complete reversal. Trying to unlearn?

[lard]

Haha! Nope, I tried that way first of all, couldn't get it working so figured I'd keep going with the single query as long as I could until it got too complicated! And then it got too complicated!

 

So if I continue on that route, is it possible to continue with the:

 

SELECT * FROM (SELECT * FROM...(SELECT * FROM.....

 

as many times as may be neccessary, or is there a limit to that?

 

Sorry about the names btw, faces I can do but names...

[RichardRotterdam]

when building large queries I often find it useful to notate the query in a way that it will be still readable when large.

you can use new lines and indents to make the whole thing more readable.

That would be a lot better then using multiple queries and slowing stuff down.

 

And if things really get complex where a query wouldn't be sufficient you might want to look into stored procedures

[lard]

Thanks Kat

 

Definitely good advice, and what I've been trying to do. A mate just mentioned stored proceedures to me as well - a bit of reading for tonight me thinks...