[SOLVED] How filter results by date range?

Hello everyone! How are you?

If someone could help me on this:

I need to filter the results in CC, where I could define a range of two dates, so it returns all the results between these dates.

Here's my actual WHERE SQL code:

<?php
$status = JRequest::getString('status', '', 'post');
$tecnico = JRequest::getString('tecnico', '', 'post');
$periodo = JRequest::getString('periodo', '', 'post');
$enquadramento = JRequest::getString('enquadramento', '', 'post');
$nome_solicitante = JRequest::getString('nome_solicitante', '', 'post');
$dt_limite = JRequest::getString('dt_limite', '', 'post');
$dt_solicitacao = JRequest::getString('dt_solicitacao', '', 'post');
$search_array = array('nome_solicitante', 'status', 'tecnico', 'enquadramento', 'dt_solicitacao');
$where = array();
foreach ( $search_array as $search ) {
	  $value = JRequest::getString($search, '' , 'post');
	  if ( $value ) {
		$where[] = " $search LIKE '%$value%' ";
	  }
}
if ( !empty($where) ) {
  echo " WHERE ".implode(' AND ', $where);
  if($dt_limite){
	echo "AND `dt_solicitacao` BETWEEN '$dt_solicitacao' AND '$dt_limite'";
  }
}

if($periodo == "antigas"){
	echo " ORDER BY `dt_solicitacao` ASC";
	}
else{
	echo " ORDER BY `dt_solicitacao` DESC";
}
?>

This give me no errors but also return no results, so please I ask for something to make it work properly.

Best regards,
~Aluizo Jr.

Hi Aluizo Jr,
What is the datatype of dt_solicitacao in your DB Table?

Also, looking at the logics, your date-check would only be applied if atleast one of your other search-fields are set. Even further, I see that one of the search-fields actually includes dt_solicitacao, which means that your range-clause would already be covered by the (narrower) dt_solicitacao check.

I'd probably rewrite the code somewhat like this:

<?php
  $status = JRequest::getString('status', '', 'post');
  $tecnico = JRequest::getString('tecnico', '', 'post');
  $periodo = JRequest::getString('periodo', '', 'post');
  $enquadramento = JRequest::getString('enquadramento', '', 'post');
  $nome_solicitante = JRequest::getString('nome_solicitante', '', 'post');
  $dt_limite = JRequest::getString('dt_limite', '', 'post');
  $dt_solicitacao = JRequest::getString('dt_solicitacao', '', 'post');
  $search_array = array('nome_solicitante', 'status', 'tecnico', 'enquadramento');
  $where = array();

  foreach ($search_array as $search)
  {
    $value = JRequest::getString($search, '' , 'post');
    if ($value)
    {
      $where[] = "$search LIKE '%$value%'";
    }
  }
  if (!empty($dt_solicitacao))
  {
    if (!empty($dt_limite))
    {
      $where[] = "`dt_solicitacao` BETWEEN '$dt_solicitacao' AND '$dt_limite'";
    } else
    {
      $where[] = "`dt_solicitacao` LIKE '%$dt_solicitacao%'";
    }
  }

  if (!empty($where))
  {
    echo " WHERE " . implode(' AND ', $where);
  }

  if ($periodo == "antigas")
  {
    echo " ORDER BY `dt_solicitacao` ASC";
  } else {
    echo " ORDER BY `dt_solicitacao` DESC";
  }
?>

/Fredrik

Hi Fredrik!!

dt_solicitacao it's the start date, and dt_limite is the end date of the range.

Thanks for your answer! I will check your tip now, friend. =)

Best regards,

~ Aluizo Jr.

Hi Aluizo Jr,
My first question was regarding the data type in your database, not the form data. If this is VARCHAR(...), comparisons such as "BETWEEN x AND y" will not do a date comparison, but a lexiographic (plain string) comparison - which will not yield the desired result. You'll have to make sure it's of type DATE or DATETIME to do such comparisons properly.

/Fredrik

Oh sorry Fredrik, the field is a timestamp type because:

I must preserve the real insert time of that row, and wasn't happening when I was editing the record. I was using the standard "recordtime" field, but when I edit it gets the current edit date and time.

So I googled around and found that if a field is timestamp, I can prevent it to change value on update of his row.

I'm still learning in that long road =D

Thanks!

~ Aluizo Jr.

Hi Aluizo Jr,
The TIMESTAMP type should work equally well, as long as you are aware of it's automated update properties.

/Fredrik

Oh yeah I'm aware, so I choosed TIMESTAMP because I can control the auto update options.
What I'm doing here is a helpdesk system using both CF and CC. Everything, besides the two dates filter, is working. But I like the possibilities which Chrono Engine provides.

I enabled the Joomla Debug to help me too. And I'm seeing the SQL output, altough sometimes it don't outputs real time, with each change in the WHERE SQL box.

I'll update this topic if solved anything.
Any ideas would be nice, if anyone wish to give =D


Thanks man!

Yeah, almost got it!!
Thanks Fredrik, somehow the solution was part of your conclusions.

I changed the query to be more friendly to me, but still with some conditions.

But what is wrong, it's the fact that when I fall in the condition of two dates submited, It don't returns eg. the records until today. But if I set the end date to tomorrow, it gives me the today records. So that's all left, any ideas? =DD

Following the code:

<?php
$status = JRequest::getString('status', '', 'post');
$tecnico = JRequest::getString('tecnico', '', 'post');
$periodo = JRequest::getString('periodo', '', 'post');
$enquadramento = JRequest::getString('enquadramento', '', 'post');
$nome_solicitante = JRequest::getString('nome_solicitante', '', 'post');
$dt_limite = JRequest::getString('dt_limite', '', 'post');
$dt_solicitacao = JRequest::getString('dt_solicitacao', '', 'post');

echo "WHERE nome_solicitante LIKE '%$nome_solicitante%'
AND status LIKE '%$status%'
AND tecnico LIKE '%$tecnico%'
AND enquadramento LIKE '%$enquadramento%'
";

if($dt_limite){
	echo "AND dt_solicitacao BETWEEN '$dt_solicitacao' AND '$dt_limite'";
}else{
	echo "AND dt_solicitacao LIKE '%$dt_solicitacao%'";
}

if($periodo == "antigas"){
	echo " ORDER BY dt_solicitacao ASC";
	}
else{
	echo " ORDER BY dt_solicitacao DESC";
}
?>

Hi Aluizo Jr,
That is due to the granuality of TIMESTAMP type, which are equivalent to the DATETIME type. As the name applies, this contains both date and time.
However, if you only supply a date (or two dates), for comparison, it is cast to a DATETIME with time being 0:00:00.

Thus, any entries for "today" would be similar to "2011-11-03 21:02:15". If you then were to look for records created yesterday and today, the ranges would be WHERE date BETWEEN '2011-11-02 0:00:00' AND '2011-11-03 0:00:00'. Unfortunately, our entry today occured past midnight, so it's outside of the range.

For MySQL, you could use some tricks such as the DATE_ADD() function to increase the date by one day:

...
      $where[] = "`dt_solicitacao` BETWEEN '$dt_solicitacao' AND DATE_ADD('$dt_limite', INTERVAL 1 DAY)";
...

/Fredrik

Yeah! It worked, thank you very much Fredrik! =D Hooraa!
Now it returns properly! I'll make some full tutorial soon based on your tips, on how to build a simple helpdesk-ish system.

Code below:

<?php
$status = JRequest::getString('status', '', 'post');
$tecnico = JRequest::getString('tecnico', '', 'post');
$periodo = JRequest::getString('periodo', '', 'post');
$enquadramento = JRequest::getString('enquadramento', '', 'post');
$nome_solicitante = JRequest::getString('nome_solicitante', '', 'post');
$dt_limite = JRequest::getString('dt_limite', '', 'post');
$dt_solicitacao = JRequest::getString('dt_solicitacao', '', 'post');

echo "WHERE nome_solicitante LIKE '%$nome_solicitante%'
AND status LIKE '%$status%'
AND tecnico LIKE '%$tecnico%'
AND enquadramento LIKE '%$enquadramento%'
";

if($dt_limite){
	echo "AND dt_solicitacao BETWEEN '$dt_solicitacao' AND DATE_ADD('$dt_limite', INTERVAL 1 DAY)";
}else{
	echo "AND dt_solicitacao LIKE '%$dt_solicitacao%'";
}

if($periodo == "antigas"){
	echo " ORDER BY dt_solicitacao ASC";
	}
else{
	echo " ORDER BY dt_solicitacao DESC";
}
?>

Thanks mr!!
o/

Hello, I want to do something similar, what did you use to select the dates?? can you help me please

Hi there servf,

Sorry for the late answer, but I got the tip from here

I put the same calendar from CF in the CC, following the procedures in the link above.
You need to check your date format, and if there's any MooTools conflict.
If any doubt, don't hesitate to ask.

Regards,
~ Aluízo Jr.