SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date search within 2 dates

    Hi All,

    I have made a dynamic form details content table where the data is storing by comma separated values.All the form fields are stored as dynamic in the table.

    Fields :

    HTML Code:
    n1_First_name,n2_bank_aaccount,piriod_to,n5_comments_by,n6_like,n7_comments
    Data:

    Code:
     nil@nil.com,test1,4-May-2010,test2,no,abc
    
    nil@nil.com,test1,4-June-2010,test2,no,abc
    Now as you see this field "piriod_to" refers to 4-May-2010 and 4-June-2010.....till now its ok...no problem...

    But when I am doing a date range search its not listings all the dates which falls under 2 dates....

    Can you please suggest a way where I can pull out those records which falls under 2 dates(from posted values)

    Thanks,
    Raj

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you can begin by telling us what datatype your column is

    the easiest way to do that is with the SHOW CREATE TABLE command

    i'm guessing it's VARCHAR
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The datatype for the column "valuedata" is "text"

  4. #4
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have found something here...

    http://forums.mysql.com/read.php?10,...573#msg-255573

    But can't get it working with mine...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    omg i just read your first post more carefully

    all the data is stored inside a single column?

    you are placing yourself at a humoungous disadvantage

    no search query will ever be efficient, and some of them you won't even be able to write with sql at all

    please, consider redesigning the table

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm...yes but the problem is that the generated form is completely dynamic and so instead of creating dynamic columns I found the way to put all the data with a comma separated values under single column...

    yes this is obviously inefficient in terms of query table but if I say to my project manager that I am going to change the db design then for sure he will kill me...

    Is there any tricky way to make it working...I mean its not a huge db and its for the inhouse usage....

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by prithvi.web View Post
    ...its not a huge db and its for the inhouse usage....
    use SELECT * FROM daTable to read it all into memory, then search with your application language
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm.....that will be much better....I am using php....I have to open a new thread under php.....my logic is

    1.run a for loop from search_start_date to end_search_date
    2.do a preg match with the stored data inside the loop
    3.pick up the corresponding ids

  9. #9
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am going to redesign my db....

    what if I keep a field as text and insert 2011-01-15...will it accept the value......the datatype is text and later i would like to keep a date search also...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you want a date search, you should use DATE or DATETIME datatype, not TEXT
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ya i know...hehe...but i have no other way than to keep as text or varchar...


Bookmarks

Posting Permissions

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