[SOLVED] Select From

[TutorMe]

Is there a way to select from multiple mysql tabels?

[pocobueno1388]

Yes, there is.

[TutorMe]

Ok.  If I have this:

$result = mysql_query("SELECT * FROM January ORDER BY day");

How would I select from more than just January?

 

 

[pocobueno1388]

Well, you are going to need a unique key in each table that relate to eachother first.

 

Could you give more information on both tables?

[corbin]

If you just want to select from multiple tables, it's as easy as:

 

SELECT something FROM table1, table2

 

But, chances are, that's quite useless.... Let's say you have something like this:

 

table users:

 

id     name    password

 

and then you have another table called friends:

 

user_id   friend_id

 

To get the name of the friend, you could do something like:

 

SELECT f.friend_id, u.name FROM friends as f INNER JOIN users as u ON u.id = f.friend_id WHERE f.user_id = 1

 

That would return the friend IDs and names for the user ID 1.

[TutorMe]

To: pocobueno: I'm not sure exactly what you mean.  I created each table with the following code:

CREATE TABLE `*Month` (
`username` VARCHAR( 20 ) NOT NULL ,
`month` VARCHAR( 13 ) NOT NULL ,
`day` INT( 2 ) NOT NULL ,
`year` INT( 4 ) NOT NULL
) TYPE = MYISAM ;

*Month being replaced by whichever month's table I was creating.  Is that what you were asking for?

 

To: Corbin:  Each table has identical rows, so I tried the following:

$result = mysql_query("SELECT * FROM January, February, etc... ORDER BY day");

However, it just turned up blank.  I appreciate your replies.  I think I'm overlooking something.

 

[darkfreaks]

<?phpresult = mysql_query("SELECT * FROM January,February ORDER BY day");?>

[TutorMe]

<?php("SELECT January, February *FROM content  ORDER BY day";?>

 

That code worked somewhat, but only when I removed the ORDER BY part.  However, when I did this it only showed information from February.

[darkfreaks]

instead of day try Order by ASC or Desc

[darkfreaks]

do you havew a column in the database with a the days in the month?

[TutorMe]

That didn't seem to help.  I appologize for the difficulty.

 

Yes, I do have that row, but what the code returns almost looks like I don't.

 

On another page, the code works with only one month and WITH the ORDER BY day, so i don't know what to do.

[pocobueno1388]

Take a look at this tutorial:

http://www.tizag.com/sqlTutorial/sqljoin.php

[TutorMe]

Thanks for the link, but I actually need to keep the tables separate, but want to display information from all of them in one table.

 

Here is an example using ORDER BY day, but with a different database: http://tutorme.homeip.net/bdays/index.php.

 

It works.  Keep in mind though, that a. there is not much info there, and b. it is displaying each table in a separate table.

[darkfreaks]

try

 

ORDER BY days ASC

[TutorMe]

That didn't work either.  Could it have anything to do with the way my test server is setup?

[corbin]

Hmmmm.....

 

You could try something like:

 

SELECT * FROM January, February, March, April....

 

Assuming you had something like

 

January

------------

corbin jan 3 1991

TutorMe jan 15 1985

SomeOne jan 15 1985

 

February

--------------

SomeOneElse feb 1 1920

some1 feb 23 1995

someoneelse feb 25 1990

 

It would return something like:

 

corbin jan 3 1991

TutorMe jan 15 1985

SomeOne jan 15 1985

SomeOneElse feb 1 1920

some1 feb 23 1995

someoneelse feb 25 1990

[TutorMe]

This is the wierdest thing.  I tried your code corbin (with january, february, and march), and it only showed info from march, and then, all the info in march was shown twice.

 

I'm baffled.

[darkfreaks]

SELECT January, February, etc FROM January, February, etc

[corbin]

My bad..... My format was entirely incorrect...

 

When you do SELECT * FROM table1, table2 it selects the columns from table1 and table2, not all of the rows.... bleh... sec

 

 

Edit:

 

Ahhh.... After some thinking, I came up with:

 

SELECT * FROM jan

UNION

SELECT * FROM feb

UNION

SELECT * FROM march

UNION

SELECT.... blah blah blah

[darkfreaks]

yeah i just looked it up before you posted there.

 

was ifxing to recomend it but you beat me to it 

 

Please hit the solve button

[TutorMe]

Wow.  Thank you guys so much.  I thought it was hopeless.

 

This did exactly what I was wanting to do.

[pocobueno1388]

Don't forget to press "solved"

[TutorMe]

Sorry.  Thanks Paco.