Selecting date from multiple tables WHERE...

[BluMess]

Hey

I've been playing around with selecting data from multiple tables, but I can't get it to work properly.

 

I need to select certain rows from 3 different tables (all with the same column names) where the column 'status' = approved (it's the same in each table), then sort all the rows by their datetime.

 

This is what I have so far:

 

SELECT user_games.id , user_games.type , user_games.title , user_games.author , user_games.thumbnail , user_games.short_d, 
user_toons.id , user_toons.type , user_toons.title , user_toons.author , user_toons.thumbnail , user_toons.short_d, 
user_art.id , user_art.type , user_art.title , user_art.author , user_art.thumbnail , user_art.short_d F

ROM user_games, user_toons, user_art 

WHERE status='approved' <-- I can't get this line to work!

 

It all works until I put a WHERE in. I haven't even tried ordering the results yet D:

 

 

Any help would greatly be appreciated!

Thanks!

[premiso]

WHERE user_games.status='approved' AND user_toons.status = 'approved' AND user_art.status = 'approved'

 

Is how you would need to do it. I am not sure if this will work, however, since you do not seem to have a relational link between the 3 tables.

[BluMess]

WHERE user_games.status='approved' AND user_toons.status = 'approved' AND user_art.status = 'approved'

 

Is how you would need to do it. I am not sure if this will work, however, since you do not seem to have a relational link between the 3 tables.

 

Hm, you're right it doesn't work - thanks for the quick response anyway.

 

Relational link? :S I'm a bit new to this

 

 

[premiso]

WHERE user_games.status='approved' AND user_toons.status = 'approved' AND user_art.status = 'approved'

 

Is how you would need to do it. I am not sure if this will work, however, since you do not seem to have a relational link between the 3 tables.

 

Hm, you're right it doesn't work - thanks for the quick response anyway.

 

Relational link? :S I'm a bit new to this

 

 

 

Relational databases means that 1 table can link to another by a foreign key. So if these tables are to be related you should have a unique key in each that relates one to another. However, I do not think this is what you want here, as those tables do not seem to need to be related to each other. You just need to run 3 separate queries to get the data you want. If they should be related, then let me know and I can explain a bit more on the relationship part.

[BluMess]

Hm. I would use 3 queries but the only problem is I want them to show up in the same place, not separated out - so all of the rows can be put together and then sorted by their datetime row

[premiso]

Nest the queries...

Not sure if this is the best way, but yea.

 

<?php
$query = mysql_query("SELECT * FROM user_games WHERE status = 'approved'");
$i=0;
$data=array();
while ($row = mysql_fetch_assoc($query)) {
    $data[$i]['user_games'] = $row;
    $i++;
}

    $i=0;
    $query2 = mysql_query("SELECT * FROM user_toons WHERE status = 'approved'");
    while ($row2 = mysql_fetch_assoc($query2)) {
        $data[$i]['user_toons'] = $row2;
        $i++;
    }

    $i=0;
    $query3 = mysql_query("SELECT * FROM user_toons WHERE status = 'approved'");
    while ($row3 = mysql_fetch_assoc($query3)) {
        $data[$i]['user_art'] = $row3;
        $i++;
    }
print_r($data);
?>

 

But each table data will not necessarily be related to each other. Is there a way that each table is related, or did you just want to show random data together ?

 

EDIT:

Fixed my logic.

[BluMess]

I'm not sure if this is what you mean, but like I said each table has the same names for columns, the only main difference is what is in the rows of each table.

 

If I'm right will that script show 1 game, then 1 toon then 1 art, repeating as many times as necessary?

 

Incase you didn't guess, it's for showing toons, games and art which has been approved (status='approved') in a list sorted by when it was added (the row 'datetime')

 

[premiso]

I'm not sure if this is what you mean, but like I said each table has the same names for columns, the only main difference is what is in the rows of each table.

 

If I'm right will that script show 1 game, then 1 toon then 1 art, repeating as many times as necessary?

 

Incase you didn't guess, it's for showing toons, games and art which has been approved (status='approved') in a list sorted by when it was added (the row 'datetime')

 

 

Here is the question. If all the data is the same why have 3 tables? Why not add another column to the table called datatype  if this is 1 it is a toon if it is a 2 it is art etc etc.

 

Make it a lot simpler especially for retrieving data.

[BluMess]

True, I should've thought of that

 

It's just the tables are used for other purposes, and the art table has different columns, but I suppose I could get round that. Thanks for all your help

 

So I can't achieve what I want with the tables how they are atm?

[premiso]

True, I should've thought of that

 

It's just the tables are used for other purposes, and the art table has different columns, but I suppose I could get round that. Thanks for all your help

 

So I can't achieve what I want with the tables how they are atm?

 

You can, it is just about 5times more work.  I would add the extra columns art needs to the table, add the datatype column then go from there. That way you can order it by type, name a certain column etc and filter it that way too. This will also save coding time down the line as you just need basic 1 query to do it all and just have the datatype field on the php page be dynamic to what you want.