473,237 Members | 1,227 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Getting data in a lookup table to populate automatically

8
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?
Aug 3 '07 #1
3 5292
zapdbf
1
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.
Aug 10 '07 #2
pbmods
5,821 Expert 4TB
Heya, zapdbf. Welcome to TSDN!

The other option is to
Expand|Select|Wrap|Line Numbers
  1. SELECT LAST_INSERT_ID();
Aug 12 '07 #3
Markw
8
Thanks guys for your help,

I'll have to give that a try.
Aug 13 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Michael | last post by:
Hi All, I need a few suggestions. I have the following XML segment: <LookUp> <ControlType>CheckBoxGroup</ControlType> <DBField>LastMedDate</DBField> <ControlName>cmbGoal1</ControlName> <Values...
8
by: Steve Jorgensen | last post by:
Hi folks, I'm posting this message because it's an issue I come up against relatively often, but I can't find any writings on the subject, and I haven't been able to figure out even what key...
1
by: meganrobertson22 | last post by:
hi everybody- what is the best way to add data from one form to another? i have 2 tables: person and contract. here are some of the fields. table: person personid (autonumber and primary...
2
by: Eric Eckberg | last post by:
I have been searching for a solution or example and just can't find one. My application uses lookup tables to populate a combobox. The lookup table is maintained by the user. For instance, the...
13
by: dbuchanan | last post by:
Hello, Here is the error message; ---------------------------- Exception Message: ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child key values (5) to exist in the...
1
geo039
by: geo039 | last post by:
I have a user control that uses a lookup table to populate. When the user clicks the loopup icon for building, a list of items they can choose from appears. How could I populate the same control...
3
by: OzNet | last post by:
I have inherited a database for placing job seekers into jobs. The original table has 3 fields which contain job preferences using codes which had to be looked up in a book. There are around 5000...
1
by: tellercb | last post by:
Hello everyone! I'm new here, so go easy on me ;-) I have a pretty good skillset with VBA for Excel, but now I'm venturing out into Access. So if the solution I'm looking for requires VBA (and I'm...
4
by: Wayne | last post by:
I've used a data field as the rowsource for a combo box many times. The main advantage that I see is that a separate table of say, "Customers" does not have to be constantly updated. When a new...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.