another search issue !

#1
[eluser]quest13[/eluser]
Hi, The issue I am facing while doing an application with CI is quite related to MySQL. But I hope the strong CI forum may able to throw some light on this issue.Though I posted this issue in MySQL forums, I hope to get some earlier breakthrough here. The script and query are as follows.

Thanks in advance.

I have a user requirement where in user can feed Price value in this way Price : $60-$80,$8-$10 and so on.I faced a problem while creating a search query to find the minimum and max price value entered.I could create a query to break the price value into Min_Price : 60 and Max_Price : 80. So When I tried to find a value between 65 to 75, I get the data of Price value pointing to $60-$80. But I face a peculiar problem of not able to find the value in single digits like price values between $7-$10 ! though I am able to see the proper breakdown of $7 and $10 as two digit values through select query ( 7 & 10 )respectively. I am not getting any error messages in MySQL ( PhpMyAdmin ).I get a message as " Your SQL query has been executed successfully " But nothing displayed !

My query is as follows :


Code:
SELECT searchid,propertyname,description,Price,Size,
TRIM(TRAILING '-' FROM(SUBSTRING_INDEX(SUBSTRING_INDEX( `Price` , '$', 2 ),'$',-1)))as Min_Price,
SUBSTRING_INDEX(SUBSTRING_INDEX( `Price` , '-', 2 ),'$',-1)
AS Max_Price,
SUBSTRING_INDEX(SUBSTRING_INDEX( `Size` , 'p', 1 ),'p',-1)
AS Act_Size
FROM search WHERE TRIM(TRAILING '-' FROM(SUBSTRING_INDEX(SUBSTRING_INDEX( `Price` , '$', 2 ),'$',-1))) >=100 AND SUBSTRING_INDEX(SUBSTRING_INDEX( `Price` , '-', 2 ),'$',-1) <=250

NoteTonguelease try to change the value Price between 7 and 9 , and see what is happening.

You can run the following script to see what is happening.

Code:
CREATE TABLE IF NOT EXISTS `search` (
  `searchid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `propertyname` varchar(250) NOT NULL,
  `description` text NOT NULL,
  `Size` varchar(250) DEFAULT NULL,
  `Price` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`searchid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `search`
--

INSERT INTO `search` (`searchid`, `propertyname`, `description`, `Size`, `Price`) VALUES
(1, 'property1', 'Description of the property 1', '10 per Sqft', '$100-$120'),
(2, 'property2', 'Description of the property 2', '20 per Sqft', '$150-$250'),
(3, 'property3', 'Description of the property 3', '30 per Sqft', '$7-$10');

Note : I tried out " between " option also , but of no use.

Any help in this regard is highly appreciated.

#2
[eluser]quest13[/eluser]
Oops ! Sorry if my previous question bothered someone. I got the solution. Actually there is nothing wrong with the query I posted. I had some '<' and '>' symbol reversed when I was checking locally !.

But I hope the code could have helped someone though !


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.