Hi Folks, is there a way to validate input fields using only PHP scripts before inserting into a MySQL database?

I have fileds for:
Date (DD-MM-YYYY)
Time (24 hour format between 0900 and 1700)
Location (String)

Your suggestions will be a boost.

I have just written a script to do just that. I used ereg() to do a search for a regular expression that will verify a valid entry. e.g:

if (!ereg('^[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]$', $date)){
//I got an error when I used "[0-9]{2}" instead of "[0-9][0-9]"
//^ matches start of string and $ matches end.
  &errors_present = TRUE;
  $errors .= "date not valid";
}
if (!ereg('^[a-zA-Z ]+$', $date)){
//etc.

preg_match is usually faster.

preg_match("/^[0-9]{2}-[0-9]{2}-[0-9]{4}$/",$date)

should work, but its not tested.

Perhaps I've misunderstood the question, and the two previous posters used regular expressions but I would normally test a date with something like the following:

function is_date($value) {
  // If the value of the field is empty, immediately return false.
  if (empty($value)) { return FALSE; }

  // Attach the exploded values to variables.
  list($day, $month, $year) = explode("/", $value);

  // Check those variables in the checkdate function. If it's valid, the function returns true.
  return checkdate($month, $day, $year);
}
Comments
good

I am going to tryyour solutions with the date - wht about the 24 hour time format with the respective time given:

0900 - 1700

and Strings to ensure they are not used as SQL injection by users?

the time format could use a regex or a combination of is_numeric and strlen. and for the strings just run mysql_real_escape_string and you should be good.

there a many solutions to this problem so choose the one that will work for you best and run with it.

I am going to tryyour solutions with the date - wht about the 24 hour time format with the respective time given:

0900 - 1700

and Strings to ensure they are not used as SQL injection by users?

Something like this perhaps?

preg_match("/^[0-9]{4} - [0-9]{4}$/", $time)

Just in case you hadn't fixed it yet, (\d{2}\-){2}\d{4} is the correct regular expression. for 12-34-5678 format. To correctly write a regular expression you have to know exactly what your input must look like. If you post what you want in your fields it would be easier.

Comments
good

I am going to tryyour solutions with the date - wht about the 24 hour time format with the respective time given:

0900 - 1700

and Strings to ensure they are not used as SQL injection by users?

Time:

$time = "0900 - 1730";

// If there is one occurence of the needle in the value, then explode the array.
// If there is not, set it to an empty array.
if (substr_count($time, " - ") == 1) {
  list($startTime, $endTime) = explode(" - ", $time);
} else {
  list($startTime, $endTime) = array(0, 0);
}

String:

$name = O'Reilly

$sanitisedName = mysql_real_escape_string($name);

Hmm, your inputs are great.

About the time, I do not want a user to type any characters less than 4
ie '0900'
and any value less than 0900 or greater than 1700 should be rejected

With the date, I am accepting it as dd-mm-yyyy, I read some where that MySQL easily handles date as yyyy-mm-dd
in other to verify that, months are accepted as 01 to 12 and days do not go beyond 31 how would this be done?

Perhaps I've misunderstood the question, and the two previous posters used regular expressions but I would normally test a date with something like the following:

function is_date($value) {
  // If the value of the field is empty, immediately return false.
  if (empty($value)) { return FALSE; }

  // Attach the exploded values to variables.
  list($day, $month, $year) = explode("/", $value);

  // Check those variables in the checkdate function. If it's valid, the function returns true.
  return checkdate($month, $day, $year);
}

This is a great method for the date. If you wanted to you could even return "$year - $month - $day"; if it is a valid date or FALSE if it isn't seeing as you already have the variables set.

As for the time, I would probably take the input as a string and use one of the regular expression checks above to see if it consists of 4 numbers, then typecast it to an integer and check if ($time_num >= 900 && $time_num <= 1700) { echo "time is valid"; }

Comments
good

Great Stuff!

Your postings have giving me much understanding of most issues.

start_time is a varchar data type, is it possible to perform an addition on this variable?

Supposing $start_time has a value 1100 because I am using 24 hour format can I add a value to it?

ie. $start_time = $start_time + 100;

In php, if you try to perform an operation on $start_time (assuming that it is a variable in the script containing a string) it will be used as an integer. It works fine if the string is something like "1234". $start_time will then be converted to an integer variable (because it is on the left of the "=").

If there is a column in a mysql table that is named "start_time" and is a varchar datatype and you do something like $start_time = $row['start_time'] (where $row contains a row of a result from a query) then $start_time will be a string.

If you are writing a query to submit data to the mysql database and you can do something like

$query = "INSERT INTO table (`start_time`, `finish_time`) VALUES ( " . $start_time . ", " . $finish_time . ")";
//or
$query = "INSERT INTO table (`start_time`, `finish_time`) VALUES ( '" . $start_time . "', '" . $finish_time . "')";

With the single quotes ('), the mysql treats whatever is in them as a string no matter what. When you do $string = "$int"; or $string = "number is " . $int; , $int is used as a string. So when you are creating the query it doesn't matter what type $start_time is. Without the single quotes I think SQL will assume it's whatever type it needs to be. (mysql converts it to the right data type anyway so there's usually no problem). Just be careful doing "... WHERE culumn_name=$string" instead of "... WHERE culumn_name='$string'" because other wise only the first word of $string will be used.

This changing types is called "typecasting" if you want to research it more or anything like that.

This question has already been answered. Start a new discussion instead.