sending a email based on date and time.

[azgold]

I have the following code and i know im close but dont have it right. Im trying to setup a cron job to run and pull messages from a database every hour. Here is the code so far. 

 

 

<?php

$con = mysql_connect(mine","myuser","mypass");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("database", $con);

 

Need $time setup

$today = date("Y-m-d");

 

I need the query to select the data where i have 3 columns for the date Year-Month_Day and compare it to $today as well as the time Time and Time2. Time holds the time in 12 hr format and time2 holds AM/PM.

$result = mysql_query("SELECT * FROM table where Year-Month-Day >= '$today'");

 

I think the below is correct. What should happen is.... The script should see if the database has any messages that need to be sent today then depending on the hour send those messages to the person. I want the cron job to run hourly to do this. Reason for this is the person creating the messages is able to decide what time they want the message delivered.   The closest thing i can compare it to is a alarm clock. sending a message to the email address at a certain time on a certain date. Hope that helps

 

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

   

       {

            $email=$row['emailid'];

            $to = $EmailAddress;

 

            $subject = "You have a Message on mysite.com from $Name";

            $body = "$Message";

            $headers = 'From: Message@mysite.com' . "\r\n" ;

            $headers .= 'Reply-To: Message@mysite.com' . "\r\n";

            mail($to, $subject, $body, $headers);

    }

 

   mysql_close($con);

?>

 

[lemmin]

I'm guessing it's your query you're having trouble with.

 

The whole thing would be much easier if you just had one datetime field in your database. The datetime data type can store all of the data that your five different fields store separately. Then, your query could just be:

SELECT * FROM table WHERE datetime_field >= NOW()

[azgold]

yes your right. The user enters the date that they want it sent. by options. how do i merge them into one column?

[jugesh]

Instead of making three fields for Year-Month-Date make one field and insert the data in that field. then It will be easier for you select the records

[lemmin]

The datetime type uses the format YYYY-MM-DD HH-MM-SS. I don't know of a way to convert a twelve-hour time to twenty-four with MySQL so you might have to extract the data, convert it in PHP using strtotime(), then update your table. You can just make a new field, update the data, then drop your other five fields after the new data is in.