0

I have write sql query in drupal looks like this.

Let's assume today's date is 2013-07-24 and total of node is 4.

    $query = "SELECT COUNT(*) amount FROM {node} n 
              LEFT JOIN {flagging} f ON n.nid = f.entity_id AND f.fid = '1'
              LEFT JOIN {field_data_field_tarikh_tamat_passport} ttp ON n.nid = ttp.entity_id AND (ttp.entity_type = 'node' AND ttp.deleted = '0')
              WHERE (( (n.status = '1') AND (n.type IN  ('data_pekerja')) AND (ttp.field_tarikh_tamat_passport_value > CURDATE()) AND (f.uid IS NULL ) ))";
$result = db_query($query)->fetch();
print $result->amount;

The output of code above is 3 but with this code below is 1

 $query = "SELECT COUNT(*) amount FROM {node} n 
                  LEFT JOIN {flagging} f ON n.nid = f.entity_id AND f.fid = '1'
                  LEFT JOIN {field_data_field_tarikh_tamat_passport} ttp ON n.nid = ttp.entity_id AND (ttp.entity_type = 'node' AND ttp.deleted = '0')
                  WHERE (( (n.status = '1') AND (n.type IN  ('data_pekerja')) AND (ttp.field_tarikh_tamat_passport_value > '2013-07-24') AND (f.uid IS NULL ) ))";
    $result = db_query($query)->fetch();
    print $result->amount;

The different of this sql query is only the value of CURDATE() and '2013-07-24'

Can you please explain why the output is different? I need the query to displaying total of node that has date field value greater than today.

  • Can we see a dump of those rows? – Chris Rockwell Jul 23 '13 at 19:18
0

Drupal usually stores dates as Unix timestamps, not regular date strings. Have a look at your field_data_field_tarikh_tamat_passport table to confirm which data format you're working with. If it's a timestamp, update your query to use UNIX_TIMESTAMP() instead of CURDATE().

  • the format is YYYY-MM-DD HH:MM:SS – Dieyy Jul 23 '13 at 19:37
  • I'm using date module for this field. – Dieyy Jul 23 '13 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.