asked on

How do I split querystring values?

Ok, this problem is probably not for the faint hearted.

Some of you php gurus are probably familiar with this project by now but this appears to be the last big challenge.

We use dynamic WHERE clause to pass form values to server side code.

Those values are used appropriately in our query.

This works fine.

The big challenge is to export data to a .csv file based on the results from the WHERE clause.

I have been able to work out most of the issues.

However, the issue that I am stuck with is how to export data to .csv file when the WHERE clause involves more than one fieldnames.

Attached is an exact querystring values from the where clause.

The where clause in the attached screenshot is stypes which has two form fields , BidStatus and department with respective values.

How do I split these up into two in the destination .php page?

I had similar situation with FromDate and EndDate and I was able to get them to work with the following codes:

 $start = $_REQUEST["start"];
 $end = $_REQUEST["end"];
 $strTypes = $_REQUEST['stypes'];

$Types = explode("=", $strTypes);
$Typesd = str_replace("&txtToDate", "", $Types[1]);
$Typesdd = str_replace("&txtToDueDate", "", $Types[1]);

Open in new window

Then the query:

     if ((strpos($strTypes, 'txtFromDueDate') !== false) && (strpos($strTypes, 'txtToDueDate') !== false)) {
       	$sql = " SELECT c.* FROM (
   			SELECT ROW_NUMBER() OVER(ORDER BY b.ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime,  b.BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS 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.DueDate BETWEEN '".ms_escape_string($Typesdd)."' AND '".ms_escape_string($Types[2])."'
   			) AS c
   	";
   }

Open in new window

Not sure if that was the best approach but it worked and the reason it worked was because date values are same length.

I tried similar approach with BidStatus and department but that didn't work because department could be one digit, two digits, digits or even 4 digits.

Any ideas how to solve this?

Thanks in advance
dynamicWHERE.jpg

Mark Brady

My first question is do you have any control over how the query string is formed because that is not a normal way of doing it. A better approach if you have multiple values fir a key (field name) is to comma separate them like this:
a&stypes={"Bidstatus":"2","department":"4"}

That way you can grab to $_GET['stypes'] which you know to be a valid JSON and decode it to get the key/value pairs.
Your key names (fields) should also follow the name naming convention like camel case or all lowercase and not be mixed like yours are.

In saying that, if you don't have control over these things then I will work something out after lunch for you.

sammySeltzer

ASKER

Great question Mark.

Unfortunately, I don't.

I think I bit more than I could chew on this project.

I am normally a .net guy coming form classic asp.

When I was deciding on how to code the dynamic WHERE clause, I had done it several times with .net and classic asp but could not figure out a way to do it with php.

So, I came up with this:

$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' => 'gte'),
    'txtToDate' => array('field' => 'b.BidDate', 'searchType' => 'lte'),
    'txtFromDueDate' => array('field' => 'b.DueDate', 'searchType' => 'gte'),
    'txtToDueDate' => array('field' => 'b.DueDate', 'searchType' => 'lte'),
    'bidDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'dueDate' => array('field' => 'b.DueDate', 'searchType' => 'equal')

);

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

Open in new window

Actually, a guy Brian Tao actually helped clean it up a bit.

This is then used in my query:

          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 " . implode(' AND ', $where) : " " ) . " ORDER BY $sort $order

Open in new window

Notice the $searchType;

Then to pass that along with some additional fields via querystring, I used this:

 echo "<br /><br /><a class='btn btn-default' href='exportsw.php?start=".urlencode($row_start)."&end=".urlencode($num_rows)."&stypes=".urlencode($searchType)."'>Export to CSV</a>";

Open in new window

I don't mean to confuse you with more codes but just to give you a little background of how I arrived at the situation I am in right now.

Thanks a lot for your hope.

I have asked my peers and users to test thoroughly instead before giving me a feedback because I thought I had everything working until they discovered this.

Everything the $fields = array(...) above including exporting the values to .csv file is working correctly except when you combine two or more files.

For instance, you select a department and you select status of a bid as in the current issue, that's when you run into trouble with exporting to .csv file.

I have spent two days trying to figure it out on my own but running to mental blocks.

Mark Brady

Interesting way to approach it but let's see if I can help. Firstly, parameters passed in a URL are available in the $_GET variable in php. Although you are using $_REQUEST which will grab $GET and $_POST it is considered a security risk. If you are using a query string change your php to use $_GET instead - safer.

Now, looking at your query string it is not a normal one so we need to split it up somewhat to grab the parts. Here is a small example of how to do that.

// consider the URL :  ?stypes=BidStatus%3D2%26department%3D4    

$stypes = $_GET['stypes']; // gives you the string value of styles in the URL

echo $stypes; // BidStatus=2&department=4

// split that string at the '&' 
$parts = array_map('trim', explode('&', $stypes));

// now split each part of that array by the '=' to get the key/values
foreach ($parts as $part) {
	$keyvals = array_map('trim', explode('=', $part));
	echo $keyvals[0] . ' = ' .$keyvals[1]."\n";
}

Open in new window

That should print out: BidStatus = 2 department = 4

That should give you an idea on how to parse it but it is still not a great way to do things (keeping in mind you have no control over the query string)

Hope this helps

sammySeltzer

ASKER

Yes, it gives me something similar to BidStatus = 2 department = 4

*But* how do I then use it in my where clause below?

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 ?

When I tried fitting it into my code with this:

             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.BidStatus = '".ms_escape_string($$keyvals[0])."' AND b.AliasID = '".ms_escape_string($$keyvals[1])."'

Open in new window

I get this when I echo the query:

where b.AliasID = '1&department'

Open in new window

AliasId is deptID

Sorry if this is too elementary and thanks a lot for your time and help.

Mark Brady

Ok I see the issue. You have got your single and double quotes around the wrong way. Also remove one of the '$' from both of the $keyvals variables. The query should print out (echo it) something like this:

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.BidStatus = "department" AND b.AliasID = "4"

I would use the quotes like this:

$query = "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.BidStatus = '".$keyvals[0]."' AND b.AliasID = '".$keyvals[1]."';

Open in new window

I prefer to use double quotes on the outside of a query and single quotes to denote values inside the query. Try this and see if it works

sammySeltzer

ASKER

Not working.

Just to be sure I am following you correctly:

$stypes = $_GET['stypes']; // gives you the string value of styles in the URL

// split that string at the '&'
$parts = array_map('trim', explode('&', $stypes));

// now split each part of that array by the '=' to get the key/values
foreach ($parts as $part) {
	$keyvals = array_map('trim', explode('=', $part));
	 $keyvals[0] . ' = ' .$keyvals[1]."\n";
}

Open in new window

//Then the query, exactly the way I am using it:

     if($stypes = 'BidStatus' && $stypes = 'department'){
       	$sql = " SELECT c.* FROM (
   			SELECT ROW_NUMBER() OVER(ORDER BY b.ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime,  b.BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS 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.BidStatus = '".$keyvals[0]."' AND b.AliasID = '".$keyvals[1]."'
   			) AS c
   	";
 }

Open in new window

Mark Brady

Several things wrong here. Firstly remove line 9 from your first post. You removed the word "echo" from my example but really that was only to show you the values are correct. You can remove that entire line.

Finally,
if($stypes = 'BidStatus' && $stypes = 'department'){

This block of code will never evaluate. The value assigned to $stypes is 'BidStatus=2&department=4' and you are asking if it is 'BidStatus' AND if it is 'department'

It can't be both. No need to do a check for anything here because it has already been assigned above. There are many checks you should do with any data coming from a form and I don't have time to give you code to beef up your security right now, however just go ahead and remove line 1 and 7 from your final post.

Unfortunately I have to take off on vacation. I am hoping you get it working.

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sammySeltzer

ASKER

I will to close this question due to lack of solution.