Checking records in MySQL before inserting into database

Question

I'm creating a reservation system in which the client will fill up a reservation form (Location, Classroom, Time, & Date).

My question is about checking the records typed before inserting it in database. Meaning if the LOCATION, CLASSROOM, TIME, and DATE were already inserted/taken/reserved in the database then, the system will prompt a message like "The Location, Date and Time were reserved already", else it will be inserted in the database. I run this code but still it records the same location, classroom, date, time. Is there something wrong with this code?

$res_location = isset($_POST['res_location']) ;
$res_classroom = isset($_POST['res_classroom']) ;
$res_inclusive_date = isset($_POST['res_inclusive_date']);
$res_inclusive_time_start = isset($_POST['res_inclusive_time_start']) ;

// Build the query
$query = sprintf("SELECT Location_Faculty FROM tbl_reservation WHERE Location_Faculty=%s AND Classroom=%s AND Inclusive_Date=%s AND Inclusive_Time=%s ",
 GetSQLValueString($res_location, "text"),
 GetSQLValueString($res_classroom, "text"),
 GetSQLValueString($res_inclusive_date, "date"),
 GetSQLValueString($res_inclusive_time_start, "date"));

$result = mysql_query($query) or die(mysql_error() . '<hr />' . $query);
$num_rows = mysql_num_rows($result);
if( $num_rows >= 1){
   // then the record already exists
echo "Duplicate entry";
} 
else{
//insert query
}

It's SQL Injection free because of the "GetSQLValueString" function.

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

You can check the number of inserted row:

if(mysql_num_rows($Result1) > 0){
   //row is inserted
}

or you can run a SELECT query before inserting, and check if a row is returned.

Another option is to update the record if it is already inserted.

edited May 23 '17 at 12:06

Community

1
1

answered Jan 25 '15 at 13:13

meda

43,711
13
85
120

@Ahmed, since you have 20K rep, you _must_ know it is OK for people to change their mind about which answer they found most helpful. – halfer Jan 26 '15 at 16:15

ltdev · Accepted Answer · 2015-01-26T16:21:07.313

1

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

edited Jan 26 '15 at 16:21

answered Jan 25 '15 at 13:16

ltdev

3,099
14
52
105

Thanks! I'll give it a try :) – Nikkoz009 Jan 25 '15 at 13:20
Just a small tip. You can put your query inside a function , eg reservation_exist() where you can pass the required parameters and return either TRUE or FALSE if the record exist. Then in the if statement you can check against what your function will return and to the appropriate actions – ltdev Jan 25 '15 at 13:23
hey! i have a problem _Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Thesis\reservation_page.php on line 39_ and i cant insert now @Lykos. – Nikkoz009 Jan 25 '15 at 15:46
I suspect that there must be an error on your query, perhaps a syntax error, that's why it return false. Try this: $result = mysql_query($query) or die(mysql_error() . '
' . $query); and see what it returns – ltdev Jan 26 '15 at 09:10
`$query = "SELECT * FROM tbl_reservation WHERE Location_Faculty = " . isset($_POST['res_location']) ." AND Inclusive_Date = " . isset($_POST['res_inclusive_date']) ." AND Inclusive_Time = ". isset($_POST['res_inclusive_time_start']) ." AND Classroom = ". isset($_POST['res_classroom'])." LIMIT 1"; mysql_select_db($database_myConnection, $myConnection); $result = mysql_query($query, $myConnection) or die(mysql_error()); if(mysql_num_rows($result) == 1) echo 'Duplicate Reservation'; else{ }` that's my query can u check it? thanks :) i really it for my thesis :) – Nikkoz009 Jan 26 '15 at 15:13
First of all ... you need to put your table name and its fields inside ``, i.e FROM `tbl_reservation` WHERE `Location_Faculty` etc etc... – ltdev Jan 26 '15 at 15:33
2nd.. Dont do this: isset($_POST['res_location']) in the query ... perhaps the reason why your query returns false is that one of your values is not set (properly).. Try to use this way instead right before your query: $res_location = (isset($_POST['res_location'])) ? mysql_real_escape_string ($_POST['res_location']) : 'set a default value here' ; and then pass each variable (e.g $res_location) in your query. If you variable is string or text, you need to wrap it in quotes in your query, like this: WHERE `Location_Faculty` = ' " . isset($_POST['res_location']) ." ' AND etc etc. hope that helps – ltdev Jan 26 '15 at 15:37
hi thanks for the help can you check my query if it's correct? `$res_location = (isset($_POST['res_location']));` `$res_classroom = (isset($_POST['res_classroom']));` `$res_inclusive_date = (isset($_POST['res_inclusive_date']));` `$res_inclusive_time_start = (isset($_POST['res_inclusive_time_start']));` `$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' ";` – Nikkoz009 Jan 26 '15 at 15:58
Please review my answer above, I did an update. It will show you how you could do it – ltdev Jan 26 '15 at 16:14
hi this is prompting after submitting _You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Audio Visual Hall'' AND Classroom = NULL AND Inclusive_Date = ''2015-01-27'' AND' at line 1_ Here's 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}' ";` I'm really really sorry if im taking up youre time. I'm just new to php. – Nikkoz009 Jan 26 '15 at 16:48
@Nikkoz: it is generally thought here that large swathes of code don't render very well in comments, so we try to keep it to a minimum. Some is OK, but the above is hard to follow. On a related note, since this poster has already helped you, it may be that subsequent assistance should be sought in a new question, to avoid asking the same person for help repeatedly. Whether something is essentially the same problem or a new and separate one is a matter of judgement, of course. – halfer Jan 26 '15 at 17:02
@halfer hi! I would like to apologize on my doings, like flooding some comments. I did this because, I'm a newly registered member and I'm new to php language with myslq. Second, Of course, at this moment I learnt some brand new ideas with regards to php and mysql because of Lykos. Above these, I would like to thank you for opening up this to me to explore some resources here that can help me with my queries. Apologies to Lykos if I took some of your time to help me but still, I'm thankful to help me in this matter. Thanks and Goodluck :) – Nikkoz009 Jan 26 '15 at 17:17
@Nikkoz: no apology necessary - just letting you know some of the sort-of-guidelines we have around here `:-)`. – halfer Jan 26 '15 at 17:19
@halfer , Thanks and that will be noted! – Nikkoz009 Jan 26 '15 at 17:21
@Nikkoz009 also if your new to PHP you should not use `mysql_` it is deprecated look up `PDO` or `mysqli_` and learn about prepared statement. that's why your code is so messy – meda Jan 26 '15 at 17:26
@Nikkoz009: No need to appologize, glad I help! :-) Just some additional tips: 1st. if you are new to php, use the php manual and this site http://www.w3schools.com/. In fact bookmark them, you will reference them quite often. 2nd if you get an error like these ones before, copy them and paste them on google. Chances are that someone else had the same problem with you, so you can easily adapt the solution to your problem and fix it very easily. 3rd ALWAYS - ALWAYS escape data before inserting into database and filter them before outputting them. Google for sql-injection & xss-attacks. – ltdev Jan 26 '15 at 17:36
4th try to avoid using the mysql, use mysqli or pdo instead. And finally if you still got a problem which you still can't solve it use StackOverflow ;-) we 're glad to help! – ltdev Jan 26 '15 at 17:38

Checking records in MySQL before inserting into database

2 Answers2