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 :
n1_First_name,n2_bank_aaccount,piriod_to,n5_comments_by,n6_like,n7_comments
Data:
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,
r937
January 14, 2011, 4:06pm
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
The datatype for the column “valuedata” is “text”
I have found something here…
http://forums.mysql.com/read.php?10,206857,255573#msg-255573
But can’t get it working with mine…
r937
January 14, 2011, 4:18pm
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
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
January 14, 2011, 4:43pm
7
use SELECT * FROM daTable to read it all into memory, then search with your application language
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
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
January 15, 2011, 11:27pm
10
if you want a date search, you should use DATE or DATETIME datatype, not TEXT
ya i know…hehe…but i have no other way than to keep as text or varchar…
