Welcome to WebmasterWorld Guest from 34.204.173.36

Forum Moderators: open

Message Too Old, No Replies

Any way to do this in one query instead of 2?

     
7:32 pm on Oct 13, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120


I'm doing this in Perl, using the DBI module.

I have pm_data set up with 3 columns: an AI "refid" (short for "reference_id"), a CHAR(32) for the "hash", and then a TEXT for the "message". The hash column is set to UNIQUE.

So what I'm doing is inserting to pm_data first, and ignoring duplicates. Then I do a second query to get the refid:

my $sth_data_new = $dbh->prepare("INSERT IGNORE INTO pm_data (hash, message) VALUES (MD5(?), ?)");
$sth_data_new->execute($comment, $comment);

my $sth_select_data_new = $dbh->prepare("SELECT refid FROM pm_data WHERE hash = MD5(?) LIMIT 1");
$sth_select_data_new->execute($comment);
$refid = $sth->fetchrow_array();


Do you guys know of a better way to get the refid without doing the second query?

I realize that I could also do this:

my $sth_data = $dbh->prepare("INSERT IGNORE INTO pm_data (hash, message) VALUES (MD5(?), ?)");
# if inserted, $num_rows = 1; else it will = 0 or -1
$num_rows = $sth_data->execute($contents{'comment'}, $contents{'comment'});

if ($num_rows == 1) {
# should I be using $dbh->last_insert_id() instead?
$refid = $dbh->{'mysql_insertid'};
}

else {
my $sth_select_new = $dbh->prepare("SELECT refid FROM pm_data WHERE hash = MD5(?) LIMIT 1");
$sth_select_new->execute($comment);
$refid = $sth->fetchrow_array();
}


That would prevent a second query if the row is inserted, but I'm not sure how $dbh->{'mysql_insertid'} works, or if it uses less memory than the select query.