Select from database if exists

stockpedia · **Select from database if exists -** 11-26-2010, 04:53 PM

HI there,
Suppose I have a mysql table like this-

Table Name: date_value
id--date------value1---value2---value3
1 - 24/11/10 - 100 ----- 105 ---- 110
2 - 24/11/10 - 500 ----- 504 ---- 502
3 - 24/11/10 - 300 ----- 304 ---- 310
4 - 23/11/10 - 120 ----- 130 ---- 160
5 - 23/11/10 - 563 ----- 541 ---- 550
6 - 23/11/10 - 341 ----- 325 ---- 319

Now I have a page form.html like this:

HTML Code:

<form method="post" action="sql.php">
<input name="date" type="text" size="10" value="write DD/MM/YY" />
<input type="submit" value="Go" />
</form>

Now my action page sql.php has this mysql query:

PHP Code:


			
if(isset($_POST['date']){

$date = $_POST['date'];

//code for connecting database and table here;

$result = mysql_query("SELECT * FROM date_value where date='$date'");

while($row=mysql_query($result)){

echo $row['value1']."&nbsp;&nbsp;".$row['value2']."&nbsp;&nbsp;".$row['value3']."<br>";

}

mysql_close();

So, if user writes the date as 24/11/10 in form.html then sql.php will show him/her the consequent value from database.
But, if the user write such a date (suppose, 20/11/2010 or 30/11/2010), then mysql will not find the date in the database and will return error.

But I want that mysql will check first whether the date 20/11/10 or 30/11/10 exists in the database or not. If it is not exists then it will show "Record of your search date is not found in the database and will exit. But if the date exists then it will show the value. I don't want to proceed the stated sql query first and based on the error of the sql query search if date is present or not. Instead, I want that date will be searched first and if matched then the above sql query will be executed.

What will be the query for searching user input date in the mysql database from date column?

flips22738 · 11-27-2010, 07:15 AM

You need to implement some form validation. The quickest way is JavaScript, but it can be done on the server side as well.

First limit the number of characters that can be entered in the field with the 'maxlength' attribute of the text input, like this.

HTML Code:

<input name="dateField" id="dateField" type="text" size="12" value="write DD/MM/YY" maxlength="8" />

Next validate the entry with this JavaScript function.

Code:

function validateForm()
{
	with(document.getElementById("dateValueForm"))
	{
		var strDate = dateField.value;
		var objRegExPattern = new RegExp(/^[0-9]{2}\/[0-9]{2}\/[0-9]{2}$/);
		var boolValidPattern = objRegExPattern.test(strDate);
		if(boolValidPattern)
		{
			var objDate = new Date(strDate);
			if(objDate == "Invalid Date")
			{
				dateField.select();
			}
			else
			{
				submit();
			}
		}
		else
		{
			dateField.select();
		}
	}
}

What the heck is all that?
1. Get the value of the date field
2. Create a Regular Expression object that ensures the the value of the date field is only two digits, a forward slash, two more digits, another forward slash, and finally two more digits, or 11/22/33.
3. Test the value of the date field with the RegEx pattern.
4. Test the validity of the pattern test.
4a. If the we have a valid pattern...
5. Try to create a Date object from the value of the date field.
5a. If the date object is not a valid date
5aa. Return the user to the form.
5b. If the date object is a valid date
5ba. Submit the form.
4b. If the pattern is invalid
4ba. Return the user to the form.

So we end up something like this.

HTML Code:

<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>Date Value Page</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<script type="text/javascript">
function validateForm()
{
	with(document.getElementById("dateValueForm"))
	{
		var strDate = dateField.value;
		var objRegExPattern = new RegExp(/^[0-9]{2}\/[0-9]{2}\/[0-9]{2}$/);
		var boolValidPattern = objRegExPattern.test(strDate);
		if(boolValidPattern)
		{
			var objDate = new Date(strDate);
			if(objDate == "Invalid Date")
			{
				dateField.select();
			}
			else
			{
				submit();
			}
		}
		else
		{
			dateField.select();
		}
	}
}
</script>
</head>

<body>

<form id="dateValueForm" method="post" action="sql.php" enctype="multipart/form-data">
	<input name="dateField" id="dateField" type="text" size="12" value="write DD/MM/YY" maxlength="8"/>
	<input type="button" value="Go" onclick="javascript:validateForm()"/>
</form>

</body>
</html>

I hope this helps.

flips22738 · 11-27-2010, 03:05 PM

In my previous post we took care of the front end, now lets clean up the back end.

If you use the validateForm JavaScript function we know that we're getting a date string formatted as desired DD/MM/YY. So that means far less validation after the form has been submitted, but its always good practice to protect against injection attack, so let's start there.

Assume the included db_connection.php file contains an instance of the database connection object in the form of a variable $dbLink.

PHP Code:


			
include_once "./helpers/db_connection.php";

foreach($_POST as $key => $value)

{ 

    $_POST[$key] = mysql_real_escape_string($value,$dbLink);

}

Now we can be reasonably confident that the form submission is safe for database introduction.

So lets run the query and validate the return.

PHP Code:


			
$strQuery = "SELECT * FROM date_value where date=\"{$_POST['dateField']}\"";

$arrResult = mysql_query($strQuery,$dbLink);

if($arrResult)

{

    if(mysql_num_rows($arrResult))

    {

        while($row = mysql_fetch_assoc($arrResult))

        {

            $arrRows[] = $row;

        }

        mysql_free_result($arrResult);

    }

    else

    {

        mysql_close($dbLink);

        exit("Sorry but the date that you entered is not in our database.");

    }

}

else

{

    mysql_close($dbLink);

    exit("Sorry but the date that you entered is not in our database.");

}

mysql_close($dbLink);

There, now we should have a multidimensional array of database records that could be displayed like this.

PHP Code:


			
foreach($arrRows as $arrRow)

{

    for($i=0;$i<count($arrRow);$i++)

    {

        print "<p>{$arrRow['value1']} :: {$arrRow['value2]} :: {$arrRow['value3]}</p>\n";

    }

}

Again I hope this helps.