Mysql Error

[mic2100]

i have written this sql statement, i understand the code but am unsure y this doesn't work.



SELECT referrals.ReferralNum, referrals.CallbackDateAndTime, tblcallresult.CallResultDesc, referrals.AgentFK
FROM
referrals
INNER JOIN
tblcallresult ON (referrals.CallResultFK = tblcallresult.CallResultID)
WHERE
(referrals.AgentFK = %s)
AND
((referrals.CallResultFK = 6) OR (referrals.CallResultFK = 1))
AND (referrals.CallbackDateAndTime <= '%s')


It worked fine until i put in the last line which contains a date. (the god awful things)

this is the code that makes the date that i use in the above statement.


$date = date('Y-m-d 23:59:59');

and this is the error message that i receive when i run the page



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 '' at line 1

[Mhtml]

(referrals.AgentFK = %s)
Possibly here? No quotes?
Have you tried dumping the sql query to see it before it is executed?

[mic2100]

have tried that, taking out the quotes. it don't seem to matter, i still get the same error. I think it could be the 'date' and the way it is constructed. If any1 has any ideas please help.

[ronaldb66]

Correct me if I'm wrong, but don't MySQL and PHP work with different date/time formats?

It depends of course on how your CallbackDateAndTime column is defined, but you may have to convert the output of that date() function into MySQL date/time format; see Date and Time Functions (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) (search for "FROM_UNIXTIME(unix_timestamp)") for more information.

[PASHA_SANYAL]

Think that it's having the problem with the date itself in PHP. Can u try with taking account with both date and time while constructing $date..

[mic2100]

my CallbackDateAndTime is defined as

YYYY-mm-dd hh:mm:ss

the same as the format that i am trying check against

i am eally confused with these dates and times, everything works really well until i put them in the code.


<?php require_once('../Connections/Con_SJMDatabase.php'); ?>
<?php

session_start();
session_register($app_no, $userid);
$callresult = $_POST['rob_callresults'];
$date = date('Y-m-d H:i:s');
$firstphoned = $row_rs_callStatus['FirstPhoned'];
$notes = "";
$refID = $_POST['hidden_initials'];
$newHistoryNo = $row_rs_maxHistory['MAX(JobNotesNum)'] + 1;

$nextweek = date('Y-m-d H:i:s');

switch($callresult)
{

case 1:

$outcome = 15;
break;

case 2:

$outcome = 16;
break;

case 3:

$outcome = 17;
break;

case 4:

$outcome = 18;
break;

case 5:

$outcome = 19;
break;

case 6:

$outcome = 20;
break;

case 7:

$outcome = 18;
break;
}



//mysql_select_db($database_Con_SJMDatabase, $Con_SJMDatabase);

$appno_rs_callStatus = "null";
if (isset($_SESSION['app_no'])) {
$appno_rs_callStatus = (get_magic_quotes_gpc()) ? $_SESSION['app_no'] : addslashes($_SESSION['app_no']);
}
mysql_select_db($database_Con_SJMDatabase, $Con_SJMDatabase);
$query_rs_callStatus = sprintf("SELECT * FROM rreferralstatus RIGHT OUTER JOIN referrals ON (rreferralstatus.ReferralStatusNum = referrals.ReferralStatusFK) LEFT OUTER JOIN tbleagastatusgroup ON (referrals.EagaStatusGroupFKey = tbleagastatusgroup.EagaStatusGroupID) LEFT OUTER JOIN tblcallresult ON (referrals.CallResultFK = tblcallresult.CallResultID) LEFT OUTER JOIN tblusers ON (referrals.AgentFK = tblusers.UserID) LEFT OUTER JOIN tblsource ON (referrals.SourceFK = tblsource.fldSourceID) LEFT OUTER JOIN landlord ON (referrals.ReferralNum = landlord.referralIDFK) LEFT OUTER JOIN tblrejectreasongroup ON (referrals.RejectReasonFKey = tblrejectreasongroup.RejectReasonGroupID) WHERE ReferralNum = '%s'", $appno_rs_callStatus);
$rs_callStatus = mysql_query($query_rs_callStatus, $Con_SJMDatabase) or die(mysql_error());
$row_rs_callStatus = mysql_fetch_assoc($rs_callStatus);
$totalRows_rs_callStatus = mysql_num_rows($rs_callStatus);

$colname_rs_userInitials = "-1";
if (isset($_SESSION['userid'])) {
$colname_rs_userInitials = (get_magic_quotes_gpc()) ? $_SESSION['userid'] : addslashes($_SESSION['userid']);
}
mysql_select_db($database_Con_SJMDatabase, $Con_SJMDatabase);
$query_rs_userInitials = sprintf("SELECT * FROM tblusers WHERE UserID = %s", $colname_rs_userInitials);
$rs_userInitials = mysql_query($query_rs_userInitials, $Con_SJMDatabase) or die(mysql_error());
$row_rs_userInitials = mysql_fetch_assoc($rs_userInitials);
$totalRows_rs_userInitials = mysql_num_rows($rs_userInitials);

$search_rs_callStatus = "0";
if (isset($searchresult)) {
$search_rs_callStatus = (get_magic_quotes_gpc()) ? $searchresult : addslashes($searchresult);
}

[mic2100]

this is the code for the page mentioned

[ronaldb66]

Wow... if I show this one:
$query_rs_callStatus = sprintf("SELECT * FROM rreferralstatus
RIGHT OUTER JOIN referrals ON (rreferralstatus.ReferralStatusNum = referrals.ReferralStatusFK)
LEFT OUTER JOIN tbleagastatusgroup ON (referrals.EagaStatusGroupFKey = tbleagastatusgroup.EagaStatusGroupID)
LEFT OUTER JOIN tblcallresult ON (referrals.CallResultFK = tblcallresult.CallResultID)
LEFT OUTER JOIN tblusers ON (referrals.AgentFK = tblusers.UserID)
LEFT OUTER JOIN tblsource ON (referrals.SourceFK = tblsource.fldSourceID)
LEFT OUTER JOIN landlord ON (referrals.ReferralNum = landlord.referralIDFK)
LEFT OUTER JOIN tblrejectreasongroup ON (referrals.RejectReasonFKey = tblrejectreasongroup.RejectReasonGroupID) WHERE ReferralNum = '%s'", $appno_rs_callStatus);
to my DBA (DataBase Admin), he'll probably burst into tears...

Anyway: where are the references to date values you've show above? Also, try and show the values in $date and $nextweek to compare the formats; you'll probably get large integer values (Unix timestamps).