PHP MySql Help

[joshuaer]

Hello I am trying to create a sendmail script that looks at my database in the day field and compares it to the current date. When I run the script it works fine but it sends out the email even if it does not match the current date. can someone please look at my script and tell me what I am doing wrong. Thank you in advance <?phpini_set('display_errors', 1);error_reporting(E_ALL); mysql_connect("", "", "") or die(mysql_error()); mysql_select_db("") or die(mysql_error()); $headers = '';$result = mysql_query("SELECT * FROM users")or die(mysql_error());$row = mysql_fetch_assoc($result);$Cnumber = mysql_result($result,$row,"Cnumber");$address = mysql_result($result,$row,"address");$iChore1 = mysql_result($result,$row,"iChore1,");$iChore2 = mysql_result($result,$row,"iChore2");$iChore3 = mysql_result($result,$row,"iChore3,");$iChore4 = mysql_result($result,$row,"iChore4");$iChore5 = mysql_result($result,$row,"iChore5"); $txt = "Todays iChores - "; $d = date ("D");$day = ("SELECT * FROM users where day='$d'"); if ($day) { $count = 0; while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {$message = $txt . $row['iChore1'].', ' . $row['iChore2'].', ' . $row['iChore3'].', ' . $row['iChore4'].', ' . $row['iChore5'];$to = $row['Cnumber'] . $row['address']; mail($to, $headers, $message); $count++; } echo "myResult=$count Emails Sent. Done."; print $to;print $message;print $day; } else { echo "myResult=Email Submissions Failed."; } ?>

[justsomeguy]

This loop: while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) is looping over this result set: $result = mysql_query("SELECT * FROM users")or die(mysql_error()); There's nothing in that query to filter it, you're telling it to get every record from the table.

[joshuaer]

I am fairly new to this so sorry if this sounds stupid but I need it to look at every record to look at reach row until it finds a match then sends out the email to the address in that row along with the data in the row for the content of the email.

[justsomeguy]

How do you store the day information in the database? It would make more sense to only get the users that need to be sent today instead of all of them.

[joshuaer]

they are stored by Mon,Tue,Wed,etc

[Guest So Called]

How do you store the day information in the database? It would make more sense to only get the users that need to be sent today instead of all of them.

Isn't this it?

$d = date ("D");$day = ("SELECT * FROM users where day='$d'");

It would seem that table 'users' has a field 'day' that is Mon through Sun. It appears to me that the code above selects only users who have their 'day' equal to today: date("D"). Edit: No, that's not quite it. OP should use something like this:

$d = date ("D");$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());

In other words, select only users whose day is today. Then work on that.

Edited July 12, 2012 by So Called

[joshuaer]

Yes but when the script runs it sends out the email to everyone in the database even if the day does not match

[justsomeguy]

That's why you need to only select the users where the day matches, instead of everybody.

[joshuaer]

Ok, thank you so much for the help I removed the other select line and just have this $d = date ("D");$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());$row = mysql_fetch_assoc($result); and it looks to be working the way I want now Thanks again

[Guest So Called]

Heh! That's the way I would have done it. I presume that $row= will be in some form of iterative loop, and exit when the last $row has been processed.

[joshuaer]

I do need to put it in a loop so it will check each line, not exactly sure what is the best way to do that. would I use for to loop through? Thanks again

[birbal]

yes you need to put it in loop if there is more than one rows to return. mysql_fetch_*() fetches current row and move internal resource pointer ahead. by looping through you will tarvarse all the entries. Typically while loop is used for mysql result resource iteration. you can use any loop though. bu there is reason to use specific loops for specific situation. when ietration count is unknown it is good to use while loop, when you know the iteration time it is good to use for loop.you can make a loop to act as another loop.

Edited July 13, 2012 by birbal

[Guest So Called]

Something like this:

$d = date ("D");$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());while ($row = mysql_fetch_assoc($result)) {	 // process each row here}

[joshuaer]

<p>does this look correct So Called? It is running without error but it is saying there are no emails to send and there is a record in the day field for Fri</p><p> </p><p> </p><div><?php</div><div>ini_set('display_errors', 1);</div><div>error_reporting(E_ALL);</div><div> </div><div> </div><div>mysql_connect("", "", "") or die(mysql_error()); </div><div>mysql_select_db("") or die(mysql_error()); </div><div>$headers = '';</div><div>$d = date ("D");</div><div>$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());</div><div>while ($row = mysql_fetch_assoc($result)) {</div><div>}</div><div>//$row = mysql_fetch_assoc($result);</div><div>//$Cnumber = mysql_result($result,0,"Cnumber");</div><div>//$address = mysql_result($result,1,"address");</div><div>//$iChore1 = mysql_result($result,0,"iChore1");</div><div>//$iChore2 = mysql_result($result,0,"iChore2");</div><div>//$iChore3 = mysql_result($result,0,"iChore3");</div><div>//$iChore4 = mysql_result($result,0,"iChore4");</div><div>//$iChore5 = mysql_result($result,0,"iChore5");</div><div> </div><div>$txt = "Todays iChores - ";</div><div> </div><div> </div><div> </div><div>if(mysql_num_rows($result) > 0) </div><div> </div><div>//if ($row)</div><div> </div><div>{ </div><div>$count = 0; </div><div>while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) </div><div>{</div><div>$message = $txt . $row['iChore1'].' ' . $row['iChore2'].' ' . $row['iChore3'].' ' . $row['iChore4'].' ' . $row['iChore5'];</div><div>$to = $row['Cnumber'] . $row['address']; </div><div> </div><div> </div><div> </div><div> </div><div>mail($to, $headers, $message); </div><div>$count++; </div><div>} </div><div>echo "myResult=$count Emails Sent. Done."; </div><div>print $row;</div><div>echo "$row";</div><div>//print $message;</div><div>//print $day;</div><div> </div><div>} </div><div>else </div><div>{ </div><div>echo "myResult=Email Submissions Failed."; </div><div> </div><div>}</div><div> </div><div>?></div>

[Guest So Called]

I think you should repost and put your code inside CODE /CODE tags. Note also, the post editor on this site often messes up, as post #14 shows.

Edited July 13, 2012 by So Called

[joshuaer]

<?phpini_set('display_errors', 1);error_reporting(E_ALL);mysql_connect("", "", "") or die(mysql_error());mysql_select_db("") or die(mysql_error());$headers = 'iChores.com';$d = date ("D");$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());while ($row = mysql_fetch_assoc($result)) {}//$row = mysql_fetch_assoc($result);//$Cnumber = mysql_result($result,0,"Cnumber");//$address = mysql_result($result,1,"address");//$iChore1 = mysql_result($result,0,"iChore1");//$iChore2 = mysql_result($result,0,"iChore2");//$iChore3 = mysql_result($result,0,"iChore3");//$iChore4 = mysql_result($result,0,"iChore4");//$iChore5 = mysql_result($result,0,"iChore5");					  $txt = "Todays iChores - ";if(mysql_num_rows($result) > 0)//if ($row){$count = 0;while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)){$message = $txt . $row['iChore1'].'  ' . $row['iChore2'].'  ' . $row['iChore3'].'  ' . $row['iChore4'].'  ' . $row['iChore5'];$to = $row['Cnumber'] . $row['address'];mail($to, $headers, $message);$count++;}echo "myResult=$count Emails Sent. Done.";print $row;echo "$row";//print $message;//print $day;}else{echo "myResult=Email Submissions Failed.";}?>

[Guest So Called]

So does it work?

[joshuaer]

it runs with out any errors but the echo I have set in it to show the results says there are 0 emails sent, but there are 2 that should go out for Fri

[Guest So Called]

Well put in some debug statements to echo important variables at various critical spots in your code. You don't fix code by looking at it. (Which as all I could do for you.) You fix code yourself by adding statements that give you insight into what is going wrong, what is happening or what is not happening. It looks like your while loop has no code in the body of the loop,.

[justsomeguy]

You added an empty while loop which is going to loop through all of the records until it gets to the end. When you get to the second while loop which actually sends the emails the record set is already at the end so it doesn't loop again.

[joshuaer]

I have been trying to figure out what I am doing wrong but am not sure, with the code below it show me the information that should go out in the emails, and it is correct. There are 2 emails that should be going out but it is showing the sendmail results as 0 I am guessing that it is my if statement that I have incorrect can someone please point me in the right direction. Thanks

<?phpini_set('display_errors', 1);error_reporting(E_ALL);mysql_connect("", "", "") or die(mysql_error());mysql_select_db("") or die(mysql_error());$headers = 'iChores.com';$d = date ("D");$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());while ($row = mysql_fetch_assoc($result)) {//$row = mysql_fetch_assoc($result);echo $row["Cnumber"];echo $row["address"];echo $row["iChore1"];echo $row["iChore2"];echo $row["iChore3"];echo $row["iChore4"];echo $row["iChore5"];}					  $txt = "Todays iChores - "; if(mysql_num_rows($result) > 0)//if ($row){$count = 0;while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)){$message = $txt . $row['iChore1'].'  ' . $row['iChore2'].'  ' . $row['iChore3'].'  ' . $row['iChore4'].'  ' . $row['iChore5'];$to = $row['Cnumber'] . $row['address'];mail($to, $headers, $message);$count++;}echo "myResult=$count Emails Sent. Done.";print $row;echo "$row";//print $message;//print $day;}else{echo "myResult=Email Submissions Failed.";}?>

[justsomeguy]

Because, like I said, when you're finished looping through the result set then the pointer in that result set is at the end. When you do this:

while ($row = mysql_fetch_assoc($result)) {//$row = mysql_fetch_assoc($result);echo $row["Cnumber"];echo $row["address"];echo $row["iChore1"];echo $row["iChore2"];echo $row["iChore3"];echo $row["iChore4"];echo $row["iChore5"];}

It prints everything out, and at the end of that loop the result pointer is at the end. That means that when you try to loop again to send the emails it doesn't loop. You need to either rewind the result so that it's back at the start for the second loop, or combine the 2 loops into a single loop. If you want the 2 loops and want to rewind the result then you use this: http://www.php.net/manual/en/function.mysql-data-seek.php

[Guest So Called]

<?phpini_set('display_errors', 1);error_reporting(E_ALL); mysql_connect("", "", "") or die(mysql_error());mysql_select_db("") or die(mysql_error());$headers = 'iChores.com';$d = date ("D");$result = mysql_query("SELECT * FROM users where day='$d'")or die(mysql_error());//while ($row = mysql_fetch_assoc($result)) {//$row = mysql_fetch_assoc($result);//echo $row["Cnumber"];//echo $row["address"];//echo $row["iChore1"];//echo $row["iChore2"];//echo $row["iChore3"];//echo $row["iChore4"];//echo $row["iChore5"];//}					  $txt = "Todays iChores - "; if(mysql_num_rows($result) > 0)//if ($row){$count = 0;while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)){$message = $txt . $row['iChore1'].'  ' . $row['iChore2'].'  ' . $row['iChore3'].'  ' . $row['iChore4'].'  ' . $row['iChore5'];$to = $row['Cnumber'] . $row['address']; mail($to, $headers, $message);$count++;}echo "myResult=$count Emails Sent. Done.";print $row;echo "$row";//print $message;//print $day;}else{echo "myResult=Email Submissions Failed.";}?>

Try this. (commented out lines)

[joshuaer]

Yes that code worked.. Thank you So Called and also thank you Just Some Guy, I understand how it should be working now. I appreciate both of your help

[Guest So Called]

Your first while ($row = mysql_fetch_assoc($result)) loop used up all your $result (to no useful purpose as far as I could tell). By the time you tried it again it was all gone. You should have either rewound it (not correct term) or you could have repeated your select query.