I am currently in the process of converting the player saving features of a game's multi-player engine into an SQL database for the integration of a webpage to display/modify/sell characters. The original system stored all data into text files which was an awful way of dealing with this data as it was fixed to the game only. Within the Text files the user's Username, Password, ID, and player-data was stored, allowing for only one character. I have separated this into tables and can successfully save and load character data. The tables I have are quite large so for example purposes I will use the following:
account:
+----+----------+----------+
| ID | Username | Password |
+----+----------+----------+
| 1 | Player1 | 123456 | (Secure passwords much?)
| 2 | Player2 | password | (These are actually hashed in the real db)
+----+----------+----------+
account_character:
+------------+--------------+
| Account_ID | Character_ID |
+------------+--------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
+------------+--------------+
character:
+----+-----------+-----------+-----------+--------+--------+
| ID | PositionX | PositionY | PositionZ | Gender | Energy | etc....
+----+-----------+-----------+-----------+--------+--------+
| 1 | 100 | 150 | 1 | 1 | 100 |
| 2 | 30 | 90 | 0 | 1 | 100 |
| 3 | 420 | 210 | 2 | 0 | 53.5 |
+----+-----------+-----------+-----------+--------+--------+
These tables are linked using relationships.
What I have so far is, the user logs in and the server queries their username and matches the password. If the passwords match, the server begins to load the character data based on the ID loaded from the account during logging in.
This is where I am stuck. I have successfully done this through phpmyadmin using the SQL command interface, but as it was around 4AM I was tired and accidentally closed the tab that contained the command before I saved it. I have tried to replicate this but I simply cannot obtain the data I require in the query.
I've recently completed a course in databases at college and got a distinction, but for the life of me I cannot get this to work again... I have followed tutorials but as the situations usually differ from mine I cannot apply them until I understand them. I know I'm going to kick myself once I have a working command.
Tl;dr - I wish to query all character data linked to an account using the account's 'ID'.
I think this should work:
SELECT
*
FROM
account_character ac
INNER JOIN account a ON ac.Account_ID = a.ID
INNER JOIN character c on ac.Character_ID = c.ID
WHERE
account.Username = ? AND
account.Password = ?
;
We start by joining together all the relevant tables, and then filter to get characters just for the current user.