|
|
|
|
Thread title: JOIN two queries using the 1st query results? |
|
|
|
|
|
Thread tools
Search this thread
Display Modes
|
|
06-04-2012, 09:45 AM
|
#1
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
JOIN two queries using the 1st query results?
PHP Code:
$db->query("SELECT * FROM `forum topics` WHERE `slug` = '{$slug}' LIMIT 1");
$topic = $db->fetch();
$db->query("SELECT * FROM `users` WHERE `id` = '{$topic['author']}' LIMIT 1");
$author = $db->fetch();
Is it possible to join these two queries? The second query is using results gathered from the 1st query.
|
|
06-04-2012, 10:56 AM
|
#2
|
Status: Member
Join date: Apr 2005
Location: England
Expertise:
Software:
Posts: 209
|
is 'forum topics' one table? with a space in the name?
Anyway...
You might be able to do:
PHP Code:
SELECT * FROM 'forum topics', 'users' WHERE 'forum topics.slug' = '{$slug}' AND 'users.id' = 'forum topics.author' LIMIT 1
Not 100% sure, I'm not that great with SQL
|
|
06-04-2012, 11:55 AM
|
#3
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
Yeah it's one table. Is this method better than actually joining the two tables?
Here's what I had:
SELECT * FROM `forum topics` t join `users` u on `u.id` = 't.author' WHERE `t.slug` = '{$slug}' ORDER BY `t.id` LIMIT 1
|
|
06-04-2012, 12:22 PM
|
#4
|
Status: Member
Join date: Apr 2005
Location: England
Expertise:
Software:
Posts: 209
|
I'm not sure which is better, if it works then I suppose it's ok I think it's called an equi-join so could give it a google.
|
|
06-04-2012, 01:37 PM
|
#5
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
As a general rule it is best to use one query opposed to two because each query has overhead for execution. Even if one query takes longer than either two it will still run faster than the two combined. It's also better practice, you want your database to do as much of the data processing as possible opposed to having PHP play a part in it. Also, don't use SELECT *, it takes longer to run and leaves ambiguity in your code. Do something like
Code:
SELECT topics.field1, users.field2 [ect]
FROM `forum topics` topics
LEFT JOIN users ON users.id=topics.author
WHERE slug=[$slug]
I wouldn't select from two tables here since you are conditionally joining a second table to a first, that is what JOIN is for.
|
|
06-04-2012, 03:47 PM
|
#6
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
When fetching results, would it be $topic['users.id']; $topic['forum topics.id']; ?
|
|
06-04-2012, 03:55 PM
|
#7
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
I don't know off hand, I'd print_r the array and see what it does.
|
|
06-04-2012, 04:17 PM
|
#8
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
In case anyone wonders why printing array of a joined table duplicates the data: http://stackoverflow.com/questions/4...ned-join-array
Anyway, if I have two tables joining together with identical coloumn names (in this case ID.) The array prints the second ID as a number, so I have to use $topic['10'];
For example
PHP Code:
[0] => 3 [id] => 12 [1] => 2 [category] => 2 [2] => Sample Post Title [title] => Sample Post Title [3] => Let's see if the favourite/save star icon shows up yellow or hidden and grey. [text] => Let's see if the favourite/save star icon shows up yellow or hidden and grey. [4] => [revised] => [5] => [locked] => [6] => 0 [replies] => 0 [7] => 2012-06-03 13:45:40 [date] => 2012-06-03 13:45:40 [8] => abracadabra [username] => abracadabra [9] => 1 [role] => 1 [10] => 12
PHP Code:
SELECT topics.id, topics.category, topics.title, topics.text, topics.revised, topics.locked, topics.replies, topics.date, users.username, users.role, users.id FROM `forum topics` topics LEFT JOIN users ON users.id = topics.author WHERE slug='{$slug}'
Am I doing it wrong?
|
|
06-04-2012, 04:20 PM
|
#9
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
I'd name the columns then, something like the following but with more descriptive names
Code:
SELECT topics.field1 AS name1, users.field2 AS name2, [ect]
FROM `forum topics` topics
LEFT JOIN users ON users.id=topics.author
WHERE slug=[$slug]
|
|
09-21-2012, 10:18 PM
|
#10
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
PHP Code:
$db->query("SELECT t.id FROM `users themes` ut LEFT JOIN `themes` t ON ut.themeid = t.id WHERE ut.userid = '{$user->id}' ORDER BY date ASC");
// loops theme ID to create OR 'theme' =
while($foo = $db->fetch()){
if(!isset($var)){
$query = "`theme` = '".$foo['id']."'";
$var = 1;
} else
$query .= " OR `theme` = '".$foo['id']."'";
}
$db->query("SELECT slug, title from `forum topics` WHERE $query ORDER BY date ASC");
Basically the first query checks a table for rows that match the userid, and then the second query uses the matching corresponding themeid's to grab forum topics.
This works but I feel cheap. Is their a better solution?
|
|
|
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
|