How do I modify this code to include date range?

[sonnieboy]

$fields = array(
    'projectTitle' => array('field' => 'b.BidTitle', 'searchType' => 'like'),
    'BidType' => array('field' => 'b.BidType', 'searchType' => 'equal'),
    'BidStatus' => array('field' => 'b.BidStatus', 'searchType' => 'equal'),
    'department' => array('field' => 'b.AliasID', 'searchType' => 'equal'),
    'bidId' => array('field' => 'b.BidID', 'searchType' => 'like'),
    'txtFromDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'txtToDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'bidDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'dueDate' => array('field' => 'b.DueDate', 'searchType' => 'equal')

);

$where = array();
$searchType = "";
foreach($fields as $fieldPost => $field) {
    if(isset($_POST[$fieldPost]) && strlen($_POST[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_POST[$fieldPost]) . "%'";
        } else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
        $searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_POST[$fieldPost];
    }
    if(isset($_GET[$fieldPost]) && strlen($_GET[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_GET[$fieldPost]) . "%'";
        } else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_GET[$fieldPost]) . "'";
        }
        $searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_GET[$fieldPost];
        echo $searchType;
    }
}

//Then search:
	$sql = " SELECT c.* FROM (
		SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY b.BidDate DESC) AS RowID,b.ID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate,b.BidTitle,b.DueTime,b.BidID,b.BidIDFile,
          da.DeptAlias,b.BidType,CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe convert(char(10),b.AwardDate,101)END AS AwardDate,
          CONVERT(VARCHAR(11), b.LastUpdate, 106) LastUpdate,s.Status
          FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID inner join Dept d on da.DeptCode =d.DeptCode inner join status s on b.BidStatus=s.StatusId " . ( count($where) > 0  ? " WHERE b.BidDate > DATEADD(year,-7,GETDATE()) AND " . implode(' AND ', $where) : " " ) . "
          ) AS c
         WHERE c.RowID > $row_start AND c.RowID <= $num_rows
         ";

Dear all,

 

The code below allows users to dynamically search our database.

 

In other words, a user could search by one or more parameters.

 

I had the code working great until yesterday when I was asked to add date range so user would have the ability to search by date range or specific date.

 

I am having difficulty making the date range work.

 

Any ideas what I need to change to get the date range to work?

 

The dates in question are txtFromDate and txtToDate.

 

Thanks much for your assistance.

 

Relevant code is below:

 

 

[Barand]

You could introduce a couple of new search types into your array

• gte (greater than or equal to)
• lte (less than or equal to

[sonnieboy]

SELECT c.* FROM ( SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY b.BidDate DESC) AS RowID,b.ID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate,b.BidTitle,b.DueTime,b.BidID,b.BidIDFile, da.DeptAlias,b.BidType,CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe convert(char(10),b.AwardDate,101)END AS AwardDate, CONVERT(VARCHAR(11), b.LastUpdate, 106) LastUpdate,s.Status FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID inner join Dept d on da.DeptCode =d.DeptCode inner join status s on b.BidStatus=s.StatusId WHERE b.BidDate > '04/05/2015' ) AS c WHERE c.RowID > 0 AND c.RowID <= 5 

foreach($fields as $fieldPost => $field) {
    if(isset($_POST[$fieldPost]) && strlen($_POST[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_POST[$fieldPost]) . "%'";
        } elseif ($field['searchType'] == 'gte') {
            $where[] = "".$field['field']." > '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
          elseif ($field['searchType'] == 'lte') {
            $where[] = "".$field['field']." <= '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
          else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
        $searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_POST[$fieldPost];
    }
    if(isset($_GET[$fieldPost]) && strlen($_GET[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_POST[$fieldPost]) . "%'";
        } elseif ($field['searchType'] == 'gte') {
            $where[] = "".$field['field']." > '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
          elseif ($field['searchType'] == 'lte') {
            $where[] = "".$field['field']." <= '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
          else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
        $searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_GET[$fieldPost];
        echo $searchType;
    }
}

Guru,

 

Thank you for your suggestion. I am very close but here are the changes I made and the results I am getting:

 

For some reason, I tested by entering  b.bidDate (txtFromDate) which is  01/03/2015 and (txtToDate) which is 04/05/2015.

 

It is displaying WHERE b.bidDate > 04/15/2015.

What do I tweak to get it working correctly.

[Psycho]

You'll need to format the dates into the format the the database recognizes: YYYY-MM-DD

 

 

$sqlDate = date('Y-m-d', strtotime($inputDate));

[sonnieboy]

Thanks for that but it won't resolve the outstanding issue I currently have.

 

For instance, instead of WHERE b.bidDate >= '03/03/2015' and b.bidDate <='04/15/2015'; '03/03/2015' being txtFromDate and '04/15/2015' being txtToDate, it just displays WhERE b.bidDate > '04/15/2015' -> txtToDate.

 

Any ideas how to fix that?

[sonnieboy]

Can anybody please help?

[Psycho]

I'm not sure what is not understood by my post. Your query is currently using something such as 

 

$where[] = "".$field['field']." <= '" . ms_escape_string($_POST[$fieldPost]) . "'";

 

Well, a date in MySQL is stored in the format of YYYY-MM-DD. I provided sample code on how to format a date from mm/dd/yyyy into the format needed for a DB query. You now need to add logic to your code to determine which user submitted values are dates and then format them appropriately.

 

Personally, I think trying to create automated processes to dynamically create a query such as the above are not worth the effort. You have to keep adding logic to handle differences in field types (such as dates) and in the end it becomes more code.

[sonnieboy]

MY issue though is that I am using SQL Server database, not MySQL

[sonnieboy]

Thank you very much.

 

It is working great.

 

Your help is truly appreciated.