Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
Navigation
Marketplace
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,254
Total Threads: 80,792
Total Posts: 566,471
There are 1100 users currently browsing (tf).
 
  Our Partners:
 
  TalkFreelance     Design and Development     Programming     PHP and MySQL :

JOIN two queries using the 1st query results?

Thread title: JOIN two queries using the 1st query results?
Reply  
Page 1 of 2 1 2 >
    Thread tools Search this thread Display Modes  
06-04-2012, 09:45 AM
#1
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old  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
Ant is offline Ant
Status: Member
Join date: Apr 2005
Location: England
Expertise:
Software:
 
Posts: 209
iTrader: 5 / 100%
 

Ant is on a distinguished road

  Old

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

Thanked by:
Dan (06-04-2012)
06-04-2012, 11:55 AM
#3
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

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

Reply With Quote
06-04-2012, 12:22 PM
#4
Ant is offline Ant
Status: Member
Join date: Apr 2005
Location: England
Expertise:
Software:
 
Posts: 209
iTrader: 5 / 100%
 

Ant is on a distinguished road

  Old

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.

Reply With Quote
Thanked by:
Dan (06-04-2012)
06-04-2012, 01:37 PM
#5
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,894
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

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.

Thanked by:
Dan (06-04-2012)
06-04-2012, 03:47 PM
#6
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

When fetching results, would it be $topic['users.id']; $topic['forum topics.id']; ?

Reply With Quote
06-04-2012, 03:55 PM
#7
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,894
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

I don't know off hand, I'd print_r the array and see what it does.

Reply With Quote
Thanked by:
Dan (06-04-2012)
06-04-2012, 04:17 PM
#8
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

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.idtopics.categorytopics.titletopics.texttopics.revisedtopics.lockedtopics.repliestopics.dateusers.usernameusers.roleusers.id 
FROM 
`forum topicstopics
LEFT JOIN users ON users
.id topics.author
WHERE slug
='{$slug}' 
Am I doing it wrong?

Reply With Quote
06-04-2012, 04:20 PM
#9
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,894
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

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]

Thanked by:
Dan (06-04-2012)
09-21-2012, 10:18 PM
#10
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
 
Posts: 3,164
iTrader: 15 / 86%
 

Dan is an unknown quantity at this point

  Old

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?

Reply With Quote
Reply  
Page 1 of 2 1 2 >


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
 
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed