Checking for duplicates

saeed42

I would like to check that the "CompName" doesn't have any duplicates, and as I understand it I need to query the db and filter it with the Compname that is been entered into the form and if the result is not 0 then return and error message stating that the comp name is in db and user should select different name, so my question is how do I do this as I'm using dreamweaver MX and my php knowledge is "very" limited.

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $HTTP_SERVER_VARS['PHP_SELF'];
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
  $editFormAction .= "?" . $HTTP_SERVER_VARS['QUERY_STRING'];
}

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO compname (CompName) VALUES (%s)",
                       GetSQLValueString($HTTP_POST_VARS['CompName'], "text"));

  mysql_select_db($database_password_entry, $password_entry);
  $Result1 = mysql_query($insertSQL, $password_entry) or die(mysql_error());
}
?>

jkohns

while querying on the prospect compName might work, another method would be to set up the compname table with the field compname as UNIQUE, then use a query such as:

if (!$query = mysql_query("INSERT INTO compname (CompName) VALUES ('$compName')")){

print 'DUPLICATE ENTRY'; // whatever kind of error message you prefer

}

though, here, any other errors (though I can't think of any that could occur while only inserting the single item into a single field) would result in the same 'DUPLICATE ENTRY' error message...but I prefer using this method, simply because it only uses the single query.

saeed42

I did what you suggested and made the field compname as UNIQUE, What I would like to happen is the form to submit and come back with the last ID which is passed to the next form where more records need to be place into another table, and I have no problem achieving this task, but what happens when there's a duplicate? I don't want to see any error messages I just want the script to ignore that error and filter the database with name and come back with ID so it could be passed to the following form so process can continue

Thanks in advance

jkohns

I'm afraid I'm not quite following you- in the case that your prosepctive entry into compname is a duplicate, the query fails, and there is no insert ID to retrieve. If you have more information to enter in to the database, and you don't care if it is associated with a unique ID, then simply follow the process through, ignoring the duplicate error...but it seems like it would be a good idea to first get the compname field, which must be unique, down first, before trying to add any other information.

saeed42

Thanks for responding so promptly.

The process consists of three forms and their corresponding tables and the first one needs only the "CompName", this's where I want to pass the last ID to the next form in hidden field, this I can do, but things get complicated if there are duplicates, if duplicate exists I don't want the script to return an error but I want it to return the ID of the duplicate that exists in the database so that can be passed to form two where ID is a "foreign key"

jkohns

In that case, you should not use the unique method on the compname field at all- you would only want to do that where you do not ALLOW duplicate entries.

saeed42

I'm miserably failing to explain myself correctly, so please bear with me as I have one more go at it.

I don't want duplicates in that table at all, but what I want is after the script tries to enter the new record and then gets the duplicate error msg, I would rather have the script to do a query with that name "$CompName" so it can grab its ID and pass it to the next form, how ever if the script doesn't encounter any error messages I want it to get on with it and pass the new ID to next page.

Example ( in theory this is what should happen)


Insert new record

If Record exists select * from table where CompName = "$CompName"

else 

Insert data and get last ID

jkohns

Ahhh, now I get it.....(sorry)

then use the unique method...only where you attemp the first insert query, use something like this:

if (!$query = mysql_query("INSERT INTO compname (CompName) VALUES ('$compName')")){

if ($existingID = mysql_query("SELECT id FROM compname WHERE Compname='$compName'")){

  $id = mysql_result($existingID, 0, 0);

}else{

   print 'ERROR!';

}

}else{

$id = mysql_insert_id($query);

}

now, you have a case where if the $compName is new to the DB, it will be inserted, and you will have the var $id to pass to further forms...
otherwise, the second query tries to select the id from the database for $compName, and if that suceeds, it assigns the same $id variable the existing entry's value...if it fails, well, then you get ERROR! (or whatever)

hope this helps

saeed42

Thanks, I'll try this and let you know how things turnout