PHP MySQL Date comparison

Question

I insert a birth date through a text field into a table. Here I need a mysql query which checks the current month and current date with any of the dates matching in database. No need to check the year because this is meant for displaying an alert on birthday, Can any one have the idea of the SQL Query to do this in a single query.. Here I use this with PHP and MySQL. Thanks in Advance

My Sample Code is :

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);

$my_birth_date = "2010-11-08";

$result = mysql_query("SELECT * FROM mydate WHERE DAYOFMONTH($my_birth_date) = DAYOFMONTH(NOW()) AND MONTH($my_birth_date) = MONTH(NOW())
");


while($row = mysql_fetch_array($result))
  {
  echo $row['my_name'] . " " . $row['my_dob'];
  echo "<br />";
  }

mysql_close($con);
?>

My Table Schema is: my_name varchar(50) my_dob date

Date is being stored in table like this format : 2010-01-20 (Year-Month-Day) Now it is displaying a blank page with out any records, even if i has records matching. Can any one give some suggestion

Please provide the CREATE TABLE statement. If storing a date, the column should be using a DATETIME or DATE data type, not string based because you'll have to convert it to a date related data type before being able to do date operations/comparisons. — OMG Ponies
– OMG Ponies, Commented Nov 8, 2010 at 17:00

Gazillion · Accepted Answer · 2010-11-08 17:01:28Z

1

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

You can find all the Date & Time Functions available in MySQL. You'd probably want to use the DAYOFMONTH and MONTH functions to compare :)

So something like:

SELECT * FROM table WHERE DAYOFMONTH(birthday) = DAYOFMONTH(NOW()) AND MONTH(birthday) = MONTH(NOW())

There might be some more efficient ways to do this but this is what I'd try first! Hope that helps!

answered Nov 8, 2010 at 17:01

Gazillion

4,79211 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SK0004 Over a year ago

Hai Gazlion..Thanks for your reply

Gazillion Over a year ago

You might want to edit your original question with this information. Do you get an error? What does your table schema look like?

SK0004 Over a year ago

@Gazillion: I have edited my question with updated messages. Can you please have a look

Gazillion Over a year ago

Well right now you're not comparing with the date of birth in the DB, you're comparing it with a PHP variable. Switch $my_birth_date in your SQL statement with my_dob. The idea is that you're comparing the columns in the your database with the current timestamp.

SK0004 Over a year ago

@Gazillion: I have replaced the variable as you told. But now also only a blank page is showing. I printed the $result and I got "Resource id #3". What may be the problem?

|

Community · Accepted Answer · 2023-11-17 19:24:28Z

0

Your sample code should return all the records in the "mydate" table.

Try the query directly on the server(in a terminal or with an app like phpMyAdmin) to check if it returns data.

Use mysql_error to debug your code.

Example :

<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password");

mysql_select_db("nonexistentdb", $link);
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

mysql_select_db("kossu", $link);
mysql_query("SELECT * FROM nonexistenttable", $link);
echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
?>

mysql_error : php.net.

Then you should see where it crash.

edited Nov 17, 2023 at 19:24

CommunityBot

11 silver badge

answered Nov 8, 2010 at 18:16

codea

1,2524 gold badges18 silver badges27 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Date comparison

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related