[SOLVED] Confused as to how to go about an insertion

[cbear2021]

Hi, I need a little advice regarding a php insertion into a table.  Basically - I've got my website running so users can register an account and login using a mysql database, and the next stage is to allow users to upload pitch details for film projects they're working on, along with what genre the film is, and what resources they need (like directors, lighting, actors, etc.)  I've set the form up so that it's a text box for the proj name, and text area for pitch, and checkboxes for the genre and resources. 

 

I've also set my tables up so that I've got my user table, pitch table, a look up table each for the genre and resources, and then category tables for the genre and resources, all linked via primary/foriegn keys etc. 

 

so like user id is the foriegn key in the pitch table, and pitch_id is the foriegn key in the look up tables, and genre_id and resource_id from the resource_category/genre_category tables are also foreign keys in the lookup tables (and combined they make a primary key).

 

I know roughly how I can do the insertion into all the tables (using the array for checkboxes etc) - but what i'm confused about is how do i associate the current user who's logged in with the pitch they've just uploaded, so that I can have a submitted by user X next to each pitch when i paginate them to the screen? 

[Maq]

but what i'm confused about is how do i associate the current user who's logged in with the pitch they've just uploaded, so that I can have a submitted by user X next to each pitch when i paginate them to the screen?

 

When they login just store their username in a SESSION variable so when they upload a pitch you can just do:

 

echo "submitted by user : " . $_SESSION['user'];

 

Does this answer your question?

[cbear2021]

Sort of - but with regards to the insertion - because the tables are all linked via pri/foriegn keys, will it automatically associate the current user's id with pitch?  sorry - i know i'm not wording this very well, i'm quite new to php/mysql and i'm trying to figure out the best way to explain what i mean. 

 

so like for example, the user logs in with their email/password, and goes to the upload project page, when they upload the project, because user_id is a foriegn key in the pitch table, will the users id automatically be put next to the pitch id, so for example:

 

pitch_id  |  user_id  |          pitch_name                |          pitch            |

 

  1              4              killer babies from space        killer babies descend...

 

 

or do I have to somehow manually tell php to insert the users ID into the pitch table?

 

 

Thanks, and sorry if this isn't 100% clear

[Maq]

You need to normalize your tables...

 

should be 3 tables here

 

pitch

----------------

pitch_id    pitch_name    pitch

 

users

----------------

user_id        user_name    pitch_id

 

users_to_pitch

----------------

users_id    pitch_id

 

 

So now you can relate pitch.pitch_id to the users table pitch_id.

 

Now for inserting into the DB...

 

When they upload the pitch, put it in the pitch table.  pitch_id should be an auto-increment.  Then when you go to add to the user_to_pitch table you can use

 

mysql_insert_id();

 

to grab the last inserted id of the pitch.  So in the user_to_pitch table you would be inserting:

 

$pitch = mysql_insert_id();
$id = $_SESSION['id'];
$sql = "INSERT INTO users_to_pitch (user_id, pitch_id) VALUES ('$id', '$pitch');

 

Hope this makes sense and helps.

[cbear2021]

Hi - I've solved my issue, and the result is in the mysql forum.  Thanks for your help though, I got it all working!