i have a html page in which i enter data which then submits and inserts in a database on a php page. how would i validate in php that the data received is not a duplicate of the data in the database?
any help appreciated.
A:
The easiest way is to have a hidden field ID holding the value of the primary key from your table. If empty this will automatically mean that the data is new and needs to be inserted. Otherwise it depends on your specific scenario, if you give more details I can try to give you more detailed response.
Ivo Sabev
2010-04-06 17:46:26
+1
A:
You will need do a query your database with your data and see if any results are returned. If they are, then you know it's a duplicate. For example:
$data = validate($_POST['data']);
$query = "select * from `tablename` where `fieldname` = $data limit 1;";
$result = mysql_query($query, $connection);
//now check that the number of rows is 0
if (mysql_num_rows($result) == 0 ) {
//insert the data
insert($data);
}
DKinzer
2010-04-06 17:48:22
there must be another line between validating and query building - data preparation for the query.
Col. Shrapnel
2010-04-06 18:25:54
obviously the validation function would do filtering etc. I just added validate() for the sake of brevity.
DKinzer
2010-04-06 18:45:48
but it shouldn't do any "filtering", whatever you mean. sql data preparation is distinct operation, and shouldn't be mixed with anything else. Because it's bound to sql query, not to data validation.
Col. Shrapnel
2010-04-06 18:59:04
thanks for that! how do i show the error message and redirect the page back? thanks!
fuz3d
2010-04-06 19:01:33
+1
A:
It depends on the situation. If your database has primary keys, unique constraints, et al, then the database simply won't insert the data and you can try to catch any errors. Otherwise you have to query the data based on the user's info and compare their new info to their old info.
If you're simply trying to avoid a double-submit problem from a form then do a Redirect after POST
rkulla
2010-04-06 17:48:33
+3
A:
Let's say you want to check if entered username already exists. You can do it with usual SELECT query:
$name = mysql_real_escape_string($_POST['name']);
$sql = "SELECT * FROM table WHERE name = '$name'";
$res = mysql_query($query, $connection);
if (mysql_num_rows($res) > 0 ) $error = "User already exists.";
It would be also a good idea to have your form in the php file, not html one. So, you'll be able to show it back with all fields filled with entered data - great usability improvement
Col. Shrapnel
2010-04-06 18:30:37
thanks for that! after this code, i have the insert code. do i assume that the error message will exit the page without running through all the code? also, how do i redirect it to another page?
fuz3d
2010-04-06 19:07:51
you should do "select 1 from..." instead of "select * from..." since you really just want to know if a row exists and not retrieve the values from it.
Samuel
2010-04-11 19:38:10
A:
Here is the schema of page with a form and it's handler:
<?
if ($_SERVER['REQUEST_METHOD']=='POST') {
//validation part
$err='';
if (!$name) $err.="You must enter name<br>";
$name = mysql_real_escape_string($_POST['name']);
$sql = "SELECT * FROM table WHERE name = '$name'";
$res = mysql_query($query, $connection);
if (mysql_num_rows($res) > 0 ) $err. = "User already exists.";
// etc
// check if there was no errors:
if (!$err) {
//save to the database
mysql-query(...);
//and redirect
Header("Location: ".$_SERVER['PHP_SELF']);
exit;
} else {
foreach($_POST as $key => $value) $_POST[$key] = htmlspecialchars($value);
}
} else {
$_POST['name'] = $_POST['email'] = $_POST['notes'] ='';
}
//here goes the form, with error message, if any.
?>
<html>
<head></head>
<body>
<? if ($err) echo '<font color=red><b>'.$err.'</b></font>'; ?>
<form action="<? echo $_SERVER['PHP_SELF'] ?>" method="POST"><br>
Name: <input type="text" name="name" value="<? echo $_POST['name'] ?>"><br>
Email: <input type="text" name="email" value="<? echo $_POST['email'] ?>"><br>
Notes: <textarea rows="3" cols="30" name="notes"><? echo $_POST['notes'] ?></textarea><br>
<input type="submit" name="submit">
</form>
</body>
</html>
Col. Shrapnel
2010-04-06 19:37:05