Trying to filter currency, but never shows results.

mrbranden

I'm trying to filter my results using "minprice" and "maxprice" fields. For simplicity sake, I'm starting off with just minprice.

Unfortunately, no matter what value I give it, it shows no results. Here's my code:

[code] $(document).ready(function() {
var oTable = $("#results_table").dataTable({
"bJQueryUI": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource":"get_listings.php",
"fnServerData":function (sSource, aoData, fnCallback) {
aoData.push({"name":"city", "value":$('input.cityorzip').val(),
"name":"minprice", "value":$('input.minprice').val() });
$.getJSON( sSource, aoData, function(json) {
fnCallback(json)
});
}
});

$("#cityorzip").keyup(function() {
oTable.fnFilter(this.value);
});

});
[/code]

And here's a snippet of my PHP code that is probably relevant:

[code]
$sWhere = " WHERE price > '%".mysql_real_escape_string( $_GET['minprice'])."%'";

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS id, price, listingcity, numbedrooms, numfullbaths, numhal
fbaths
FROM new_listings
$sWhere
$sOrder
$sLimit
";
[/code]

I should also point out that my money column does use "$" and "," in it.
I even tried changing the ">" to an "=" to make sure there would be a valid result, but that didn't work either.

Any idea what I'm doing wrong?

Thanks! Am going to send a donation your way as well.

(Note: I plan to use your range filter plugin when the time comes. For now, this is a proof of concept, trying to get this working.)

allan

Hi mrbranden,

The first thing that stands out for me is this little bit:

[code]
aoData.push({"name":"city", "value":$('input.cityorzip').val(),
"name":"minprice", "value":$('input.minprice').val() });
[/code]
You have only one object that you are pushing onto the array there, but the object defined both the 'name' and 'value' properties twice - what happens with that will depend on the Javascript engine...

What you want is this:
[code]
aoData.push({"name":"city", "value":$('input.cityorzip').val() });
aoData.push({"name":"minprice", "value":$('input.minprice').val() });
[/code]
Once you've made that change, if that doesn't fix it (and I'd say it probably won't - but you never know and it's needed anyway!) try printing out the SQL statement which is generated: echo $sQuery . Have a look at that return in Firebug (DataTables will give you a parsing error - that's fine). See if the generated SQL looks sensible (post it here :-) ), and also look for any errors from the SQL engine.

Regards,
Allan

mrbranden

Allan,

Thank you for the response. Some progress but still stuck. Here's the output of the $sQuery:

[code]
SELECT SQL_CALC_FOUND_ROWS id, listing_id, price, listingcity, numbedrooms, numfullbaths, numhalfbaths
FROM new_lmh_listings
WHERE price > '%undefined%'
ORDER BY price
asc
LIMIT 0, 10
{"sEcho": 1, "iTotalRecords": 5, "iTotalDisplayRecords": 0, "aaData": [] }
[/code]

So the obvious problem here is that "%undefined%".

I also did an echo $_GET['minprice'], but that showed up as undefined as well, which I guess is no surprise given what we're seeing above.

I'm still poking around, but if you have any suggestions I'd much appreciate it. :-)

Thanks!

allan

Based on the code, it would suggest that there isn't an input element with a class of 'minprice' ( $('input.minprice').val() ). Can you link us to your html?

The other thing that would need to be change it the WHERE statement - at the moment you have wild cards for a condition which can't be wildcarded. Something like:

[code]
$sWhere = " WHERE price > ".mysql_real_escape_string( $_GET['minprice']);
[/code]
Allan

mrbranden

Hi again.

Ah! I fixed the minprice input (and took out the wild card). Now the SQL looks like this:

[code]
SELECT SQL_CALC_FOUND_ROWS id, listing_id, price, listingcity, numbedrooms, numfullbaths, numhalfbaths
FROM new_lmh_listings
WHERE price > '$200,000'
ORDER BY price
asc
LIMIT 0, 10
[/code]

It's still not working, but there's improvement. It now lists everything, regardless of "minprice". Could this be because I'm trying to use a "greater than" with currency? Maybe it's not understanding the dollar sign and comma...

mrbranden

Okay I am pretty sure it's the currency issue. I changed "price" to "numbedrooms" so that it would be comparing an integer instead of a dollar amount. Two things, however:

1) If I set a default value in the numbedrooms input, the filtering works against that default value. As soon as I change that value (or remove the default), it does not filter.

2) Is there a reasonable way to get around the currency filtering issue? Or am I going to have to write code to strip the dollar signs, commas, etc...?

Thanks!

mrbranden

Okay another update. I have #1 fixed. I forgot to add:

[code]
$("#minbeds").keyup(function() {
oTable.fnFilter(this.value);
});
[/code]

However, the value it gets for minbeds is off by one. If I search for a minimum of 3 beds, it searches for a minimum of 4. I worked around this by doing: $numbeds = $_GET['numbeds'] -1; in my PHP. That's a work-around, but is that expected behavior?

Still not sure how to tackle the currency issue.

Thanks again!

mrbranden

I've really got to learn to be more patient before posting here. :-) I think I figured out the currency issue. I simply added your currency plug-ins and it seems to have done the trick!

I'm still not sure why I need to decrease numbeds by one but, like I said, I have a work-around. :-)

allan

I'd be surprised if the currency sorting plug-in helps with server-side processing, since sorting is all done on the server-side. But in general yes, I'd say almost certainly the dollar sign is the issue - although of course if what you have works - then great! If not then what is the data in the database, is it an integer or a string with a dollar sign. If a string, I'd suggest converting it to an integer and add the dollar sign when outputting it from the PHP or using fnRender.

Regards,
Allan