Page 1 of 1

Duplication Advice : Records into MySQL

Posted: Wed Sep 28, 2005 6:14 am
by facets
Hi Gang,

How would I go about checking to see if an exact 'product name' exists in my DB before writing the row?
Is that the DISTINCT SQL function?

TIA Will.

Posted: Wed Sep 28, 2005 6:15 am
by Jenk

Code: Select all

SELECT DISTINCT column FROM table
:)

Posted: Wed Sep 28, 2005 6:34 am
by facets
Sorry for the waste of space!
It's a MySQL alter table function

ALTER TABLE `tableName` ADD UNIQUE (
`columnName`
)

Now, how do I catch the error nicely :)

Posted: Wed Sep 28, 2005 6:42 am
by Jenk
To catch Mysql errors in PHP, use the mysql_error() function :)

Code: Select all

<?php

mysql_query("ALTER TABLE tableName ADD UNIQUE (columnName)") or die(mysql_error());

?>
:)

That's of course the most simplistic and not the most secure method of error handling, but gives you the idea and is ideal for development :)

Posted: Wed Sep 28, 2005 6:52 am
by facets
I'll re-word my question.
How would I catch the error that comes back to browser that sights :

Duplicate entry 'Wills Test' for key 2

Perhaps a javascript alert with the page going back to edit the Name ?
Any idea on how I could do that?

Posted: Wed Sep 28, 2005 6:55 am
by Jenk
facets wrote:I'll re-word my question.
How would I catch the error that comes back to browser that sights :

Duplicate entry 'Wills Test' for key 2

Perhaps a javascript alert with the page going back to edit the Name ?
Any idea on how I could do that?
The snippet I posted above will display the error :)

Posted: Wed Sep 28, 2005 7:01 am
by Weirdan

Code: Select all

$stmt = "insert into tablename values('" . mysql_real_escape_string($_POST['something']) . "')";
if(!mysql_query($stmt)) {
   if( preg_match("/duplicate entry/i", mysql_error()) ) {
        // there was attempt to insert duplicate record, do something (redirect, etc)
   }
} else {
    // entry inserted successfully
}

Posted: Wed Sep 28, 2005 7:06 am
by facets
I think we may be crossing paths.
I've altered the MySQL column parameters ok (ALTER - ADD UNIQUE etc)
Now i'm adding data into that table/column with the INSERT below and the error displays on the browser :

Duplicate entry 'Wills Test' for key 2

So how would I add a link to 'back' or use an js alert and once OK is clicked go back to the form.

Code: Select all

$sql_add = "INSERT INTO ausapapersummary values ('','$paperCategoryId','$colloPaperName','$manufacturerName','$cpl','$stockId','$adhesiveId','$linerId','$supplierId','$availability', '$features', '$limitations','$productExamples','$suitabilityFoil','$suitabilityYellowLight','$suitabilityLabel','$suitabilityOpacity','$suitabilityBronze','$suitabilityScreen','$suitabilityIceBucket',now(),'')";

from this :
mysql_query($sql_add) or die(mysql_error()); 

to this :

mysql_query("ALTER TABLE ausapapersummary ADD UNIQUE (colloPaperName)") or die(mysql_error());
thanks for the assitance Jenk :)