Help Creating a MYSQL Query

Question

Firstly - Could you tell me if there is a better 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.

I think that you should really read a lot about SQL and then create some applications. Get some online tutorial which will introduce you to SQL. — Marcin
– Marcin, Commented May 6, 2011 at 19:25
This is a very basic question which would be covered in just about every mysql tutorial, I think you may want to read up on mysql usage before continuing on your project. — eykanal
– eykanal, Commented May 6, 2011 at 19:25

Jim Rubenstein · Accepted Answer · 2011-05-06 19:28:52Z

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.
To update LastLogin you have to run a separate UPDATE query
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.

answered May 6, 2011 at 19:28

Jim Rubenstein

6,9505 gold badges38 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Thanks, Works perfectly. If i were to edit your code is it possible to join more than 2 tables?

Jim Rubenstein Over a year ago

You can join 100 tables if you want, just add JOIN <TABLE> ON <CONDITION> for each table you want to join, and remember to edit the SELECT cause to get the correct columns that you want / need.

Collectives™ on Stack Overflow

Help Creating a MYSQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related