Page 1 of 1

How to check existence of a date in a database?

Posted: Fri Nov 26, 2010 12:10 pm
by infomamun
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:
<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:

Code: Select all

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();
}else{
exit("You have not written any date");
} 
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 the mysql will match the user input date first and if matched, then will proceed the above sql query. If not matched it will through an error and exit. I don't want to execute the above sql query first and depending on the query result to show whether date exists or not. I want that because, in practical, my sql query is so long. So instead run the long query, I want first a short query to match the date first.

What would be the necessary query to search the existence of the user date at first from the database?

Re: How to check existence of a date in a database?

Posted: Fri Nov 26, 2010 9:52 pm
by TorMike
Ha the date problem.

To get around this problem I use the time() function of PHP.

For example:

I create a table;

create employees(
name varchar2(20) not null,
date_hired int(11) not null <- not it's an INT
)

in my PHP code;

$date_hired = time();

insert = "insert into employees ('anyname', $date_hired)";

continue with PHP statement.....

Take a look at this URL:http://us2.php.net/manual/en/function.strftime.php and http://us2.php.net/manual/en/function.time.php

Since the date is now a simple number you can compare, subtract, add, then reformat using strftime to display to your client on the website.

Love it