[Resolved] help streamlining mySQL query

DjRubbish

hello,

I'm making a flash/php message board.

At the moment, I'm hitting the database twice to get some information. But can I condense these two queries into one?

First I use this query to get the parent_id of the current message from the msg_family table...

$query = "SELECT parent_id FROM msgfamily WHERE msg_number = '$p'";

Then I run another query to retrieve all the information relating to messages with the same parent_id as the current message from the tables users, msg_family and messages.

SELECT * FROM msgfamily AS mf, messages AS m, users AS u WHERE mf.parent_id = '$p' AND m.msg_id = mf.msg_number AND m.user = u.user_id ORDER BY mf.family_id ASC"

But is it possible to make this into just one query?

Thanks for your help.

DjRubbish

excuse the annoying non-wrap.

Um, I just realised that it isn't clear that the value of $p changes between the two queries.

so it gets one value of $p then the parent_id in the first query becomes $p to run the second query.

trex005

First a tip : if no one has responded to your post, and you need to make an addition, I personally reccomend editing your post... this is because if other people are like me, I only look for those with 0 replies. Why I clicked in here, I don't know. Now.. to answer your question.

It appears that your problem is that you want to join a table with itself. this can be accomplished by using the as that you seem to enjoy so much 😉

SELECT
*
FROM
msgfamily as msgfamily_orig
INNER JOIN msgfamily ON (msgfamily_orig.msg_number = msgfamily.parent_id)
INNER JOIN messages ON (msgfamily.msg_number = messages.msg_id)
INNER JOIN users ON (messages.user = users.user_id)
WHERE
msgfamily_orig.msg_number = '$p'
ORDER BY
msgfamily.family_id ASC

no clue what your database looks like, but I hope you get the idea

DjRubbish

hey thanks for helping me to learn something new.

I use AS in queries all the time, but I'm not sure that I need to. Can you tell I'm only just making my first faltering steps with mySQL??? 😉

And thanks for the tip on using the boards, I'll remember that in future.

trex005

More than welcome!... did this actually solve your problem?
I remember after I had probably been using MySQL for 6 months I learned about
LEFT OUTER JOIN
and boy that about quadrupled the power of MySQL for me!

DjRubbish

haven't tried it yet (just had a baby daughter so everything takes much longer than it used to).

I'll let you know.

trex005

CONGRATULATIONS!!!
is this your first?