CODEWITHSUNDEEP
phpmysqlsqldatabase
John Lilley
John Lilley

Reputation: 159

Inserting an entry in multiple tables in an sql database

I'm creating a game in actionscript that requires the use of an external database to store user details and scores.

This database will contain multiple tables, currently there are two.

My first table contains the headers - ID, email, username, password.

My second table contains the headers - ID, lvl1Score, lvl2Score, lvl3Score.

In my game, when a new user is created it creates an entry in the first table with the ID auto-incrementing.

My question is - Is there anyway to automatically create an entry in my second table with its default values and the same ID when I add to my first table? I've read about joins, but everything i've read just talks about looking up data over multiple tables.

Also, is my table structure correct in the sence that the ID value can be used using the JOIN keywork to look up an entry from both tables.

Upvotes: 1

Views: 297

Answers (3)

PachinSV
PachinSV

Reputation: 3780

I suggest you to use triggers and for more flexibility create a many-many relationship between "user" and "level", so you will end up with 3 tables:

  • user
  • level
  • user_level (this will contain the foreign keys: user_id, level_id)

Upvotes: 0

Navin
Navin

Reputation: 299

I would suggest you to go for triggers.

create or replace trigger trigger_name after
insert on table1
for each row
begin
insert into table2 values(new.id,"value for lvl2score","value for lvl3score");
end

Something like this.

Upvotes: 1

Dark Falcon
Dark Falcon

Reputation: 44201

If the tables truly have a one-to-one relation, I would recommend that you simply make one table having all the fields.

Or did you mean this should store multiple scores for each individual user? In this case, you should not insert a default record for the user. Instead, the score.ID field should instead reference user.ID and allow duplicates.

Upvotes: 1

Related Questions