How to code inserts into multiple tables

Anon

I'm working on a database for movie awards and through suggestions rec'd in the past I've normalized data into multiple tables like Categories (Cat_ID,Cat_Name), Movies (Mov_ID,Mov_Name),Individuals (Ind_ID, First_Name, Last_Name) and then the nominations like Nominations (Cat_ID,Mov_ID,Ind_ID,Year,Status)

Having mapped all that out I'm trying to wrap my head around the most efficient way to code the database (odbc to a MS SQL 7) input form.

Let's say I have to enter a nomination as follows:
Cat_ID
Mov_ID
Year
Status
Ind_ID(s) (possibility of more than one person in categories like art direction etc.)

Where it gets confusing for me is in the "Does this person/movie already exist in the database?" area.

If the person/title already exists I just grab their name/title ID and insert it into the nomination row. If they don't exist I have to insert their name/title into the appropriate title/person table, grab the new ID number and then insert that into the nomination table.

Being relatively new to this, I have some ideas, but any comments or pointers to articles to help me code this input form efficiently would be most appreciated.

Anon

Sounds like you've got the basic picture pretty well under control.

Anon

Kirk Parker wrote:

"Sounds like you've got the basic picture pretty well under control."

Then I deserve a Best Actor Award. I had some great advice to make sure I normalized the data, but I still really need help on the most efficient way to code checking whether a piece of data exists in the table and grabbing its ID and if it doesn't inserting it and grabbing that ID.

So I still need some suggestions. :-)

Anon

I'm working on the same problem. Currently, I do a check if the input already exists, insert it if it doesn't, or use the existing id if it does. I do this select and insert step individually for each table.

Such as:
// see if city is already in table, add if not

$cityresult = mysql_query("SELECT cityId, city FROM city WHERE city =
'$city'") or die("city select failed");

if (mysql_num_rows($cityresult)==0) {
//empty result set, so enter city data
$sql5 = mysql_query("INSERT INTO city (city) VALUES ('$city')") or
die("city insert error");
$cityId = mysql_insert_id();
if ($sql5) {
echo("city insert worked\n");
}
}
else {
// city already in table
while ($cityN = mysql_fetch_array($cityresult)) {
$cityId = $cityN["cityId"];
$city = $cityN["city"];
}
}

This works fine, but seems to take FOREVER. (There are 8 tables to check).
Did you find a better way to do this?

Anon

I have a multiple table database accepting input from a form. The basic code works OK, but I'm wondering if there is a better/faster way to do it.

Currently, I do a check if the input already exists, insert it if it doesn't, or use the existing id if it does. I do this select and insert step individually for each table.

Such as:
// see if city is already in table, add if not

$cityresult = mysql_query("SELECT cityId, city FROM city WHERE city =
'$city'") or die("city select failed");

This works fine, but seems to take FOREVER. (There are 11 tables to check).
IS there a better way to do this?

Anon

The alternative is to force the user of the input form to choose from a list of the cities/actors/whatever in the database, rather than allowing them to type whatever they want. Thus, if the city isn't in the database, it won't appear for selection on the form, and the user will know that he or she must add a new city to the database.

Not only will this reduce the number of database operations your script has to perform, but it will avoid duplicate/erroneous entries in the database. With your current system, if the user types a person's name differently than the way it appears in the database (e.g. the middle name written in full instead of as an initial), it will result in a second entry for the same person in the database -- a very undesirable situation!

Think about how to design your form so this inconveniences the user as little as possible (e.g. a radio button that selects between "use exisiting city" and "create new city", the former sitting next to a drop-down list of all the cities in the database, the latter sitting next to a text entry field).