Getting data in a lookup table to populate automatically

[Markw]

I think this was recently asked but I was a little lost on the example that was used so I'm reasking it with an example I can understand.

Also forgive me for such a basic question but I really am new to Mysql and PHP.

In the book "Build Your Own Database Driven Website Using PHP & Mysql" by Kevin Yank, they give an example for when using a many to many relationship that it is best to use a lookup table. The example they show of how it works is fine, I understand it however they have you manually enter the needed data into the fields. I'm sure later in the more advance sections they will show me how to do this but I don't see it jumping out at me when I've looked for it.

What I'm wanting to learn is how to have the data entered automatically from one tables field, in this case ID entered into the lookup table. Clearer in example below of what I'm looking for.

Example that was given in the book:

Table = joke
Fields = id and joketext
Primary key = id and it is auto incremented

Table = category
Fields = id and category
Primary key = id and it is auto incremented

Table = jokecategory and is the lookup table
Fields = jokeid and categoryid
Both are Primary Keys.

So what I want to have happen is when a new record is entered in joke table it's id value is entered into the jokeid field of the jokecategory table and then when a category is selected for this joke it's id value is entered into the categoryid field of the jokecategory table.

How does one do this?

[zapdbf]

I think this was recently asked but I was a little lost on the example that was used so I'm reasking it with an example I can understand.

Also forgive me for such a basic question but I really am new to Mysql and PHP.

In the book "Build Your Own Database Driven Website Using PHP & Mysql" by Kevin Yank, they give an example for when using a many to many relationship that it is best to use a lookup table. The example they show of how it works is fine, I understand it however they have you manually enter the needed data into the fields. I'm sure later in the more advance sections they will show me how to do this but I don't see it jumping out at me when I've looked for it.

What I'm wanting to learn is how to have the data entered automatically from one tables field, in this case ID entered into the lookup table. Clearer in example below of what I'm looking for.

Example that was given in the book:

Table = joke
Fields = id and joketext
Primary key = id and it is auto incremented

Table = category
Fields = id and category
Primary key = id and it is auto incremented

Table = jokecategory and is the lookup table
Fields = jokeid and categoryid
Both are Primary Keys.

So what I want to have happen is when a new record is entered in joke table it's id value is entered into the jokeid field of the jokecategory table and then when a category is selected for this joke it's id value is entered into the categoryid field of the jokecategory table.

How does one do this?

how i do it is to insert record into the joke table, then make a quick
select query like this "select max(id) as id from joke - this will give you the last id entered, the only flaw with this approach is if a insert query sneaks in-between
your insert and the select statement, so far i have not had any trouble with this.
store this value then repeat this for your category table as well, if you are allowing them to enter a category. if this were me i would not use the lookup table, i would put the category id straight into the joke table, the only advantage i see to the lookup table is that you can look up from either direction. if you want to look up to see how many categories that joke is in then use the three tables, but if you are just needing to look up the joke by the category only it is more efficient to just use the two tables.

[pbmods]

Heya, zapdbf. Welcome to TSDN!

The other option is to [code=mysql]SELECT LAST_INSERT_ID( );[/code]

[Markw]

Thanks guys for your help,

I'll have to give that a try.