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
LVL 29
sammySeltzerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BradyPrincipal Data EngineerCommented:
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.
0
sammySeltzerAuthor Commented:
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.
0
Mark BradyPrincipal Data EngineerCommented:
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
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

sammySeltzerAuthor Commented:
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.
0
Mark BradyPrincipal Data EngineerCommented:
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
0
sammySeltzerAuthor Commented:
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

0
Mark BradyPrincipal Data EngineerCommented:
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.
0
sammySeltzerAuthor Commented:
Ok enjoy your  vacation but just two points in case you see before you leave or you check back on your return.

The If check has been removed.

I removed it right after my last post.

Second, I removed the entire echo line.

Thanks for trying.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerAuthor Commented:
I will to close this question due to lack of solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.