Checking to see if value already exists in database

PHP
1

Hi,
I made a form to add my customers to an sql database, so to prevent me adding duplicates, I want to test to see if the postcode already exists in the db. I wrote the code below and I’ve been messing around with the order of things for a while, but I can’t seem to make it work, it just still adds the new record to the db even if the postcode already exists. Can someone show me where I’m going wrong?

Many thanks :slight_smile:


<?php
// Send form data to mysql
	
 if(isset($_POST['Postcode']))
	{
	$record = $_POST["Postcode"];
    $query = mysql_query("SELECT * FROM customers WHERE Postcode=='$record'");
	if(mysql_num_rows($query) == 0){
		$result = add_customer($_POST);
 if ($result === true )
	{	
	echo 'New reccord added to database';
				die();
			}
			}
		}	
else {
	if(isset($_POST['Postcode'])){
    $existing = existing_customer();
	echo'Record already exists!';
	}	
}
?>
2 
SELECT * FROM customers WHERE Postcode[B][COLOR="Red"]==[/COLOR][/B]'$record'

Change == into =

You could also add a unique index on Postcode. That way the insertion of dupplicate Postcode would cause an error.

3

that double equals sign isn’t valid mysql syntax

besides, you shouldn’t do a SELECT first before inserting a row – let mysql tell you on the INSERT if a duplicate has been rejected

see the thread http://www.sitepoint.com/forums/php-34/php-validation-vs-mysql-validation-766340.html

4

This is what you have:


$query = mysql_query("SELECT * FROM customers WHERE Postcode=='$record'");

This is what it should look like (only one equals sign)


$query = mysql_query("SELECT * FROM customers WHERE Postcode='$record'");

This is how you could have found the error yourself;


$sql = "SELECT * FROM customers WHERE Postcode=='$record'";
// echo $sql ; // uncomment this line to debug, remove it when not needed
$query = mysql_query( $sql );

By echoing that line onto a page somewhere, copying the output text, pasting it into your database you would have seen the error.

OR use mysql_error() to find errors straight away

OR go and find your mysql log files and look at the last sql statement entered into your database

ps not following the thinking of only permit one person per postcode, what if one neighbour tells another of your fantastic offering?

5

Thanks so much guys, that debug tip looks like something I’ll be using a lot in future!

Bloody good point! I’ll test the name aswell then :tup:

6

What you can do is to create a UNIQUE index on both postcode and name.

So then you can have only one “joe bloggs” in postcode “abc 123”, but you can have “joe bloggs” in “abc 124”.

[google]mysql unique index on multiple fields[/google]

7

Thanks so much guys, that debug tip looks like something I’ll be using a lot in future!

When you get bored of it, just have your mysql log file open all the time… I am sure you will have moved to prepared statements (PDO, Mysqli) before too long - in which case you will be looking at your logfile sooner that you think…

Echoing stuff onto the screen is a good way to force yourself to stop and develop incrementally, do something, check it, prove it, move on… that and using var_dump() on your variables - especially prior to conditional code forks.

8

Hmm ok that looks like a better solution. But can I do this on existing columns? I can’t see anywhere in phpma that allows me to update a column to make it unique.

I found this sql, but it didn’t work…
ALTER TABLE customers
ADD CONSTRAINT Postcode UNIQUE

Thanks for this, really helpful! :slight_smile:

9

MySQL :: MySQL 5.5 Reference Manual :: 12.1.11 CREATE INDEX Syntax