search word in mysql
I am trying to search mysql database for specific word or words.
In the table the data is storing like this
(# is used to join checkbox values)
Now my query is
Is it the right way to search....what if I want to search within 2 dates...I think in that case it will return only that amount of records which are matched with 2 specific dates only, but not between the dates which falls under 2 posted dates.
select * from transaction where valuedata like '%r%' and valuedata like '%a%' and valuedata like '%yes%'
Is all that data in one field, or does it represent many separate table columns?
Hi...sorry for late reply....
yes its all in one field that is "valuedata"
The data is storing in this way......
Now thinking how can i pull all the dates which fall between 1-February-2010 and 2-August-2010 (i mean if there is any)
You would have to parse the column, perhaps using SUBSTRING_INDEX() to get the two date sub-fields, then applying STR_TO_DATE() to convert them to date types, which then you could finally compare, perhaps using BETWEEN...AND. This, of course, would be highly inefficient, as it would require a full table read each time in addition to that processing, since indexes would be useless in this case. Probably a lot more efficient would be to have split up the data into separate fields in the database, each of the appropriate type, so that the power of the DBMS could be put to use.
Yes you are right.But the problem is that the fields in the form are all dynamic I mean there is a dynamic form generator which generates a form dynamically.So instead of creating columns I just concatinate each value with comma and insert in the table.
I have no other way except the first method which you told.But i am thinking how can I write the query which matches with 2 date values in the table...any starting idea plz
Here is a little proof of concept script in PHP:
Substitute the DB column name where I have '$data' and change the numeric arg for the inner SUBSTRING_INDEX() to whichever field number you want.
mysql_connect('localhost', '####', '#####') or die(mysql_error());
$data = "one,two,three,four,five";
$sql = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('$data', ',', 2), ',', -1) AS fld_2";
$result = mysql_query($sql) or die(mysql_error());
$value = mysql_fetch_assoc($result);
echo $value['fld_2']; // "two"
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)