9 Replies - 32845 Views - Last Post: 14 June 2009 - 09:20 AM Rate Topic: -----

#1 epayton  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 05-December 07

Prevent Duplicate MySQL records

Posted 05 December 2007 - 08:29 AM

Hi, as part of an excel bidding sheet, I have a VB script that sends customer information to a PHP processing script which then posts the customer name and email address to a MySQL db online. My question: What is a line that I could add to my code that would prevent the new row from being added if the email address being submitted already exists in the table? My PHP script is shown below:

<?php

//Database info
$username="morroba1_limesur";
$password="********";
$database="morroba1_limesurvey";
$dblocation="localhost";
$tablename="lime_tokens_51256";

//grab the variables from the query
$Getfirstname=$HTTP_GET_VARS["GetFirstname"];
$Getlastname=$HTTP_GET_VARS["GetLastname"];
$Getemail=$HTTP_GET_VARS["GetEmail"];

//connect and select DB
mysql_connect($dblocation,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO $tablename VALUES ('','$GetFirstname','$GetLastname','$GetEmail','','en','','','','','')";

mysql_query($query) or die ("Error writing to database");

mysql_close();

?>



Is This A Good Question/Topic? 0
  • +

Replies To: Prevent Duplicate MySQL records

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Prevent Duplicate MySQL records

Posted 05 December 2007 - 08:32 AM

Easiest way is to make the email address a primary key field in the database. This will prevent duplicate records. You'll want to catch the error returned from mySQL though and say something nice like "Account already exists" instead of "primary key error" or whatever mySQL returns these days.
Was This Post Helpful? 0
  • +
  • -

#3 epayton  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 05-December 07

Re: Prevent Duplicate MySQL records

Posted 05 December 2007 - 08:43 AM

Thanks for the quick reply, but I know next to nothing about MySQL, I was hoping for an IF THEN I could just add to the PHP file
Was This Post Helpful? 0
  • +
  • -

#4 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Prevent Duplicate MySQL records

Posted 05 December 2007 - 09:15 AM

Well, if you have access to PHPMyAdmin or similar, it's just a matter of editing the table and selecting "primary key" on the email field.

If you want to do it in PHP though, you're going to have to run a SELECT email FROM tablename WHERE email = '$Getemail' and run that query. Then do if ($mysql_num_rows($sql_result)) { ERROR} else { run your INSERT }

That's the pseudo code and will check if any results were found with that email address.
Was This Post Helpful? 1
  • +
  • -

#5 floutag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 11-June 09

Re: Prevent Duplicate MySQL records

Posted 11 June 2009 - 01:47 PM

Hello,
i just would like to ask how to catch an error whitch occurs when someone tries to add duplicate to the primery key. I have simple application in php for getting e-mail address for newsletter. If someone try to use the same e-maill address twice - mysql error shows (just line off text). I would like to create a link to webpage saying that e-mail adress is already in the database instead. I have really no idea how can i do that. Thank you for your help....

View Postskyhawk133, on 5 Dec, 2007 - 08:15 AM, said:

Well, if you have access to PHPMyAdmin or similar, it's just a matter of editing the table and selecting "primary key" on the email field.

If you want to do it in PHP though, you're going to have to run a SELECT email FROM tablename WHERE email = '$Getemail' and run that query. Then do if ($mysql_num_rows($sql_result)) { ERROR} else { run your INSERT }

That's the pseudo code and will check if any results were found with that email address.

This post has been edited by floutag: 11 June 2009 - 01:49 PM

Was This Post Helpful? 0
  • +
  • -

#6 RPGonzo  Icon User is offline

  • // Note to self: hmphh .... I forgot
  • member icon

Reputation: 151
  • View blog
  • Posts: 954
  • Joined: 16-March 09

Re: Prevent Duplicate MySQL records

Posted 11 June 2009 - 02:09 PM

Your best best would be skyhawks php example.. in your submission form

<?php
//Database info
$username="morroba1_limesur";
$password="********";
$database="morroba1_limesurvey";
$dblocation="localhost";
$tablename="lime_tokens_51256";

//grab the variables from the query
$Getfirstname=$HTTP_GET_VARS["GetFirstname"];
$Getlastname=$HTTP_GET_VARS["GetLastname"];
$Getemail=$HTTP_GET_VARS["GetEmail"];

//connect and select DB
mysql_connect($dblocation,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT COUNT(*) AS count FROM $tablename WHERE useremail='$Getemail'";
$results = mysql_query($query) or die ("Error reading from database");
$existingEmails = mysql_fetch_array($results);

if ($existingEmails['count'] > 0) {
	echo "I'm sorry our database already contains that email please use a new email address to continue.";
} else {
	$query = "INSERT INTO $tablename VALUES ('','$GetFirstname','$GetLastname','$GetEmail','','en','','','','','')";
	mysql_query($query) or die ("Error writing to database");
}

mysql_close();
?>



thats tailored to your code

and this line
$query = "INSERT INTO $tablename VALUES ('','$GetFirstname','$GetLastname','$GetEmail','','en','','','','','')";



could be GREATLY reduced just using column names
$query = "INSERT INTO $tablename (firstname,lastname,useremail,language) VALUES('$GetFirstname','$GetLastname','$GetEmail','en')";



just looks cleaner and easier to read

This post has been edited by RPGonzo: 11 June 2009 - 02:26 PM

Was This Post Helpful? 1

#7 floutag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 11-June 09

Re: Prevent Duplicate MySQL records

Posted 12 June 2009 - 12:00 AM

Thank you for your reply. I understand on the example, but the problem is that I'm using Dreamweaver CS4 to write the codes for me and it seems much more complicated to me:(. I can't find where to put the codes to catch the error. Here is the code generated by dreamweaver:

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
	$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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 = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "newsletter_form")) {
  $insertSQL = sprintf("INSERT INTO subscriptions (subEmail, subSubscriptions) VALUES (%s, %s)",
					   GetSQLValueString($_POST['newsEmail'], "text"),
					   GetSQLValueString($_POST['subSubscriptions'], "int"));

  mysql_select_db($database_connSubscribe, $connSubscribe);
  $Result1 = mysql_query($insertSQL, $connSubscribe) or die(mysql_error());

  $insertGoTo = "subscribe_confirm.php";
  if (isset($_SERVER['QUERY_STRING'])) {
	$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
	$insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>



Thank you very much!!!

This post has been edited by floutag: 12 June 2009 - 12:01 AM

Was This Post Helpful? 0
  • +
  • -

#8 RPGonzo  Icon User is offline

  • // Note to self: hmphh .... I forgot
  • member icon

Reputation: 151
  • View blog
  • Posts: 954
  • Joined: 16-March 09

Re: Prevent Duplicate MySQL records

Posted 12 June 2009 - 07:01 AM

I'm not entirely sure about this but you can try

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "newsletter_form")) {
  $insertSQL = sprintf("INSERT INTO subscriptions (subEmail, subSubscriptions) VALUES (%s, %s)",
					   GetSQLValueString($_POST['newsEmail'], "text"),
					   GetSQLValueString($_POST['subSubscriptions'], "int"));

  mysql_select_db($database_connSubscribe, $connSubscribe);
  $Result1 = mysql_query($insertSQL, $connSubscribe) or die(mysql_error());

  $insertGoTo = "subscribe_confirm.php";
  if (isset($_SERVER['QUERY_STRING'])) {
	$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
	$insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}



To this:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "newsletter_form")) {
  mysql_select_db($database_connSubscribe, $connSubscribe);
  
  $checkSQL = sprintf("SELECT COUNT(*) AS count FROM subscriptions WHERE subEmail='%s'",
					   GetSQLValueString($_POST['newsEmail'], "text"));
  $Results = mysql_query($checkSQL, $connSubscribe) or die(mysql_error());
  $existingEmails = mysql_fetch_array($Results);
  
  if ($existingEmails['count'] > 0) {
		// do something here so the user gets alerted it didnt work
		return false;
  }
  
  $insertSQL = sprintf("INSERT INTO subscriptions (subEmail, subSubscriptions) VALUES (%s, %s)",
					   GetSQLValueString($_POST['newsEmail'], "text"),
					   GetSQLValueString($_POST['subSubscriptions'], "int"));
  $Result1 = mysql_query($insertSQL, $connSubscribe) or die(mysql_error());

  $insertGoTo = "subscribe_confirm.php";
  if (isset($_SERVER['QUERY_STRING'])) {
	$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
	$insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}



If you have sessions started a easy of alerting the user is to set a $_SESSION var with some info about being denied, than on your form page use a if (isset($_SESSION['emailFailure'])) { // do something; unset($_SESSION['emailFailure']); }

This post has been edited by RPGonzo: 12 June 2009 - 07:03 AM

Was This Post Helpful? 0
  • +
  • -

#9 floutag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 11-June 09

Re: Prevent Duplicate MySQL records

Posted 12 June 2009 - 11:06 AM

I'll try it and let you know if it worked. I really appreciate your help! Thank you...
Was This Post Helpful? 0
  • +
  • -

#10 floutag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 11-June 09

Re: Prevent Duplicate MySQL records

Posted 14 June 2009 - 09:20 AM

I have no idea why, but it doen't work. I've tried almost everything. MySQL gives me error: (unusuall/strange/wierd) syntax (near/next to) "something@something.com(value from the form)"' on line 1 (this is translation from czech language, I'm running this script on czech server). It gives me this error everytime, even though there is no entry in the database or there is different mail address entered. Code seems alright to me, but something must be wrong. Error message is quite useless too. Help please!!!

For now, I'm going to use following code, which redirect to error page whenever ANY error occurs. I set up subEmail column in my database to unique, so it gives error when someone tries to insert the same e-mail address. But this solution is too general and I'd still prefer to catch only this error....
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "newsletter_form")) {
  $insertSQL = sprintf("INSERT INTO subscriptions (subEmail, subSubscriptions) VALUES (%s, %s)",
					   GetSQLValueString($_POST['newsEmail'], "text"),
					   GetSQLValueString($_POST['subSubscriptions'], "int"));

  mysql_select_db($database_connSubscribe, $connSubscribe);
  $Result1 = mysql_query($insertSQL, $connSubscribe) or die(header(sprintf("Location: subscribe_error.php")));

  $insertGoTo = "subscribe_confirm.php";
  if (isset($_SERVER['QUERY_STRING'])) {
	$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
	$insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

This post has been edited by floutag: 14 June 2009 - 09:29 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1