close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

DA SEARCH - currency

Thread began 10/28/2011 2:44 pm by Steve | Last modified 10/31/2011 12:10 pm by Steve | 1499 views | 8 replies |

Steve

DA SEARCH - currency

Hello,
I have a search that uses Min and Max but it seems there is a problem.

I see that the default helper file uses:

//strips currency markup
function WADS_stripCurrency($currencyField) {
if (isset($currencyField) && $currencyField != "undefined" && $currencyField != "") {
if (is_numeric($currencyField)) {
return $currencyField;
}
else {
return preg_replace("/[^\d\.]/", "", $currencyField);
}
}
return "";
}



but I'm thinking there needs be a string_replace to remove commas?


For instance one of my values is 42,500.00

Searching 40000 - 50000 will not return a result. I'm thinking it's the comma. Is this true?

Sign in to reply to this post

Steve

I added the string replace function but still a No-Go..

php:
//strips currency markup

function WADS_stripCurrency($currencyField)     {
  if (isset($currencyField) && $currencyField != "undefined" && $currencyField != "")     {
    if (is_numeric($currencyField))     {
      return $currencyField;
    }
    else {
      return preg_replace("/[^\d\.]/", "", $currencyField);
      return str_replace(",", "", $currencyField);
    }
  }
  return "";
}



echoing back the $currencyField value on the search page is not showing this present so maybe that was the wrong variable to be looking to manipulate?

Here is the search query string and results of echoing $currencyField:


SELECT tblhouseinfo.BedRooms, tblhouseinfo.BathRooms, tblhouseinfo.list_price, tblinventory.* FROM tblhouseinfo JOIN tblinventory ON tblinventory.invId = tblhouseinfo.addInfoID WHERE tblinventory.mid = 10 AND (list_price >= 24000) AND (list_price <= 45000) First Previous Next Last


Notice: Undefined variable: currencyField in E:\Websites\......\www\htdocs\results.php on line 209

Sign in to reply to this post

Steve

I just realized that "WADS_stripCurrency" actually only works on the user submitted value that is being used as INPUT, not changing the values returned from the QUERY to the DB...

Need to change the DB values on the fly to strip the MySQL Currency formatting (commas) before the Search comparison occurs...

Sign in to reply to this post

Steve

ok, Discard prior discussions and questions to this point as I can use MySQL function "replace()" to strip commas in the values available to the Search Comparison.

For example:

SELECT tblhouseinfo.BedRooms, tblhouseinfo.BathRooms, replace( tblhouseinfo.list_price , ',' , '') AS list_price, tblinventory.* FROM tblhouseinfo JOIN tblinventory ON tblinventory.invId = tblhouseinfo.addInfoID

returns the results shown in the attached screen shot.

But when the Search comparison is run, no results are returned:

SELECT tblhouseinfo.BedRooms, tblhouseinfo.BathRooms, replace( tblhouseinfo.list_price , ',' , '') AS list_price, tblinventory.* FROM tblhouseinfo JOIN tblinventory ON tblinventory.invId = tblhouseinfo.addInfoID WHERE tblinventory.mid = 10 AND (list_price >= 24000) AND (list_price <= 45000)

What as I missing? Greater or Equal to 24000 and Less than or Equal to 45000 should return records: 44900 and also 42500.00 Right??

Sign in to reply to this post

Jason ByrnesWebAssist

you need to be using the replace function in the where clause, by using it in the select clause, you are only effecting the output data.

Keep in mind that the Data engine that executes the SQL Statement does not execute the query in the same order it is written

First it looks at the FROM statement, including the INNER JOINS, then filters the data from all of the table included using the WHERE clause, finally it looks at the select clause and returns only those columns.

this is why the where clause can act on columns that are not included in the select clause.


SELECT tblhouseinfo.BedRooms, tblhouseinfo.BathRooms, tblhouseinfo.list_price, tblinventory.* FROM tblhouseinfo JOIN tblinventory ON tblinventory.invId = tblhouseinfo.addInfoID WHERE tblinventory.mid = 10 AND (replace(list_price, ',' , '') >= 24000) AND (replace(list_price, ',' , '') <= 45000)


should give the result you are after.

Sign in to reply to this post

Steve

Perfect! Thanks Jason...

Now how to edit the Where clause in the Search extension? Will I have to manually hack this in place?

Sign in to reply to this post

Jason ByrnesWebAssist

Send a copy of the results page and can show you what needs to be edited.

Sign in to reply to this post

Steve

This post has been deleted.

Jason ByrnesWebAssist

edit line 39 and 40:

php:
$WADbSearch1->addComparison("list_price","".WADS_stripCurrency($_POST["start_price"])  ."","AND",">=",1);

  $WADbSearch1->addComparison("list_price","".WADS_stripCurrency($_POST["end_price"])  ."","AND","<=",1);




to:

php:
$WADbSearch1->addComparison("replace(list_price, ',' , ''","".WADS_stripCurrency($_POST["start_price"])  ."","AND",">=",1);

  $WADbSearch1->addComparison("replace(list_price, ',' , ''","".WADS_stripCurrency($_POST["end_price"])  ."","AND","<=",1);
Sign in to reply to this post

Steve

Thanks Jason!!

Added a parenthesis and it worked perfectly.

php:
$WADbSearch1->addComparison("replace(list_price, ',' , '')","".WADS_stripCurrency($_POST["start_price"])  ."","AND",">=",1);

  $WADbSearch1->addComparison("replace(list_price, ',' , '')","".WADS_stripCurrency($_POST["end_price"])  ."","AND","<=",1);
Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...