Jump to content

MySQLi query with date range criteria


josephbupe

Recommended Posts

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.

 

Link to comment
Share on other sites

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']);
}
Link to comment
Share on other sites

  • 1 month later...

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 by josephbupe
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.