proctk Posted December 13, 2006 Share Posted December 13, 2006 error message any idea what is causing thisYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM child[code=php:0]$get_childdob = mysql_query("SELECT STR_TO_DATE(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM childdob),'-',EXTRACT(YEAR FROM CURDATE()),'%d-%m-%y') as newdate, owner_id FROM children WHERE childdob < DATE_SUB(CURDATE(),INTERVAL 20 DAYS); ")or die (mysql_error()); [/code] Quote Link to comment Share on other sites More sharing options...
fert Posted December 13, 2006 Share Posted December 13, 2006 what's the error? Quote Link to comment Share on other sites More sharing options...
proctk Posted December 13, 2006 Author Share Posted December 13, 2006 ops I missed up the copy and pastYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM child Quote Link to comment Share on other sites More sharing options...
proctk Posted December 13, 2006 Author Share Posted December 13, 2006 this is what I have this far and still having issues[code=php:0] mysql_query("SELECT STR_TO_DATE(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM childdob),'-',EXTRACT(YEAR FROM CURDATE()))),'%d-%m-%y') as ThisYearsDate FROM children WHERE ThisYearsDate < DATE_SUB(CURDATE(),INTERVAL 20 DAYS")or die (mysql_error()); [/code] Quote Link to comment Share on other sites More sharing options...
artacus Posted December 13, 2006 Share Posted December 13, 2006 Boy is that hard to read. How 'bout a return? You can't use a field you've defined in your select clause in your where clause. Quote Link to comment Share on other sites More sharing options...
proctk Posted December 13, 2006 Author Share Posted December 13, 2006 I have been working at this for three days now. I'll posy an update to where I'm at.what I'm trying to do: show all members that are having a birthday in the next 20 days.This web site has been a great help to me. I really want to solve this problem. If someone helps me get it solved I will make a $20.00 donation to php freakserror message[b]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(CONCAT(EXTRACT(DAY FROM CURDATE()),'-',EXTRACT(MONTH FROM CURD[/b][code=php:0] $get_childdob = mysql_query("select STR_TO_DATE(CONCAT(EXTRACT(DAY FROM CURDATE()),'-',EXTRACT(MONTH FROM CURDATE()),'-',EXTRACT(YEAR FROM CURDATE()))),'%d-%m-%Y') as ThisYearsDate FROM children WHERE ThisYearsDate < DATE_SUB(CURDATE(),INTERVAL 20 DAYS")or die (mysql_error()); $get_childdob = mysql_query($query)or die(mysql_error());[/code] Quote Link to comment Share on other sites More sharing options...
artacus Posted December 13, 2006 Share Posted December 13, 2006 This would select all the children who have birthdays in the next 20 days. But you can modify it to get what you want. Oh, and can you send that $20 to the Artacus cookie fund? :)[code]SELECTFROM children AS cJOIN ( SELECT id, CONCAT(IF(DAYOFYEAR(childdob) > DAYOFYEAR(NOW()), YEAR(NOW()), YEAR(NOW()) + 1), DATE_FORMAT(childdob, '-%m-%d')) AS nextBD FROM children) AS sub ON c.id = sub.idWHERE sub.nextBD BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 20 DAY)[/code] Quote Link to comment Share on other sites More sharing options...
proctk Posted December 13, 2006 Author Share Posted December 13, 2006 Gave what you posted a try and below is the message I get and the code that I'm working with. Is there anything that I need to change to match column names in my tablethank you for the helperror message[b]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM children AS c JOIN ( SELECT id, CONCAT(IF(DAYOFYEAR(c[/b][code=php:0] $get_childdob = mysql_query("SELECT FROM children AS c JOIN (SELECT id, CONCAT(IF(DAYOFYEAR(childdob) > DAYOFYEAR(NOW()), YEAR(NOW()), YEAR(NOW()) + 1), DATE_FORMAT(childdob, '-%m-%d')) AS nextBD FROM children) AS sub ON c.id = sub.id WHERE sub.nextBD BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 20 DAY")or die (mysql_error()); [/code] Quote Link to comment Share on other sites More sharing options...
artacus Posted December 13, 2006 Share Posted December 13, 2006 What version of mysql are you using? If you are on v4, it doesn't do subqueries. Quote Link to comment Share on other sites More sharing options...
proctk Posted December 13, 2006 Author Share Posted December 13, 2006 version 4.0.27 - I just called my provider and that is what they told me. is this good enough, if not have any recommendations for web posting providers that supports what I'm trying to do Quote Link to comment Share on other sites More sharing options...
proctk Posted December 14, 2006 Author Share Posted December 14, 2006 Well, I did some more testing on my local server which is setup using xampp 1.5.5 and runs mysql 5.0.27. i still get the same message. so in light of this I will post all the code on this page[code=php:0]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/internalsite.dwt" codeOutsideHTMLIsLocked="false" --><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><!-- InstanceBeginEditable name="doctitle" --><title>email members</title><!-- InstanceEndEditable --><!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable --><link href="../css/main.css" rel="stylesheet" type="text/css" /></head><body><div id="outer"><div id="banner"> <h1><a href="index.php" title="Home Page">FamilyClick.ca</a></h1></div> <div id="sub"> <div id="center"><!-- InstanceBeginEditable name="EditRegion3" --> <form method="post" action="codesendmembersemail.php"><table class="columntable"><tr><td class="tableheader">Send Email to all Members</td></tr><tr><td class="border" style="padding-bottom:10px;">Title or Subject: <input name="subject" type=text maxlength=100 size=40></td></tr><tr><td class="border">Message:<textarea wrap name="message" rows=10 cols=40></textarea><input type=submit name="submit" value="SUBMIT"></td></tr></table> </form> <!-- InstanceEndEditable --> </div> <div id="left"><!-- InstanceBeginEditable name="EditRegion4" --> <?php include('../leftlinks.php'); ?> <!-- InstanceEndEditable --></div> </div> <div id="right"><!-- InstanceBeginEditable name="EditRegion5" --> <p> <?php include ('../config/db.php'); $get_childdob = mysql_query("SELECT FROM children AS c JOIN (SELECT id, CONCAT(IF(DAYOFYEAR(childdob) > DAYOFYEAR(NOW()), YEAR(NOW()), YEAR(NOW()) + 1), DATE_FORMAT(childdob, '-%m-%d')) AS nextBD FROM children) AS sub ON c.id = sub.id WHERE sub.nextBD BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 20 DAY")or die (mysql_error()); while($row_get_childdob=mysql_fetch_assoc($get_childdob)){$fname = $row_get_childdob['childfirstname'];$lname = $row_get_childdob['childlastname'];$owner_id = $row_get_childdob['owner_id'];$childdob = $row_get_childdob['childdob'];echo $name; if(isset($_POST['childdob_submit'])){ $subject = "Birthday Reminder from Family Click"; $message = " $fname lname birth is on $chiddob. be sure to give them a call or send them a message wishing them a Happy Birthday. Visit www.familyclick.ca to get their conact information and check out their gift wish list. This message was sent to you by Family Click This is an automated message, please do not reply!"; $x = 1; $hold = 50; // quantity of emails sent before 3 sec delay $query_email_owner = mysql_query("SELECT email_address FROM users WHERE user_id = '$owner_id'"); $email_count=mysql_num_rows($query_email_owner); while($row_email_owner=mysql_fetch_assoc($query_email_owner)){ $email_address = $row_email_owner['email_address']; mail($email_address, $subject, $message, "From:FamilyClick.ca <kevin.proctor@familyclick.ca>"); $x++; if($x == $hold) { // When $x is equal to $hold, a 3 sec delay will occur avoiding php to timeout sleep(3); $x = 0; } // end of while loop } } } ?> </p> <form id="form1" name="childrenbday" method="post" action="<?php $PHP_SELF ?>"> <p style="text-align:center; margin-top:10px;"><input style="width:90%; height:30px; text-align:center;" type="submit" name="childdob_submit" value= "Children Birthdays <?php echo $childdob_count; ?>" /></p> </form> <p> </p> <!-- InstanceEndEditable --> <p> </p> <p> </p> </div> <div id="footer"> <p>Copyright © 2006 familyclick.ca</p> <ul title="footer menu"> <li><a href="#" title="">Top</a></li> <li><a href="#" title="">Privacy Policy</a></li> <li><a href="#" title="">Site Map</a></li> </ul> </div></div></body><!-- InstanceEnd --></html>[/code] Quote Link to comment Share on other sites More sharing options...
artacus Posted December 14, 2006 Share Posted December 14, 2006 Haha, I can tell you what your problem is, you need to pick which fields to select (or pick *). I left that blank thinking you'd fill in whatever fields you needed. But the subquery wont work on your providers server. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.