Perl/mysql: retrieve id and name for record already in the database or create newname

[Francesca]

Hi mysql and perl experts,

I am new to both perl and mysql, and I am trying to populate a database that I had previously created in mysql. I am stuck on a step and don't really know how to move on and would REALLY appreciate suggestions...
I need to insert new data on individuals which refers to another table (called "location") through a foreign key, but I first need to check whether this location has already been entered: if it has already been entered then take the location_id and use this to enter the individual data which refers to this location, otherwise create a new location_id. Then, if there is no location_name, then enter an invented one from the last_inserted id (which differentiates the ones that already had a name from the ones that did not).

This is what the mysql tables look like:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----Table_1-----------
LOCATION_ID (AUTO-INCREMENT) LOCATION SITE NAME

1 1234 4 syd
2 1235 4 brux
3 1236 5 temp_1

-----Table_2-----------
ASSIGN_ID (AUTO-INCREMENT) LOCATION_ID (FOREIGN KEY) ASSIGN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
And this is where I got stuck and what I am trying to do with the perl script:

I am looping through my data file and creating these variables for the columns in Table 1 and Table 2:
my $location = shift(@fields);
my $site = shift(@fields);
my $name = shift(@fields);
my $assign = shift(@fields);


#see if record exists
my($id, $location_name) = $dbh->selectrow_array(qq{SELECT location_id, location_name FROM location WHERE location = $location && site = $site });

## If there is already an entry, then use the location_id and name entered in Table 1 (and if the name is not entered already in the database, then enter this name) --- In this step I do not need to do anything in table_1, but I need to select this location_id (and name if there is one), maybe using a “selectrow_array”?

## If there is not an entry, then insert the new data into Table_1 using the “name” if there is one, and if there is no name entering the new data in Table_1 using a temporary name such as “temp_1”.
# I DO NOT KNOW HOW TO DO THIS....MAYBE SOMETHING LIKE THIS? ...
if(!$id){
my $sth = $dbh->do(qq{
INSERT location
SET location = $location, site = $site, location_name = '$location_name',
});
}
elsif(!$id){
my $sth = $dbh->do(qq{
UPDATE location
SET location_name = '$location_name'
WHERE location_id = $id
});
}

$dbh->do(q{INSERT INTO table_1 (location, site, name) VALUES (?, ?, ?)}, undef, $location, $site, $name);
# get the primary key value of the inserted record of Table 1:
my $location_id = $dbh->selectrow_array (qq (select last_insert_id() ));

##And if there is no name, then maybe do:
update location where location_id = last_insert location_name = "temp_$id"

## Then enter the new data for Table2, using either the newly entered location_id or the location_id that was fetched in step 3…

$dbh->do (qq (insert into table_2 (location_id, assign) values (?,?) ), undef, $location_id, $assign);


Thank you so much for any help!! I really appreciate any suggestions...
-f

[code green]

Rather than all this code could you make use of

INSERT INTO table (..) VALUES (..)
  ON DUPLICATE KEY UPDATE ....

[Francesca]

Hi code green,

Thank you for your reply.
I am not sure about the benefits of using the "on duplicate key update", and since I am quite confused I thought to work an "insert" even if it is not the most practical way...But I will check it out!
I could make the code work by creating the query using "selectrow_array" and then repeating all the conditions if there is an entry, and again if there is no entry...

-f