Closed Thread Icon

Topic awaiting preservation: Mysql query with select from multiple tables (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=27394" title="Pages that link to Topic awaiting preservation: Mysql query with select from multiple tables (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: Mysql query with select from multiple tables <span class="small">(Page 1 of 1)</span>\

 
justin
Neurotic (0) Inmate
Newly admitted

From:
Insane since: Jan 2006

posted posted 01-26-2006 16:31

Okey, I have a small forum that I've written.
In this, I have one table for all the entries in the forums, and another table for all the users.
The entries all have their individual id-number and so do the users.

When I publish each entry I first query to get row with all the data (timestamp, content, author_id etc). Then I do another query with the author_id to get the users data (name, location etc) from the other table.
After all this I publish the data retrieved.
But...it's not that ball-breaking or slow but the code looks.....bad. I want to get all this in one query, and I've read some examples of subqueries and stuff. But is it possible to do one of them AND keep the data from the first query?
Anyone understand the slightest what I'm trying to achieve here?


Very nice forum btw.

/Justin

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 01-26-2006 18:23

You don't need a sub query for this.
You'll need a mysql->join

Basic idea is to select from two tables

code:
select users.*, records.* WHERE records.userId = users.userID AND records.recordId = 234



so long,

->Tyberius Prime

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 01-26-2006 18:28

I don't think you need a subquery for this those are generally used in things like where statements.

Basically what you want to do is something like:

SELECT users.username, users.userinfo entries.title, entries.content FROM users, entries WHERE
users.author-id = '$aid' AND
entries.entry-id = '$eid'



.:[ Never resist a perfect moment ]:.

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 01-26-2006 18:29

or what he said (let this sit for a sec before hitting post and he beats me to it)



.:[ Never resist a perfect moment ]:.

« BackwardsOnwards »

Show Forum Drop Down Menu