MySQLi query with date range criteria

[josephbupe]

Hi,

 

I can I include a date range criteria to query with in the following code? The date field in the table (t_persons) is IncidentDate.

$criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' );
    $likes = "";
    $url_criteria = '';
    foreach ( $criteria AS $criterion ) {
            if ( ! empty($_POST[$criterion]) ) {
                    $value = ($_POST[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
            } elseif ( ! empty($_GET[$criterion]) ) {
                    $value = mysql_real_escape_string($_GET[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
            } //var_dump($likes);
    }
    $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";

Kind regards.

 

[mentalist]

Something like this...

...
WHERE (IncidentDate BETWEEN '2014-09-01 14:00:00' AND '2014-12-31 23:59:59')

[josephbupe]

Ok, now I need help to merge the following two pieces of code into a single block for my search criteria. The two codes are both IF statements and I am lost on how to make them work as a whole.

 

Actually, I have been using the first, but the second has been suggested to me:

$criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' );
    $likes = "";
    $url_criteria = '';
    foreach ( $criteria AS $criterion ) {
            if ( ! empty($_POST[$criterion]) ) {
                    $value = ($_POST[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
            } elseif ( ! empty($_GET[$criterion]) ) {
                    $value = mysql_real_escape_string($_GET[$criterion]);
                    $likes .= " AND `$criterion` LIKE '%$value%'";
                    $url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
            } //var_dump($likes);
    }
    $sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";

And the date range criteria code:

if (!empty($_REQUEST['start_date']) && !empty($_REQUEST['end_date'])) {
    $start = mysqli_real_escape_string($con, $_REQUEST['start_date']);
    $end = mysqli_real_escape_string($con, $_REQUEST['end_date']);
    $likes .= " AND IncidentDate BETWEEN '$start' AND '$end'";
    $url_criteria .= '&start_date='.htmlentities($_REQUEST['start_date']).'&end_date='.htmlentities($_REQUEST['end_date']);
}

[Ch0cu3r]

Add it before the last line maybe?

$sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";

[josephbupe]

Hi,

 

First, I wish to apologize for taking too long to acknowledge your responses to my earlier question above.

 

The suggested code did not return records between specified date range. Here is the query echo:

SELECT * FROM t_persons WHERE PersonID>0 AND IncidentDate BETWEEN '' AND '' ORDER BY p.PersonID DESC LIMIT 0,27

For clarification purpose, I want to know what these variables refer to:

 

1.  $start

2.  $end

 

I assume that:

 

start_date is the name of the field to input the first date

end_date is the name of the field to input the second date

 

I appreciate your further assistance.

 

Joseph

Edited October 7, 2014 by josephbupe

[mac_gyver]

the reason the two values are empty in the sql statement is because the code is using mysqli_real_escape_string() and you likely don't have a mysqli database connection, resulting in null values.

 

if php's error reporting was on, you would be getting php errors alerting you to this problem. you need to have php's error_reporting set to E_ALL and display_errors set to ON in the php.ini on your development system to get php to help you. you will save a ton of time.

 

you cannot mix mysql_ (no i) and mysqli_ (with an i) functions. you must use all the same functions that match the type of database connection statement you used.

 

however, the mysql_ functions, which is what you have shown in the first code in this thread, are obsolete and you should not be using them when writing new code. you should be using either the mysqli_ or PDO database functions.

[josephbupe]

Looking into it.

 

Thanx alot.