[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
Note
lease 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.
