By johnhanley on

I have the following query that works perfectly in phpMyAdmin, but Drupal reports an error at the top of the page. What's weird is the resulting recordset data actually displays correctly within the page, yet Drupal complains nevertheless. Is there a way to satisfy Drupal or suppress the error? I have wasted two hours trying to make Drupal happy and rechecking (and rechecking and rechecking) my syntax. Needless to say this is freakin' annoying.

SELECT s.serviceid, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%h:%i %p')) AS servicetime, ss.servicestatus, IFNULL('----', si.sitename) AS sitename
FROM service s
INNER JOIN servicestatus ss ON ss.servicestatusid = s.servicestatusid
LEFT JOIN site si ON si.siteid = s.siteid
WHERE s.claimid = 17

Comments

nevets’s picture

nevets commented

Can you post the error message, it might help someone come up with an answer.

johnhanley’s picture

johnhanley commented

The complete error message is as follows:

user error: 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_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate,
query: SELECT COUNT(*) FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%h:%i %p')) AS servicetime, ss.servicestatus, IFNULL('----', si.sitename) AS sitename
FROM service s
INNER JOIN servicestatus ss ON ss.servicestatusid = s.servicestatusid
LEFT JOIN site si ON si.siteid = s.siteid
WHERE s.claimid = 17 in /var/www/html/drupal/includes/database.mysql.inc on line 66.

nevets’s picture

nevets commented

Look at

FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y'))

I believe this should be

FROM_UNIXTIME(s.servicedate, '%a %b %e, %Y')

Also similar issue with DATE_FORMAT (which is not needed since you can pass format as 2nd argument to FROM_UNIXTIME)

johnhanley’s picture

johnhanley commented

...for the preceding IF statement.

Check this out. If you look at the error message again. Notice the query shown includes "SELECT COUNT(*)" which is not part of my query. Could this be somehow inserted by Drupal?

This is so weird. Again the query executes and returns the correct data, yet this annoying error message shows up.

user error: 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_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate, 
query: SELECT COUNT(*) FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%h:%i %p')) AS servicetime, ss.servicestatus, IFNULL('----', si.sitename) AS sitename
			FROM service s 
			INNER JOIN servicestatus ss ON ss.servicestatusid = s.servicestatusid
			LEFT JOIN site si ON si.siteid = s.siteid
			WHERE s.claimid = 17  in /var/www/html/drupal/includes/database.mysql.inc on line 66.
nevets’s picture

nevets commented

I was looking at

SELECT COUNT(*) FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y'))

which is not part of an IF statement and I just relized there is no comma after the COUNT(*).
What does the php code look like. I am wondering if the Drupal functions don't know how to handle the 'IF'

johnhanley’s picture

johnhanley commented

Nevets, thanks for your continued help in diagnosing this problem.

The actually code wrapping this query looks like this:

$header = array(array('data' => t('Date'), 'field' => 'servicedate', 'sort' => 'asc'),
array('data' => t('Time'), 'field' => 'servicetime'),
array('data' => t('Location'), 'field' => 'sitename'),
array('data' => t('Status'), 'field' => 'servicestatus'),
array('data' => t('Operations')));
					
$query = "SELECT s.serviceid, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%h:%i %p')) AS servicetime, ss.servicestatus, si.sitename
FROM service s 
INNER JOIN servicestatus ss ON ss.servicestatusid = s.servicestatusid
LEFT JOIN site si ON si.siteid = s.siteid
WHERE s.claimid = " . variable_get('claimid', 0) . tablesort_sql($header);

$result = pager_query($query, 20);

Your comment makes me believe that the issue might be related to page_query(). I am using the IF statement elsewhere in the same module with no problem, but NOT with page_query(). Thoughts?

johnhanley’s picture

johnhanley commented

Ok, it seems the developer of pager_query() WISELY anticipated this type of "complex query" and included an optional query to handle the record count needed by the function to determine the number of pages required.

I have revised my pager_query() call to include the optional count query:

$query = "SELECT s.serviceid, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%a %b %e, %Y')) AS servicedate, IF(s.servicedate = 0, '----', DATE_FORMAT(FROM_UNIXTIME(s.servicedate), '%h:%i %p')) AS servicetime, ss.servicestatus, si.sitename
FROM service s 
INNER JOIN servicestatus ss ON ss.servicestatusid = s.servicestatusid
LEFT JOIN site si ON si.siteid = s.siteid
WHERE s.claimid = " . variable_get('claimid', 0) . tablesort_sql($header);
$count_query = "SELECT COUNT(DISTINCT(serviceid)) FROM service s WHERE s.claimid = " . variable_get('claimid', 0);

$result = pager_query($query, 20, 0, $count_query);

Works perfectly. Read the pager_query documentation for full details. Shame on me for not going there earlier.

johnhanley’s picture

johnhanley commented

I have determined that the problem isn't the query, but rather with pager_query(). The function is confusing "FROM_" (as in FROM_UNIXTIME) with the "FROM" statement. If the function looks for "FROM " it should solve the problem.