Jump to content


Photo

Multiple tables at once


  • Please log in to reply
9 replies to this topic

#1 Cloud9247

Cloud9247
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationMy Computer

Posted 11 July 2006 - 09:01 PM

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?

#2 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 11 July 2006 - 09:17 PM

simply list as many tables as you need in the FROM segment:

SELECT table1.stuff, table2.something FROM table1, table2, table3, table4


#3 komquat

komquat
  • Members
  • PipPipPip
  • Advanced Member
  • 130 posts
  • LocationHoughton, MI

Posted 11 July 2006 - 09:20 PM

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

#4 Cloud9247

Cloud9247
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationMy Computer

Posted 11 July 2006 - 09:59 PM

this is my query:
	 $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";

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

#5 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 11 July 2006 - 10:20 PM

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.

#6 Cloud9247

Cloud9247
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationMy Computer

Posted 12 July 2006 - 02:57 AM

I'm sorry, I don't understand what you are saying exactly, could you show me in code?

#7 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 12 July 2006 - 06:00 AM

Cloud..

SELECT * from avatars, wallpapers, pngs, layouts where avatarts.active = 1 ORDER BY series.id ASC

Yes?

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#8 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 12 July 2006 - 06:08 PM

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?

#9 Cloud9247

Cloud9247
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationMy Computer

Posted 12 July 2006 - 06:57 PM

'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?:

 $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";


#10 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 12 July 2006 - 07:58 PM

the easiest way to do this is to go one table at a time, one query for each table:

SELECT id, series, active FROM avatars WHERE active='1' ORDER BY series ASC

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:

$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";

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users