prithvi_web — 2011-01-14T10:37:56-05:00 — #1
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.
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)
r937 — 2011-01-14T11:06:58-05:00 — #2
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
prithvi_web — 2011-01-14T11:09:32-05:00 — #3
The datatype for the column "valuedata" is "text"
prithvi_web — 2011-01-14T11:13:45-05:00 — #4
I have found something here...
But can't get it working with mine...
r937 — 2011-01-14T11:18:13-05:00 — #5
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
prithvi_web — 2011-01-14T11:21:40-05:00 — #6
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....
r937 — 2011-01-14T11:43:24-05:00 — #7
use SELECT * FROM daTable to read it all into memory, then search with your application language
prithvi_web — 2011-01-14T12:01:51-05:00 — #8
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
prithvi_web — 2011-01-15T16:49:30-05:00 — #9
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...
r937 — 2011-01-15T18:27:32-05:00 — #10
if you want a date search, you should use DATE or DATETIME datatype, not TEXT
prithvi_web — 2011-01-15T18:28:55-05:00 — #11
ya i know...hehe...but i have no other way than to keep as text or varchar...