Display error message when user tries to add duplicate record into db

[sharapov]

I am letting users add some information into MySQL database via forms. I have set one of the fields in the database as"unique" to prevent duplicate records. What I want to do is to display message to the user when he/she tries to add the same record twice(i.e submit ethe same form with the same info twice. Usuallu happens when user refreshes the browser). Below is the form that I use to submit info to database. I know that I have to do some checking before all this info is actually inserted into database, but I can't figure how. I tried to do the following without sucsess, can anybody help?

$check = "SELECT COUNT(pos_title) FROM $tablename WHERE pos_title='$_POST[array]')";
$check_result = mysql_query($check);

if(mysql_num_rows($check_result)>0)
{
die("YOU have submited it already");
}

echo"<form action='edit.php' name='add' method='post'>\n";
echo"<input type=hidden name='tablename' value='$tablename' />\n";
echo"<input type=hidden name='dbname' value='$dbname' />\n";
echo"<table>";
echo"<tr><td colspan=2><center><b>NEW POSITION</b></center><br></td></td>";
echo"<tr><td class=td>Position Title:</td>";
echo"<td class=td><input type=\"text\" name='array[1]' size=\"32\"></td>";
echo"<tr><td class=td>Position Description:</td>";
echo"<td class=td><textarea rows=\"6\" name='array[2]' cols=\"34\"></textarea></td>";
echo"<tr><td colspan=\"2\">";
echo"<button title='New Position' class=buttons type=submit name='addrec'/> Create New </button>\n";
echo"<button onclick=\"history.back();\">Cancel</button></td>";
echo"</table></form>";

 

[sleipnir214]

Output to the browser the SELECT query you're constructing in your code.

You'll see that the problem is that $_POST['array'] is, well, an array. You have to pass MySQL a string.

If one particular element of $_POST['array'] is the value for which you are checking in MySQL, pass MySQL just that one element in your query.

If you need MySQL to check every element of $_POST['array'], you're going to have to construct a query which reads as "...WHERE post_title = $_POST['array'][0] or post_title = $_POST['array'][1]...." or you're going to have to loop through $_POST['array'], constructing multiple queries and sending them to MySQL separately.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sharapov]

sleipnir214 ,

Thank you for your responce. I rewrote my statement a little bit (see below) but it doesn't seem to be working. I shows there is 0 records when you run it, where there should be at least one. CAn you help? Thanks.

$query = "SELECT pos_title FROM $tablename WHERE pos_title = '$_POST[array][2]'";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);

echo $num_results; //show on the screen

if(($num_results)>0)
{
die("YOU have submited it already");
}

 

[sleipnir214]

The first step to debugging database problems is to output the query and examine it.

Does the query look right?
If you copy the output query to your preferred database tool, does it return what you expect?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sharapov]

sleipnir214,

I checked the query and it looks fine. (there is no errors) but when I run it inside the code I get "0" as a result when user tries to submit exactly the same information to the database (look at my code above). May be there is an error in my logic, not the code itself?

 

[sleipnir214]

What happens if you copy-and-paste that query into a MySQL admin tool?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sharapov]

It says: Your SQL-query has been executed successfully (Query took 0.0003 sec)

 

[sleipnir214]

That's a SELECT query -- what is returned?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sharapov]

Well you can't really check that in the MySQL admin tool because you are not submitting anything with the "POST,"
but if yo run it normally and show input on the screen, it shows "0" records, where there should be 1.

 

[sleipnir214]

If you copy the query output by your script into your MySQL admin tool, you can run the query your script would run.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sharapov]

That is true, but my script sends some information from the form. That information is storeed in the $_POST variable. and I can't simulate that when I copy and paste query into admin tool.

 

[DRJ478]

Tip:

PHP really doesn't like to work with multi-dimensional arrays within a double-quoted context. For clarity and to avoid any mishaps it should not be:

$query = "SELECT pos_title FROM $tablename WHERE pos_title = '$_POST[array][2]'"; 
# better:
$query = "SELECT pos_title FROM $tablename WHERE pos_title = '".$_POST['array'][2]."'";

Also, the naming of a posted variable to array seems to me asks for trouble since array is a reserved word in PHP. It is always recommended to stay away from reserved words.

Also, follow sleipnir214's advice and print the SQL statement out. You need also to show us how you ascertain the return value.

 

[sharapov]

Thank you guys for your help. I figured out where my mistake was.

 

[sleipnir214]

Okay. Now dish.

Someone else may be able to benefit from your experience.

Want the best answers? Ask the best questions!

TANSTAAFL!!