I am developing a dynamic web page using Drupal 7. I ran into a very strange problem. I have reduced my problem to a very small test case as follows:
The database side: I am using MySQL. I have two tables as defined here with a few sample data entries:
CREATE TABLE people (
pid INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
);
INSERT INTO people VALUES (NULL, 'Joe');
INSERT INTO people VALUES (NULL, 'Ant');
INSERT INTO people VALUES (NULL, 'Tom');
CREATE TABLE event (
eid INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pid INT(6) UNSIGNED NOT NULL,
event_desc VARCHAR(20) NOT NULL,
event_date DATE NOT NULL
);
INSERT INTO event VALUES (NULL, 1, '1p', '2015-10-01');
INSERT INTO event VALUES (NULL, 2, '1p', '2015-10-12');
INSERT INTO event VALUES (NULL, 2, '2p', '2015-10-00');
INSERT INTO event VALUES (NULL, 2, '3p', '2015-00-00');
INSERT INTO event VALUES (NULL, 3, '1p', '2010-07-18');
INSERT INTO event VALUES (NULL, 3, '2p', '2010-09-00');
Note the only interesting feature here is that the event table may contain incomplete event date in the format of '2015-10-00' when date is unknown and '2015-00-00' when both date and month are unknown. I understand this is legal in MySQL.
The Drupal 7 side: I create a custom module as follows:
<?php
/**
* Implements hook_menu().
*/
function test_menu() {
$items['test'] = array(
'type' => MENU_NORMAL_ITEM,
'title' => 'Test',
'description' => 'Test',
'page callback' => '_test_page',
'access callback' => TRUE,
'menu_name' => 'main-menu',
);
return $items;
}
function _test_page() {
$output = "";
$sql = "SELECT event.event_desc, event.event_date, people.name FROM event, ";
$sql .= "people WHERE event.pid=people.pid ORDER BY people.name";
$result = db_query($sql);
foreach ($result as $data) {
$output .= $data->name. " ". $data->event_desc. " ". $data->event_date. "<br>
";
}
return $output;
}
Note that I create a page with a menu item 'Test'. The page performs a query and displays the result. Now the very strange part: when the event date is incomplete, the query gets the wrong data '0000-00-00' as shown in the screen capture below:
Some analysis:
- This does not seem to be a problem with my SQL, because when I try the identical query from mysql command line I can get the correct result.
- This only happens when I join the two tables. If I simply query the 'event' table, I can get the correct result display in that Drupal page.
- When passing the SQL from a standalone PHP script to the database via PDO, there's no problem.
- I learned from Drupal's forum that the Date type is not used in Drupal core. So it may or may not be well supported.
Because the debugging isn't straight-forward to do, I decided not to pursue it any longer. The simplest way around the bug was to use VARCHAR(10) to store the Date information. By making that simple change, my web page can function properly. Of course I would lose the ability to use any Date-related functions.
