Help with query

[rickarro]

Hey Guys,

I need a little help. I have a calendar script that I want to write a query for and pull out the day of the week, then have it send an email showing all the entries for that day. Unfortunately i'm querying a day, month, year as seperate fields and they are int not date fields. I have accomplished querying the database and used a date/mktime function to convert the int fields into a date field. Where I'm running into trouble is, I can't figure out how to pull this date field and compair it to todays date (date()), and have it display only the records from todays. Here is the code I have so far, I know it is very simple so If you have a better way of doing this, please let me know.

<?php
require("config.php");

mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());

$query = "SELECT * FROM pec_mssgs ORDER BY m, d";
$result = mysql_query($query) or die('Nope, query didn\'t work');
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$d = $row['d'];
$m = $row['m'];
$y = $row['y'];
$title = $row['title'];
$text = $row['text'];

$newdate = date("Y-m-d", mktime(0, 0, 0, $m, $d, $y));

echo "$newdate ($text, $title)<br>\n";
}

if ($newdate == date("Y-m-d"))
{
	echo "good if";
} else
	echo "bad if";

?>

This displays:
2009-01-07 (test, test)
2009-05-19 (test, test)
2009-05-20 (this is a test, today is 20 may)
2009-05-25 (Have a Great Day!!, Memorial Day)
2009-05-25 (test, testing day)
bad if

So far it is doing what I want but I want only those listed above that match today's date. Then I can take those items and send them out in an e-mail.

Any help is much appreciated. Thx.

[Ezzaral]

Why not set the parameters of your query to only pull records where m=$todaysMonth and d=$todaysDay and y=$todaysYear?

[rickarro]

I tried this:

$todaysMonth = date('m');
$todaysDay = date('d');
$todaysYear = date('y');

mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());

$query = "SELECT * FROM pec_mssgs WHERE m=$todaysMonth, d=$todaysDay, y=$todaysYear";
$result = mysql_query($query) or die('Nope, query didn\'t work');

My result was "Nope, query didn't work". Did I put it wrong in my select statement?
Thanks.

[Ezzaral]

You would want the clause to be m=$todaysMonth AND d=$todaysDay AND y=$todaysYear

[Will Gresham]

It should be

$query = "SELECT * FROM pec_mssgs WHERE m=$todaysMonth AND d=$todaysDay AND y=$todaysYear";

Edit: Ezzaral beat me to it :)

[rickarro]

Ok, i see my error, my select statement needed "And" instead of ",". It seems to be working just fine they way you said. I will try to progress with that. Thank you so much for the help.

[rickarro]

Ok, all is working well, I put the email functions in and it is working fine. My only problem now is that if there are more than one item on the calendar for that day, the email lists only the last item, not all of them. I need it to either list all of them, or one email for each, either one is acceptable. Here is the code I have so far:

<?php
require("config.php");

$todaysMonth = date('m');
$todaysDay = date('d');
$todaysYear = date('Y');
echo "the year is: $todaysYear<br>";
echo "the month is: $todaysMonth<br>";
echo "the day is: $todaysDay<br>";
//echo $today;

mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());

$query = "SELECT * FROM pec_mssgs WHERE m=$todaysMonth AND d=$todaysDay AND y=$todaysYear";
$result = mysql_query($query) or die('Nope, query didn\'t work');
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$d = $row['d'];
$m = $row['m'];
$y = $row['y'];
$title = $row['title'];
$text = $row['text'];

$newdate = date("Y-m-d", mktime(0, 0, 0, $m, $d, $y));

$to = "myemail$here.com";
$subject = "Today's Calendar Reminder";
$message = "Today's Calendar Reminder: $title";
$from = "Web Calendar Reminder";
$headers = "From: $from";

echo "$newdate ($text, $title)<br>\n";
}

if ($newdate == date("Y-m-d"))
{
	mail($to,$subject,$message,$headers);
} else

	echo "bad if";

?>

I'm thinking I need some sort of a loop maybe?

[Will Gresham]

When you assign a value to $message, use .= rather than just = , this will append the text to the end of the current value. You may also want to put an \r\n onto the end of the string to add a new line at the end of each event.

EDIT: Also, take some of those statements that will not change out of the WHILE statement (i.e. the $from and $subject) otherwise they are having the same values written to them multiple times.

While will loop through all results.

[rickarro]

Thank you Xan, I entered this:

$message .= "Today's Calendar Events Reminder: $title\r\n";

it displays multiple events, but does not put a return break after each one. Also, is there a way to put the part before $title only once, otherwise it prints "Today's Calendar Events Reminder" for each event. If it does, its ok for now, but looks funny :)

thanks again.

[Will Gresham]

Rather than

$message .= "Today's Calendar Events Reminder: $title\r\n";

Just put

$message .= "$title\r\n";

Then before use mail() put:

$message = "Today's Calendar Events Reminder: " . $message;

[rickarro]

That worked great. I did have to put this in:

$mmessage = "Today's Calendar Events Reminder: \n" . $message;

and make my mail line look like this:

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

It seems that using the $message twice confised things, so changing the first one to $mmessage fixed that problem.

My next plan is to get the server to run this at a preestablished time using task manager. Do you see a problem with this, or is there a better way. I only want it to run once per day.

Thanks for all your help, its great.

[Will Gresham]

If you want to run in once a day, then that would be fine, if you are on a Linux server, a Cron job would be best.

[rickarro]

I'm running on a windows server with IIS. I did try to run it using task manager on my test machine (windows XP w/IIS), telling it to run a php file only opened the file for editing at the designated time. This is my first time doing this, how would I get this to run at a designated time using task manager?

[rickarro]

Ok, I did some research and got this to work. For people who don't know how to set up a cronjob in Windows/IIS, here is how I did it.

First I created a .bat file called cron.bat, created it in notepad and saved it as that file name, here is the code:

SET PATH="c:\PHP"
   start php.exe timemail.php

timemail.php is the file I wanted to run. C:\PHP is the location of my php.exe file that will run it. The only way I could get it to work was by putting it in my web root. I don't know if this is a security problem or not, but i'm running this on my network intranet server so I can get away with it. I then set up a schedule to run the cron.bat file at a specified time. my timemail.php file pulls data from my calendar and sends an email to designated people stating what events are occurring for that day, i let it run at like 4am before everyone gets to work. Thats it. I thought this was going to be real hard, but it was quite easy. Fearing the unknown is no fun.

Thanks for ya'lls help with getting this running, I really appreaciate it.