Ad:

PHP Discussion Thread
Marked Solved
Views: 802

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

May 20th, 2009

Help with query

Expand Post »

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 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.

Similar Threads

Removing Query Strings in ASP.NET
Sql Query in VB.NET
Double MySQL Query in PHP
Dynamic Query in JSP
MySQL nested query / joined query conversion help in MySQL

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

May 20th, 2009

Re: Help with query

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

Ezzaral

Moderator

Featured Poster

Reputation Points: 3239

Solved Threads: 839

Posting Genius

Offline

6,761 posts
since May 2007

Permalink

May 20th, 2009

Re: Help with query

I tried this:

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.

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

May 20th, 2009

Re: Help with query

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

Ezzaral

Moderator

Featured Poster

Reputation Points: 3239

Solved Threads: 839

Posting Genius

Offline

6,761 posts
since May 2007

Permalink

May 20th, 2009

Re: Help with query

It should be

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.

Will Gresham

Reputation Points: 96

Solved Threads: 124

Master Poster

Offline

728 posts
since May 2008

Permalink

May 20th, 2009

Re: Help with query

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

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

May 20th, 2009

Re: Help with query

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 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?

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

May 20th, 2009

Re: Help with query

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.

Will Gresham

Reputation Points: 96

Solved Threads: 124

Master Poster

Offline

728 posts
since May 2008

Permalink

May 20th, 2009

Re: Help with query

Thank you Xan, I entered this:

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.

rickarro

Reputation Points: 10

Solved Threads: 1

Junior Poster

Offline

107 posts
since Jan 2008

Permalink

May 20th, 2009

Re: Help with query

Rather than

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

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

Then before use mail() put:

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

Will Gresham

Reputation Points: 96

Solved Threads: 124

Master Poster

Offline

728 posts
since May 2008

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Concurrent Query ?

Next Thread in PHP Forum Timeline: Arrays: Invertated Commas and Apostrophes

DaniWeb Message

Cancel Changes

User Name
Password