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,
Raj

Recommended Answers

All 12 Replies

I don't see how your problem refers to mysql.
In mysql you can search for date ranges with a where clause like "mydate between '2011-02-01' and '2011-03-01'.

I don't see how your problem refers to mysql.
In mysql you can search for date ranges with a where clause like "mydate between '2011-02-01' and '2011-03-01'.

Yeah thats what I did previously...but in this case is there any function in mysql like preg match i mean pickup all the dates which will fall under 2 dates...or else I have to think about alternative ways....but the problem is the form is a dynamic form....and the number of columns cannot be same always

A date field in mysql is in effect a numerical field with some formatting. You cannot apply the regular expression function rlike to it.
For further suggestions, show your query and some input data.

for example,

The fields are:
'n1_Bill_Of_Lading,n1_Number_Of_Container,n1_Invoice,n1_Customs_Bill,n1_Others_Bill,n2_Supplier,
Paper_Arrival_Date,Container_Receving_Date,n2_Region_of_Country,n2_Port,n1_Items_With_Quantity'

and the corresponding data :

'MISCAM2000000507,2,1409,43084,000306620,Symphony,1-January-2011,12-January-2011,India,Riyadh,Kaizen 450'

For the query I am using like operator.Now "Paper Arrival Date" and "Container Receving Date" are fields related with dates

"Show the query" means: show the query code which you pass to mysql to get your data.
What field types are your "related with dates" fields? Are this character fields? Or date fields? Or what?

"Show the query" means: show the query code which you pass to mysql to get your data.
What field types are your "related with dates" fields? Are this character fields? Or date fields? Or what?

No the field types are "text" and currently I am not sending the dates to mysql....just thinking how to send.....obviously it will be " like '%2010-4-10%'..but I think first need to dump all the data and then use php to find and match with the array values....

If you deal with text strings in the form of day-month-year, month being a text string, you might code a function which replaces the month name by a numerical value, reverts the sequence of elements to year-month-date, and then you could use the mysql between function on the result.
Like in

select mydate(Container_Receving_Date) as crd from mytable having crd between "2010-01-01" and "2010-03-01"

where mydate is a UDF which you would have to create.

If you deal with text strings in the form of day-month-year, month being a text string, you might code a function which replaces the month name by a numerical value, reverts the sequence of elements to year-month-date, and then you could use the mysql between function on the result.
Like in

select mydate(Container_Receving_Date) as crd from mytable having crd between "2010-01-01" and "2010-03-01"

where mydate is a UDF which you would have to create.

where mydate is a UDF which you would have to create.

Can you please explain this a bit more....and where to create and what data it will contain...do you mean at the time of data entry I need to insert data in this column?

Learn about UDFs: http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html
Are we really talking about MySQL? Or are yuo trying to solve an HTML interface problem?

Hi many thanks for your input...I am going to redesign my db structure so that according to relational db law each field should contain each data...much better instead growing confusion ... :)

thanks once again for your nice ideas...

Maybe you want to show us your redesign?
The representation of a date field does not affect the relational structure. It's only easier to deal with if you have it in a MySQL DATE format so that you can apply the DBMS's date/time functions to it.

I assume that a form can have max of 20 fields...so I just kept

n1...n20 columns and rest of the things I have managed with server side language to put the correct values under correct column....running smooth....hehehe

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.