www.webdeveloper.com
Results 1 to 6 of 6

Thread: search word in mysql

  1. #1
    Join Date
    Feb 2006
    Location
    india
    Posts
    678

    search word in mysql

    Hi All,

    I am trying to search mysql database for specific word or words.

    In the table the data is storing like this
    raj,mitra,male,married,1-February-2010,3-March-2012,laptop#pc,US#UK,b,bad,dummycomments
    (# is used to join checkbox values)

    Now my query is

    PHP Code:
    select from transaction where valuedata like '%r%' and valuedata like '%a%' and valuedata like '%yes%' 
    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.

    Please suggest.

    Thanks,
    Raj

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,337
    Is all that data in one field, or does it represent many separate table columns?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Feb 2006
    Location
    india
    Posts
    678
    Hi...sorry for late reply....

    yes its all in one field that is "valuedata"

    raj,mitra,male,married,1-February-2010,3-March-2012,laptop#pc,US#UK,b,bad,dummycomments

    jay,sen,female,single,2-August-2010,23-April-2019,ipad#iphone,GA#IN,c,good,dummycomments1

    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)

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,337
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Feb 2006
    Location
    india
    Posts
    678
    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

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,337
    Here is a little proof of concept script in PHP:
    PHP Code:
    <?php
    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"
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles