DaniWeb Forum Index > Web Development > PHP

Help with query

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jan 2008

Posts: 100

Reputation: rickarro is an unknown quantity at this point

Solved Threads: 1

rickarro

Offline

Junior Poster

Help with query

May 20th, 2009

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.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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";
 
?>
<?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.

•

Join Date: May 2007

Posts: 4,461

Reputation: Ezzaral has much to be proud of

Solved Threads: 511

Ezzaral

Offline

Industrious Poster

Re: Help with query

May 20th, 2009

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

•

Join Date: Jan 2008

Posts: 100

Reputation: rickarro is an unknown quantity at this point

Solved Threads: 1

rickarro

Offline

Junior Poster

Re: Help with query

May 20th, 2009

I tried this:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$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');
$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.

•

Join Date: May 2007

Posts: 4,461

Reputation: Ezzaral has much to be proud of

Solved Threads: 511

Ezzaral

Offline

Industrious Poster

Re: Help with query

May 20th, 2009

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

•

Join Date: May 2008

Posts: 524

Reputation: Will Gresham is on a distinguished road

Solved Threads: 86

Will Gresham Will Gresham is offline

Offline

Posting Pro

Re: Help with query

May 20th, 2009

It should be

Help with Code Tags

php Syntax (Toggle Plain Text)

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

Edit: Ezzaral beat me to it

Last edited by Will Gresham; May 20th, 2009 at 4:11 pm.

AJAX is not a programming language, scripting language or any other sort of language.
It is acheived by using JavaScript http functions.
So, AJAX = JavaScript.

•

Join Date: Jan 2008

Posts: 100

Reputation: rickarro is an unknown quantity at this point

Solved Threads: 1

rickarro

Offline

Junior Poster

Re: Help with query

May 20th, 2009

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.

•

Join Date: Jan 2008

Posts: 100

Reputation: rickarro is an unknown quantity at this point

Solved Threads: 1

rickarro

Offline

Junior Poster

Re: Help with query

May 20th, 2009

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:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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";
 
?>
<?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?

•

Join Date: May 2008

Posts: 524

Reputation: Will Gresham is on a distinguished road

Solved Threads: 86

Will Gresham Will Gresham is offline

Offline

Posting Pro

Re: Help with query

May 20th, 2009

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.

Last edited by Will Gresham; May 20th, 2009 at 5:02 pm.

AJAX is not a programming language, scripting language or any other sort of language.
It is acheived by using JavaScript http functions.
So, AJAX = JavaScript.

•

Join Date: Jan 2008

Posts: 100

Reputation: rickarro is an unknown quantity at this point

Solved Threads: 1

rickarro

Offline

Junior Poster

Re: Help with query

May 20th, 2009

Thank you Xan, I entered this:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$message .= "Today's Calendar Events Reminder: $title\r\n";
$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.

•

Join Date: May 2008

Posts: 524

Reputation: Will Gresham is on a distinguished road

Solved Threads: 86

Will Gresham Will Gresham is offline

Offline

Posting Pro

Re: Help with query

#10

May 20th, 2009

Rather than

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$message .= "Today's Calendar Events Reminder: $title\r\n";
$message .= "Today's Calendar Events Reminder: $title\r\n";

Just put

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$message .= "$title\r\n";
$message .= "$title\r\n";

Then before use mail() put:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$message = "Today's Calendar Events Reminder: " . $message;
$message = "Today's Calendar Events Reminder: " . $message;

AJAX is not a programming language, scripting language or any other sort of language.
It is acheived by using JavaScript http functions.
So, AJAX = JavaScript.