simple MySQL prob

caesar has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
(jeffa) Re: simple MySQL prob by jeffa (Bishop) on Mar 03, 2002 at 21:45 UTC
You have to do this with two queries, first issue a select to see if the value exists. Then, if the value does not exist (via Perl's if or unless), insert it with the second query. jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply]
Re: simple MySQL prob by Tardis (Pilgrim) on Mar 03, 2002 at 23:28 UTC
Unless your application can be guaranteed to be running one instance at a time (ie, not a web CGI), you need to care about transactions. Example: Instance 1: Does key XXX exist? DB: Nope. Instance 2: Does key XXX exist? DB: Nope Instance 1: Please insert new row with key XXX DB: Done! Instance 2: Please insert new row with key XXX DB: <Explosion> Check the DBI manpage for details on transaction support. If you aren't using DBI, or your database doesn't support transactions, you should use one that does :-) Alternatively, code your database schema and application such that it doesn't need to do such a check. This may well be possible, but it's impossible to say without knowing your application.	[reply]
Re: simple MySQL prob by fuzzyping (Chaplain) on Mar 03, 2002 at 23:12 UTC
As jeffa suggests, you need to break your process into 2 actions. And as I refer to here, you might consider loading all of your values into hash keys at the beginning of your script. Handling the comparison in the hash will increase your performance, although some might argue that this doesn't scale as well. I beg to differ... my particular DBI script went from a runtime of minutes (allowing the database to handle the logic) to just under 7 seconds (loading the keys and then performing the logic in the hash). Here's an example... my $session = shift; # value we want to query for my $dbh = DBI->connect("DBI:mysql:$database:$server","$username","$pas +sword"); &extract_hash; unless ($session_hash{$session}) { &inject; } $dbh->disconnect(); sub extract_hash { my $select_query = "SELECT session FROM stats"; my $sth0 = $dbh->prepare($select_query); $sth0->execute(); while (@data = $sth0->fetchrow_array) { $session_hash{$data[0]} = 1; } } sub inject { my $insert_stmt = "INSERT INTO stats (date, time, user, session, action, type, zone) VALUES (?,?,?,?, +?,?,?)"; my $sth2 = $dbh->prepare($insert_stmt); $sth2->execute($date,$time,$user,$session,$action,$type,$zone) +; } [download] UPDATE: As Tardis alludes to below, you might need to worry about transactions, depending on your usage. I ass-u-me'd that you'd be performing this in a similar context to mine, where I run it in an isolated environment via cron. That may or may not be the case. -fuzzyping	[reply] [d/l]
Re: simple MySQL prob by screamingeagle (Curate) on Mar 03, 2002 at 21:44 UTC
u can do this to check if a value exists : fire an SQL query before the the INSERT statement to check whether the value exists : `select count(*) VALEXISTS from tablename where fld = 'fldvalue'` [download] if the value does not exist, the VALEXISTS column will be 0. depending on this value, u can decide whether to fire the INSERT statement or not :-)	[reply] [d/l]
Re: simple MySQL prob by Zaxo (Archbishop) on Mar 03, 2002 at 23:21 UTC
MySQL INSERT will fail if you try to form a new record with a duplicate of a unique field. It's poor form to rely on error conditions for program logic, so the two step process of jeffa and screamingeagle is best. If you instead wish to only replace NULL values in an existing record, `my $sth=$dbh->prepare('UPDATE db SET foo=IF(foo IS NULL,?,foo) WHERE k +ey=?');` [download] Note the distinction between INSERT and UPDATE, INSERT creates new records, UPDATE modifies existing ones. After Compline, Zaxo	[reply] [d/l]
Re: simple MySQL prob by gav^ (Curate) on Mar 03, 2002 at 23:34 UTC
You can MySQL do the checking for you: `$dbh->do('insert into foo values (x,y,z)'); if ($dbh->err && $dbh->err == 1062) { # we've got a duplicate }` [download] If you wanted to replace an existing record, look up the syntax for REPLACE where you can do something like: `$dbh->do('replace foo values (x,y,z)');` [download] Hope this helps... gav^	[reply] [d/l] [select]
Re: simple MySQL prob by webadept (Pilgrim) on Mar 04, 2002 at 02:37 UTC
Loading everything into a hash for a web site program isn't working, I mean you could bring down a server fast doing that kind of thing. Besides, with mulitple users you have the problem of A messing with a seperate value table than B and then duplicates being created when you shove the hash back into the database. Transactions are good if they are available and you want to deal with them. What I do is get session_id's working and a unique identifier, grabing an MD5 of the current time in seconds is normally pretty good. then build the table to keep user information seperate. ie `create table sample( sample_id int not null auto_incrment primary key, session_id char(45) not null, item_num int, next2field int, .. blab blab );` [download] To keep things fast I always place indexs on the session_id and primary key combination. This will speed things up enough that using a hash won't increase recall speed much faster. With multiple users getting at your CGI this takes out the possibility that user 1, is adding duplicates to user 2 and so on. Also with the primary key, there is not a chance of a real duplicate record getting in there. Of course all of this is based on the idea that you are only worried about a single user putting in duplicate data such as items in a shopping cart or account information. If you have global informtion that is not of the "single user" type, say dictionary information or bills paid with mulitple data entry, then you are back to transactions. Man, did I get all talkitive or what? geez.. maybe just having "anyone" put stuff in here isn't all that good of an idea :-) Hope this helps, webadept.net	[reply] [d/l]
Re: simple MySQL prob by thor (Priest) on Mar 04, 2002 at 01:47 UTC
I'm not familiar enough with ANSI SQL standards to know if this is possible in MySQL, but you could implement a trigger that fails if you try to insert a duplicate row. Merely checking to see if the row exists first, and then inserting it if it doesn't isn't good enough, because a race condition exists. Say that your program checks to see if the row exists and finds that it doesn't. Now, some other application inserts the row. Now, your application inserts it. Bad mojo. Better to use the trigger to check for exitance on insertion.	[reply]