Dear All,
I have stored my dates as a text in database (MySQL). I want to make an advanced search for users so that users can select from a calendar their targeted date as start_time and also they can select from a calendar their targeted date as finish_time. But, it seems to me that, it does not work properly and accurately. Here is my code:
$search_query = "select material_name, start_date, finish_date from table where
material_name != ‘’ and ";
if(isset($sdate)) {
$search_query .= " sdate >= ‘$sdate’ ";
}
if(isset($edate)) {
$search_query .= "AND edate<= ‘$edate’ ";
}
My code returns something, but in some cases it does not return correct results. How to modify it?
Thanks in advance.
Your logic is messed up. You set a WHERE condition, then you test and if something is correct, set a
second one. THEN, you test something and set an AND. Therefore, you might end up with a “and AND” in your query.
You should just move the " and " to inside the assignment of the query test. Loosely like:>
material_name != ‘’;
if(isset($sdate)) {
$search_query .= " AND sdate >= ‘$sdate’ ";
That way, each additional compare includes the AND. Hope that helps.
1 Like
Thanks for your consideration,
The problem is related to the database, In other words, dates are stored as a text and as a result, MySQL can not recognize that dates are real dates. So, for solving the problem, I have to use DATE statement before sdate.
if(isset($sdate)) {
$search_query .= " AND DATE(sdate) >= ‘$sdate’ ";
Well, in your code, it will end up creating a query like this…
SELECT * FROM table WHERE some!=’’ and AND somecondition…
The " and AND " will fail every time!
As far as the dates go, why would you save dates as text? But, if you do, you can convert them to a real
date in the query itself. To compare a string as a date, you use something like this:
WHERE STR_TO_DATE(fieldname) >=’$sdate’"; You can add a format option to match the type of date
if you use a non-standard version. Here is a link explaining this: Mysql-str_to_date
1 Like
Thank you very much for getting back to me, my friend
Hope you got it working. If not, show us the query…
1 Like
Doing this in the query cannot use an index and will cause a full table scan in every query. Once you have a real amount of data, this will take a long time to execute every query.
Just correctly store the dates as a DATE data type. You can then use an index with the column, directly do date comparisons, and sort the result by the date. This will also use the least amount of storage space.
1 Like
Thank you,
In phpmyadmin, I set date field as a text with this format(2020/5/16). Because I have another calendar(hijri) and I convert hijri to normal calendar by PHP code.
First of all, I need to change text to date in phpmyadmin structure?
And then can you please give me an example to send my data to be stored in this format at database as a date?
Format(year/month/day)
Looking forward to your comments
If you wish to alter your database to use date formats, that is easy, just go into your phpmyadmin and select
“structure” for the table and click on the pencil icon to show the format of that field. Then, alter it to DATE.
Easy enough. But, it will NOT alter the data already in the database. It might, but, unlikely. Therefore, you
need to make sure you have that field backed up.
To save data in the field using PHP, you just convert the incoming text to the date format. Here is an example
of how to do an update using your format…
$date = "2020/5/16"; // TEXT version
$query = "UPDATE table SET start_date = STR_TO_DATE(" . $date . ", '%y/%m/%d')";
This converts the text version of a date and updates a field with it… Not tested, just create for you to
understand how it works.
Now, with all that said, you can still use the text versions of the dates if you want to by using the code
I showed earlier. If you already have a large amount of data in the date field, you can still do searches
on it by this same STR_TO_DATE() function to change it to a date format to compare to whatever date
you need. In my opinion, changing the database would make it more standard.
Hope this helps…
1 Like
Dear ErnieAlex, I’m deeply grateful for your kind response.
Very glad to help you. Hope it helps you solve your code puzzle!
1 Like