Animetal
Posts: 27
|
| Posted: 02/28/2011, 2:32 PM |
|
Hi,
I have a search page search.php that send 2 dates variable to the url for result.php
In the result.php I get this SQL
SELECT * FROM consult WHERE con_date >= '2011-01-17 00:00:00' AND con_date <= '2011-01-18 00:00:00'
but as you can see is not including the day 2011-01-18 because the time is set to 00:00:00 and I want to add it so in MySQL using DATE_ADD I can do this but I dont know where to edit the SQL in my result.php or if theres another way to edit the 2nd date before sending it to the url.
In short I want to edit my default SQL to this:
SELECT * FROM consult WHERE con_date >= '2011-01-17 00:00:00' AND con_date <= DATE_ADD( '2011-01-18 00:00:00', INTERVAL 1 DAY)
|
 |
 |
wadus
Posts: 5
|
| Posted: 02/28/2011, 2:39 PM |
|
You can use 23:59:59 in the second date
SELECT * FROM consult WHERE con_date >= '2011-01-17 00:00:00' AND con_date <= '2011-01-18 23:59:59'
|
|
|
|
Animetal
Posts: 27
|
| Posted: 02/28/2011, 3:18 PM |
|
Yes, i can do that too but where i should change that? i get the Default SQL string from codecharge i didnt make it
|
|
|
|
damian
Posts: 838
|
| Posted: 02/28/2011, 3:29 PM |
|
try returning the date as yyyy-mm-dd without any time.
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
datadoit
|
| Posted: 02/28/2011, 4:20 PM |
|
In the Visual Query Builder, for the WHERE parameters, you can specify
"Expression" for your parameter. So your Expression for your second
parameter would be:
con_date <= DATE_ADD(CCToSQL(CCGetParam("s_EndDate", ""), ccsDate),
INTERVAL 1 DAY)
That "s_EndDate" should match your search field name.
|
|
|
|
Animetal
Posts: 27
|
| Posted: 03/01/2011, 6:22 AM |
|
Thanks datadoit, I was looking for something like that "i didnt know how to call a url variable in the expression field" but this line:
DATE_ADD(CCToSQL(CCGetParam("s_con_date2", ""), ccsDate), INTERVAL 1 DAY)
Is giving me an error:
Parse error: syntax error, unexpected T_LNUMBER in C:\xampplite\htdocs\Consult\result.php on line 122
|
|
|
|
Animetal
Posts: 27
|
| Posted: 03/01/2011, 8:25 AM |
|
I found another alternative but im still looking for the correct expression in the SQL Builder.
My solution right now in result.php is:
1. Remove my 2nd date url variable from the SQL Builder
2. Create an event in BeforeBuildSelect
3. Add the following custom code:
global $consult;
$date2 = date('Y-m-d', strtotime(CCGetParam("s_con_date2","")));
$consult->DataSource->Where .= " AND con_date <= DATE_ADD( '".$date2."', INTERVAL 1 DAY)";
Right now is working fine, my warnings are on in my server and i get "Warning: date() [function.date]: " is not the first time i get this, any workaround to fix this? maybe in another topic.
Thanks for your help, if you know how to correctly add this: DATE_ADD(CCToSQL(CCGetParam("s_con_date2", ""), ccsDate), INTERVAL 1 DAY) in the SQL Build expression I would apreciate that
|
|
|
|
datadoit
|
| Posted: 03/01/2011, 8:48 AM |
|
Try this expression:
con_date <= DATE_ADD(" . CCToSQL(CCGetParam("s_con_date2", ""), ccsDate)
.. ", INTERVAL 1 DAY)
We're basically breaking out of a MySQL function to perform a PHP or CCS
function, then going back into the MySQL function to complete the
expression.
|
|
|
|
Animetal
Posts: 27
|
| Posted: 03/01/2011, 11:39 AM |
|
Thank You, I can use it as a reference, right now is working but i had to make some changes because it was changing the date format and MySQL didnt recognize it.
con_date <= DATE_ADD(" . CCToSQL(date('Y-m-d', strtotime(CCGetParam("s_con_date2", ""))), ccsDate). ", INTERVAL 1 DAY)
Now, I have this problem, when i dont send any 2nd date variable to result.php, MySQL catch this:
SELECT * FROM consult WHERE ( con_date <= DATE_ADD('1969-12-31', INTERVAL 1 DAY) )
In my previous code I got the same issue but with one if ($date != "") fix that
Thanks datadoit, im going to mark this as resolved
|
|
|
|
|
