j05hr Posted November 12, 2009 Share Posted November 12, 2009 I'm trying to make a search form on my website and I want it to search by price in ascending order. The problem is it doesn't recognize the bigger number in the right way. I've done this search and this is how it's come back, with a million at the top and then one hundred thousdand? I think it does it based on the first letter? How can I make it so it does it by size? $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `house_price` ASC "; // order by title. 1: 1,000,000 5+ House 2: 100,000 4 House 3: 200,000 1 House 4: 300,000 2 House Quote Link to comment Share on other sites More sharing options...
darkvengance Posted November 12, 2009 Share Posted November 12, 2009 Hmm...that is interesting...try removing the commas from the prices and see if that works. Quote Link to comment Share on other sites More sharing options...
j05hr Posted November 12, 2009 Author Share Posted November 12, 2009 I thought that too and did it before trying here but it just returns, I presume it's finding the 1's and then 2, 3 etc 1: 100000 4 House 2: 1000000 5+ House 3: 200000 1 House 4: 300000 2 House Quote Link to comment Share on other sites More sharing options...
siric Posted November 12, 2009 Share Posted November 12, 2009 What is the data type on the price? If it is text or varchar, you will get that problem. Make sure that it is set to integer or float. Quote Link to comment Share on other sites More sharing options...
j05hr Posted November 12, 2009 Author Share Posted November 12, 2009 Ah yeah that would make sense but then I remembered why I set it to varchar, what happens if I want the commas? Or you just can't have them? Quote Link to comment Share on other sites More sharing options...
cags Posted November 12, 2009 Share Posted November 12, 2009 Just format the number before displaying it to the user. There is no need for the comma in the actually data as it has no integral meaning. Quote Link to comment Share on other sites More sharing options...
j05hr Posted November 12, 2009 Author Share Posted November 12, 2009 So is there no way for doing this as back end staff will be inputting these prices and when dealing with 6-7 figure numbers it's easy to make a mistake and with commas it breaks it up. If not I can just do what you suggested. Thanks Quote Link to comment Share on other sites More sharing options...
cags Posted November 12, 2009 Share Posted November 12, 2009 You can use the MySQL function Format to add the comma's before displaying. If you want the user to input the value as a string with commas in it simply strip them out by str_replacing ',' with ''. That's assuming mysql won't accept an Integer with them in (which I don't think it will, but I've not tested it). Quote Link to comment Share on other sites More sharing options...
siric Posted November 12, 2009 Share Posted November 12, 2009 You can use the MySQL function Format to add the comma's before displaying. If you want the user to input the value as a string with commas in it simply strip them out by str_replacing ',' with ''. That's assuming mysql won't accept an Integer with them in (which I don't think it will, but I've not tested it). This is the way to go. Integer will not allow commas (it strips anything after). Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.