Send rows which match certain date.

[dkin]

I would like to alter this script.

<?php

$link = mysql_connect("localhost","", "") or die ("couldnt connect: " . mysql_error());
mysql_select_db("eqoagui_nuke1",$link) or die ("couldnt select db: " . mysql_error());
$results = mysql_query("SELECT * FROM nuke_item_database WHERE date = CURDATE()",$link);

if (mysql_num_rows($results)==0) {
$message = "No items added today";
}
else {
$message = "Daily updates to your database\r\n";
while ($row = mysql_fetch_array($results)) {
$message .= "<a href=\"modules.php?name=Item_Database&file=item_page&id=" . $row['id'] . "\"><b>" . $row['Item_Name'] . "</b></a>, \r\n";

}
}

$to = "[e-mail]";
$subject = "Daily updates to your database";
$headers = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/plain; charset=utf-8\r\n";
$headers .= "From: Noreply <[e-mail]>\r\n";
$headers .= "Reply-To: none\r\n";
$headers .= "X-Mailer: PHP/" . phpversion();

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

echo "Results have been sent";

?>

So that it will send the results where the date = a date I have chosen.

This is what I have messed around with so far.

<?php
$now = "2004-07-28";
$link = mysql_connect("localhost","", "") or die ("couldnt connect: " . mysql_error());
mysql_select_db("eqoagui_nuke1",$link) or die ("couldnt select db: " . mysql_error());
$results = mysql_query("SELECT * FROM nuke_item_database WHERE date = $now",$link);
$row = mysql_fetch_array($results);

if (mysql_num_rows($results)==0) {
$message = "No items added today";
}
else {
$message = "Daily updates to your database\r\n";
while ($row = mysql_fetch_array($results)) {
$message .= "<a href=\"modules.php?name=Item_Database&file=item_page&id=" . $row['id'] . "\"><b>" . $row['Item_Name'] . "</b></a>, \r\n";

}
}

$to = "[e-mail]";
$subject = "Daily updates to your database";
$headers = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/plain; charset=utf-8\r\n";
$headers .= "From: [e-mail] <[e-mail]>\r\n";
$headers .= "Reply-To: none\r\n";
$headers .= "X-Mailer: PHP/" . phpversion();

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

echo "Results have been sent";
?>

But that does not seem to work even though there are dates that match.

Any Ideas?

[RonPK]

> does not seem to work

Could you be a bit more specific? Errors? No rows? No mail?

> SELECT * FROM nuke_item_database WHERE date = $now

Maybe this will produce more results:

SELECT * FROM nuke_item_database WHERE date = '$now'

Otherwise MySQL would perform a substraction: 2004 - 07 - 28 = uhh, well, not the date you're looking for.

[ergophobe]

dkin,

I'll try to give a quick answer, but if that's not what you're looking for, how about we tackle your other problem [webmasterworld.com] first and come back to this. I get too confused by the multiple threads going related to the same script.

I think if you follow Jatar_K's method in that other thread, a lot of your other questions will get easier to handle. How's that sound?

Also, it's *much* harder to help with a specific problem when you post such big blocks of code.

Anyway, the problem is that your dates are probably timestamps so unless someone registered exactly at midnight (2004-07-28 00:00:00), date="2004-07-28" will never match. You need to format the date so that it is only the date part, like so:

$now = "2004-07-28";
$query = SELECT * FROM nuke_item_database WHERE date_format(date, '%Y-%m-%d')=$now;

Tom

[dkin]

Terribly sorry if my posts are hard to understand, I get confused alot and often do not know what I am trying to do with my code.

In my insert form I have the date field specified like so.

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

So any time I put something like this in

2004-07-28

It should match atleast one row, in fact that exact date matches about 11 rows but still sends no results.

[ergophobe]

Okay, so here's what I do when I have a similar problem.

1. separate the php part and the sql part
2. get the SQL part right
3. integrate with PHP

In longer form that's

1. Open up your mysql client, whatever it is (default mysql client, phpMyAdmin, mysql-front, whatever). Forget about PHP for the moment. Connect to your db and run a simple query to make sure you're connected and so on.

2. Now debug your query. Add complexity and conditions until mysql returns what you want. Now you know exactly what query will return the results you want.

3. Bring it into your script. Now at the least you know that if the results aren't as expected, the problem lies with processing the result set, not with defining it.

Tom

[dkin]

well what is happening is I am getting the right output on the page but it sends me "No items added today", which meansnothing matched the date I entered. I will dive deeper.

[dkin]

I dont need it anymore I figured out an alternate way.

Thanks guys, will keep suggestions in my head for future.