Check if value exists before inserting into MySQL DB in a PHP script
Create a UNIQUE key on the fields you care about, and detect the integrity error after the fact.
How do I check if name exists in table before inserting into MySQL
You can simplify your code a bit - the last select-statement is redundant, as you already have the information before the query. You should also be using a prepared statement instead of using real_escape_string()
.
<?php
$stmt = $conn->prepare("SELECT name FROM areas WHERE idCampus = ?");
$stmt->bind_param("s", $_POST["campus_area_fk"]);
$stmt->execute();
$exists = $stmt->fetch();
$stmt->close();
if ($exists)
echo "Area name already exists";
} else {
$stmt = $conn->prepare("INSERT INTO areas (name, idCampus) VALUES (?, ?)");
$stmt->bind_param("ss", $_POST["area_name"], $_POST["campus_area_fk"]);
$stmt->execute();
echo json_encode(['area_name_retrieve' => $_POST["area_name"], 'area_id_retrieve' => $stmt->insert_id]);
$stmt->close();
}
Beware that this can cause race-conditions - you should instead put the name as a unique constraint, and try to insert it without doing any select query first. Then check against the errorcode you got back to see if it existed or not.
Check if row exists in the database before inserting
It's better to set a constraint on your columns to prevent duplicate data instead of checking and inserting.
Just set a UNIQUE constraint on imdbid
:
ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);
The reason for doing this is so that you don't run into a race condition.
There's a small window between finishing the check, and actually inserting the data, and in that small window, data could be inserted that will conflict with the to-be-inserted data.
Solution? Use constraints and check $DBH->error()
for insertion errors. If there are any errors, you know that there's a duplicate and you can notify your user then.
I noticed that you are using this, $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
. In this case, you don't need to check ->error()
because PDO will throw an exception. Just wrap your execute with try and catch like this:
$duplicate = false;
try {
$STH->execute();
} catch (Exception $e) {
echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
$duplicate = true;
}
if (!$duplicate)
echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";
php mysql issue with check if record exist before insert
Your $_POST['staff_number']
is actually an array
.
So you have to access it like $_POST['staff_number'][0]
here, 0
is a index number.
PHP/MySQL Check If Value Exists, If So-Do Not Insert It
Check out MySQL INSERT ... ON DUPLICATE KEY UPDATE, which you can use if you set the URL as unique in your database.
Also, you should make sure to sanitize your inputs before inserting them: http://php.net/manual/en/function.mysql-real-escape-string.php
Checking records in MySQL before inserting into database
You will need to run a query before you insert your data in the database for checking the reservation.
Something like this perhaps
// This function helps you escape the data before you use them in database
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
// prep you data properly. You can use the GetSQLValueString() function to
// escape the inputs, just set it to the required type.
// if some $_POST value is not set, then you can set a default one here
$res_location = isset($_POST['res_location']) ? GetSQLValueString($_POST['res_location'], 'text') : ' set a defaule value here';
$res_classroom = isset($_POST['res_classroom']) ? GetSQLValueString($_POST['res_classroom'], 'text') : ' set a defaule value here';
$res_inclusive_date = isset($_POST['res_inclusive_date']) ? GetSQLValueString($_POST['res_location'], 'date') : ' set a defaule value here';
$res_inclusive_time_start = isset($_POST['res_inclusive_time_start']) ? GetSQLValueString($_POST['res_inclusive_time_start'], 'text') : ' set a defaule value here';
// Build the query
$query = "SELECT * FROM `tbl_reservation` WHERE `Location_Faculty` = '{$res_location}' AND `Classroom` = '{$res_classroom}' AND `Inclusive_Date` = '{$res_inclusive_date}' AND `Inclusive_Time` = '{$res_inclusive_time_start}' ";
$result = mysql_query($query) or die(mysql_error() . '<hr />' . $query);
if(mysql_num_rows($result) > 0){
// then the record already exists
echo "Duplicate entry";
} else {
// save to database
}
Please note that its valnuerable to sql injections, so you have to escape your inputs and also try to use mysqli or pdo instead of old mysql functions
Related Topics
How to Convert PHP Regex to JavaScript Regex
Storing Time Information: Timezone Required
Laravel - Decryptexception: 'The MAC Is Invalid'
Simplexml Soap Response Namespace Issues
Why Do I Get "Resource Id #4" When I Apply Print_R() to an Array in PHP
Best Practice for Error Handling Using Pdo
What Does Bind_Param Accomplish
Checking If 2 Arrays Have at Least 1 Equal Value
Supplied Key Param Cannot Be Coerced into a Private Key with Google APIs
Could Not Connect to Smtp Host
Problems with German Umlauts in PHP JSON_Encode
How to Create Clean Url Using .Htaccess
PHP Timezone Database Is Corrupt Error
Php's Preg_Replace Regex That Matches Multiple Lines
When and Where Should I Use Session_Start
How to Protect My Source Code When Deployed