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
<?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!';
}
}
?>
$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?
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.
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