displaying data from one table but ordering by info from another table on a php page

[JMusic]

Okay, I have a really weird problem and I've been racking my brain trying to figure out a way to do this.

I have two tables. One table (we'll call it 'table1') is set up like this:

email address userinfo
[e-mail] userdata1
[e-mail] userdata3
[e-mail] userdata2
etc... etc...

In the above table, the email addresses are in no particular order cause the row is created when someone adds their userinfo.

Then I have 'table2' set up like this:

id email address
1 [e-mail]
2 [e-mail]
3 [e-mail]
etc... etc...

With id being an auto_incriment value, that signifies the order the addresses joined in.

I have a function called 'getidbyemail' that will look up an id number based on an email address, however...

I want to know if there's a way to display the 'userinfo' from table1 BUT list it in the order of the email address's id from table2. I assume this isn't possible with mysql only so I assume there is some type of php coding i would have to do (besides the 'getidbyemail' function i created) to get this to work.

There are already hundreds of rows in each table, so altering the tables would be a HUGE hassle if there's an alternative way I can fix this problem.

[AIS4U]

JMusic:

Well, it looks like you have just had your first lesson on the art of poor database design. You don't appear to have any kind of primary key/foreign key relationship between your tables. And, they most certainly were not normalized or you would not have duplicate information in the two tables.

However, all may not be lost since you do have a common field in the two tables, the email address.

I am not going to write it for you, but it appears that you could write a rather simple query and get the information you want from the two tables.

I would suggest that you read up on writing selection queries.

Pay particular attention to:
SELECT blah blah blah
FROM blah blah blah
WHERE blah blah == blah blah
ORDER BY blah blah

You can go to the MYSQL online manual or most any reasonably good book on MySQL should have the information. Maybe you could even Google something like "MySQL Queries" and find some helpful information.

Good Luck

[JMusic]

That's where my problem lies -- as far as my knowledge goes (and all I could find in some tutorials I looked up before posting this), you can only use "ORDER BY" when selecting data from the same table -- ie the current mysql query to display the information is this:

SELECT * FROM table1 ORDER BY email desc

But obviously that is going to order the info based on reverse alphabetical order of email addresses, not by the id like I need, hence my problem. o_O

[AIS4U]

JMusic:

you can only use "ORDER BY" when selecting data from the same table

I don't think that is correct.

As a matter of fact, I just tried it on a database I have that has 4 tables in it.

I did a SELECT query against two of the tables and used ORDER BY and it worked just fine.