Help Creating a MYSQL Query

Firstly - Could you tell me if there is a bet开发者_JAVA百科ter way to do this, perhaps using foreign keys... I once read about them but don't really understand how to use them.

I have two tables that I would like to get get data from. AccountDetails and AccountUsage both tables have a unique id of AccountID that should match for each user.

I need to compare the AccountEmail in the table AccountDetails to a value that the user has imputed, and then if there is a match, get all of the data from both tables and update data in AccountUsage. I understand i might need another query to update the data in AccountUsage but just mentioned it in case there is another way to do it, It will update the LastLogin to today's date.

So, I hope your still with me, this is the code that i have at the moment,

SELECT AccountID, AccountPassword, BandID FROM AccountDetails WHERE AccountEmail=?

After all of that - the key points are;

• Is there a better way with foreign id's (really don't know if these are relevent in this).

• If not, canyou help me with the query, I think a join is required?

• Is there a way to automaticaly upadate the LastLogin to todays data or do i need another query?

Thank You So Much

Chris

EDIT: This is some code i used last time (im converting the application to oop php).

SELECT AccountUsage.AccountID, AccountUsage.AccountActive FROM AccountUsage INNER JOIN AccountDetails ON AccountUsage.AccountID=AccountDetails.AccountID WHERE AccountDetails.AccountEmail=?

This was after already running a different query to get data from the first table, idealy i would like it all in 1 query.

Return format as an array would be nice, for php.

1. To get your user's data based on their email, you you do need a JOIN. You will join your tables based on AccountID (make sure it is the Primary Key on both tables, so it's fast) and you will test the AccountEmail in the WHERE clause. Make sure you have an index on the AccountEmail column, so it is quick also.

   Query:

   SELECT AD.AccountID, AD.AccountPassword, AD.BandID, AU.*
   FROM AccountDetails AD
   JOIN AccountUsage AU on AU.AccountID = AD.AccountID
   WHERE AD.AccountEmail = ?
   

   I have aliased AccountDetails to AD and AccountUsage to AU, this makes disambiguating the columns in the tables easier, so you don't have to type the full table name each time you need to reference it.

2. To update LastLogin you have to run a separate UPDATE query

3. Foreign Keys won't really help you pull the data out, here. Foregin Keys are built to maintain data integrity. Meaning you'd set a Foregin Key constraint for AccountID on AccountUsage which links back to AccountDetails, and you wouldn't be able to insert any rows into AccountUsage which didn't reference back to a row in AccountDetails. That might be a little confusing, but the takeaway is - you don't really need it here to pull data out. It's just a structural feature that helps you maintain consistency with your data.