encoding fields

[sonnieboy]

Hello gurus,

 

I have the following url with querystring values:

 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>";

Please take note of  style=.'"urlencode($searchType) ."'
 

 

The values from the querystring is passed to exportsw.php using the following:

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

SELECT * FROM bids b where b.BidStatus = '".$keyvals[0]."' AND b.AliasID = '".$keyvals[1]."' 

URL // split that string at the '&' $parts = array_map('trim', explode('&', $stypest)); // now split each part of that array by the '=' to get the key/values foreach ($parts as $part) { $keyvals = array_map('trim', explode('=', $part)); }

 

When I run my app, the values passed to exportsw.php look like:  

 

BidStatus =  3 department =  77

 

BidStatus and department are form fields, not database fields.

 

The database field name for BidStatus is BidStatus and the database fieldname for department is AliasID.

 

I will like to encode each field separately so that when I run my query, I will like the query to look like this:

 

Select * from myTable where b.BidStatus = '4' AND b.AliasID = '77'

 

Right now when I the following query:

 

SELECT * FROM bids b. where b.BidStatus =  .'" $keyvals[0] "'. AND b.AliasID = .'" $keyvals[1] "'.

 

It displays following on echo:

 

SELECT * FROM bids b where b.AliasID = 4&department

 

This is wrong but I believe that encoding the fields separately will solve the problem but I am not sure how to do this.

 

Any help is greatly appreciated.

 

[Barand]

 

I will like to encode each field separately so that when I run my query, I will like the query to look like this:

 

Select * from myTable where b.BidStatus = '4' AND b.AliasID = '77'

No, you don't want it to look like that. You should not use "SELECT * " and numeric values should not be quoted.

 

What is the relationship between "start" and "end" that you pass to your script via the URL and "bidStatus" and "department"

[Muddy_Funster]

have you tried using a var_dump() or print_r() on your $keyvals array to see what's actualy in it at each index?

[sonnieboy]

Ok, sorry for the mis-information.

 

The two fields I would like to encode separately are BidStatus and department.

 

For some reason, the following code didn't format correctly:

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

// now split each part of that array by the '=' to get the key/values 
foreach ($parts as $part) 
{ 
$keyvals = array_map('trim', explode('=', $part));
}

As stated originally, when I echo above code, the results displayed are BidStatus =  3 department = 77, exactly like that.

 

What I would like to do is in my query, find a way to remove BidStatus and department so that my query looks like this:

 

Select * from bids where b.BidStatus = 3 and b.AliasID = '77'

 

BidStatus is numeric but AliasID which is deptID is string.

 

I am not worried about $start and $end. Those work fine already.

 

I really appreciate you guys help.

 

Thank you

[Muddy_Funster]

and again....

have you tried using a var_dump() or print_r() on your $keyvals array to see what's actualy in it at each index?

[mac_gyver]

you would apply urlencode() to each value being put into $searchType as it is being built, not after the fact.

[sonnieboy]

 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>";

Hi Muddy,

 

Is this what you want to see?

 

I applied var_dump to get the following:

array(2) { [0]=> string(9) "BidStatus" [1]=> string(1) "1" } array(2) { [0]=> string(10) "department" [1]=> string(1) "2" }

Mac,

 

This is getting more and more complicated, I suppose.

 

I thought I was doing that with this code with url with querystring above?

[Muddy_Funster]

From the results of your var_dump you want $keyvals[0][1] and $keyvals[1][1] as your variables

 

SELECT * FROM bids b where b.BidStatus = '".$keyvals[0][1]."' AND b.AliasID = '".$keyvals[1][1]."'

it also means that your prevois atempts shouldn't have worked at all....

[sonnieboy]

Thanks a bunch Muddy.

 

This is what I am getting when I applied your solution:

 where b.BidStatus = 'e' AND b.AliasID = ''

//just to be certain I am passing everything correctly, I am including the rest of code where I am grabbing the values of $stypes:

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

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

// now split each part of that array by the '=' to get the key/values
foreach ($parts as $part) {
$keyvals = array_map('trim', explode('=', $part));

[Muddy_Funster]

right, that meens that at the point it get's to the query build it's not a 2D array like it said it was, make sure your vardump is directly before your query and try it again, somethings not adding up with that.

also,

$parts = array_map('trim', explode('&', $stypest)); //what does trim do?



// now split each part of that array by the '=' to get the key/values

foreach ($parts as $part)

{

$keyvals = array_map('trim', explode('=', $part)); // and why do you do it again here?
}
/*
consider changing the above to:
foreach($parts as $part){
$twoDeepKeyVals[] = explode("=",$part))
}
foreach($twoDeepKeyVals as $row){
$keyVals[$row[0]] = $row[1];
}
print_r($keyVals);

[sonnieboy]

    if($_GET["bidDate"] !== ''){
      	$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.BidDate = '".ms_escape_string($_GET["bidDate"])."'
  			) AS c
  	";
      }
     elseif($_GET["dueDate"] !== '' ){
       	$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 = '".ms_escape_string($_GET["dueDate"])."'
   			) AS c
   	";
   }
   elseif($_GET["BidStatus"] !==false && $_GET["department"] !== 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.BidStatus = '".ms_escape_string($_GET["BidStatus"])."' AND b.AliasID = '".ms_escape_string($_GET["department"])."'
   			) AS c
   	";
   }
else {
   echo "no data found";
    }

 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>";

Array ( [] => )

This is the printed results from your latest code:

 

Also, I made some changes to the link with querystring. I should have thought about this sooner. Here is the change:

 

 

Now, the querystring values for $searchType now looks like this:

 

 

exportsw.php?start=0&end=20&BidStatus=3&department=77

 

So, it doesn't matter what value you passed to $searchType, one field or 5 fields, you get that formfield=theValue.

 

I think this makes easier now to grab the values using the GET method.

For instance, I am now able to see the correct results when I grab the querystring values of BidStatus and department:

 

Select * from bids where b.bidStatus= .'" ms_escape_string($_GET["BidStatus"] ) ."' AND .'" ms_escape_string($_GET["department"]) ."'

 

However, while this works now, the rest of the code is not working.

For instance, the following are part of the query:

 

Can you please help me fix this part and we will be good to go?

 

Thanks a lot.

[Muddy_Funster]

You missed the bit that's not working...Although I see you are refferencing $_GET['bidDate'] and $_GET['dueDate'] and they arn't in the URL. Are they represented by $_GET['start'] and $_GET['end'] by chance?