Jump to content

Archived

This topic is now archived and is closed to further replies.

Cloud9247

Multiple tables at once

Recommended Posts

I am trying to make a 'Sort By Series' page for my site, but in order to do that, I have to call from 4 different MySQL tables with one query, at least to make it simple xD Anyways, does anyone know how I could access multiple(4) DB tables with one query?

Share this post


Link to post
Share on other sites
simply list as many tables as you need in the FROM segment:

[code]SELECT table1.stuff, table2.something FROM table1, table2, table3, table4[/code]

Share this post


Link to post
Share on other sites
What do you need from each table?

SELECT table1.info1, table2.info2, table3.info3, table4.info4
FROM table1, table2, table3, table4

This would select different information from all 4 tables

Share this post


Link to post
Share on other sites
this is my query:
[code]
$sql= "SELECT avatars.id, series, active, wallpapers.id, series, active, pngs.id, series, active, layouts.id, series, active FROM avatars, wallpapers, pngs, layouts WHERE  active='1' ORDER BY `series` ASC";
[/code]

But it doesn't seem to work, what is wrong with it?

Share this post


Link to post
Share on other sites
you have to label everything by table.field when you're selecting from multiple tables, otherwise SQL has no idea which table the field you're trying to pull is in.

Share this post


Link to post
Share on other sites
I'm sorry, I don't understand what you are saying exactly, could you show me in code?

Share this post


Link to post
Share on other sites
Cloud..

[code=php:0]SELECT * from avatars, wallpapers, pngs, layouts where avatarts.active = 1 ORDER BY series.id ASC[/code]

Yes?

Share this post


Link to post
Share on other sites
which table is "active" in?  you need to be very specific with MySQL, otherwise your statement becomes ambiguous and impossible to accurately fetch.

in the interest of properly solving this (and leaving you with useful knowledge), what exactly are you trying to pull and from which tables?

Share this post


Link to post
Share on other sites
'active' is in ALL of the tables

And, I am trying to pull the series name from each table, but I want to make it where the series name will not repeat. I also want the series' to sort with eachother, so there won't be a boundry between the tables.

EX:

I want it to go like:
Series 1
Series 2
Series 3

NOT:
Series 1
Series 2
Series 1
Series 2
Series 1
Series 2
Series 1
Series 2

-------------
I still don't understand though, could someone please help me with this specific line?:

[code]
$sql= "SELECT avatars.id, series, active, wallpapers.id, series, active, pngs.id, series, active, layouts.id, series, active FROM avatars, wallpapers, pngs, layouts WHERE  active='1' ORDER BY `series` ASC";
[/code]

Share this post


Link to post
Share on other sites
the easiest way to do this is to go one table at a time, one query for each table:

[code]SELECT id, series, active FROM avatars WHERE active='1' ORDER BY series ASC[/code]

i don't see any reason for you to try and grab all of the info in one query, as that only complicates everything.  reserve your multiple table queries for queries that actually require the correlation of table values (ie. where one value is dependent upon a value from a different table).

nonetheless, for the record, your problem is that you're not enunciating where each of those fields are to come from.  put properly, your query should be:

[code]$sql= "SELECT avatars.id, avatars.series, avatars.active, wallpapers.id, wallpapers.series, wallpapers.active, pngs.id, pngs.series, pngs.active, layouts.id, layouts.series, layouts.active FROM avatars, wallpapers, pngs, layouts WHERE avatars.active='1' ORDER BY `series` ASC";[/code]

however, this query will not at all do what you want it to do.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.