MySQL noob question: player table, location table, JOIN

[Choronzon]

I have a player table, which is made up of fields:

id SERIAL NOTNULL AUTO_INCREMENT (this is the primary key, auto increment so each player is assigned a unique key)

player VARCHAR (just a text player name)

player_id VARCHAR(text matching the players in-game number)



then i have a locations table, with fields:

lid SERIAL NOTNULL AUTO_INC (primary key, each location is assigned a unique key)

pid INT NOTNULL (this is supposed to match the id of the player table, so this location entry gets associated with the players id)

location VARCHAR (text, one of many player's starbase locations)

econ VARCHAR (text, base stats)

def VARCHAR (text, base stats)



Question is:
how do i create an INSERT statement that inserts the players name into the player table, then uses the id from the player table, inserts that into the pid table of my locations table? then how do i query the DB to find all locations of any player, using the pid number assigned to each location?

any help is appreciated! and by the way its a very fun game, quite addictive!

[ronverdonk]

You'll have to use 2 inserts because you want to use the auto_increment value of id in the second insert and the first insert has to be completed. So you combine them into 1 query like this:

INSERT INTO player VALUES(0,'John', '10');
INSERT INTO locations VALUES(0,LAST_INSERT_ID(),'Warwick','qq', 'rr');

You are not explicit about on what basis you want to select, but assuming you want it on player's name, the statement could be like:

SELECT player,location FROM player,locations 
       WHERE player='John' AND player.id=locations.pid;

When you want all players and their locations:

SELECT player,location FROM player,locations 
       WHERE player.id=locations.pid
       ORDER BY player;

Ronald :cool: