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.