the second option should be tested in phpmyadmin
is there a reason you build the queries in several rows concated with
".............".
"............."; ?
its allowed to place enters in you query.
help translating from PHP to MySQL
I tried running the following query in PHPMyAdmin:
SELECT webcal_entry.cal_id, webcal_entry.cal_name, webcal_entry.cal_priority, webcal_entry.cal_date, webcal_entry.cal_time, webcal_entry_user.cal_status, webcal_entry.cal_create_by, webcal_entry.cal_access, webcal_entry.cal_duration, webcal_entry.cal_description, webcal_entry_user.cal_category, STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') AS datefield FROM webcal_entry, webcal_entry_user WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry_user.cal_login = 'Marc' AND datefield > DATE_ADD(NOW() INTERVAL -30 DAY)
and I got the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERVAL -30 DAY)
LIMIT 0, 30' at line 1
The I tried the same query with the comma that you had originally inserted after NOW() and the error became:
#1054 - Unknown column 'datefield' in 'where clause'
Because I don't know MySQL syntax I didn't know whether what I did was allowed (using datefiled as a variable)... so I am really confused and don't know how to go about this.
To answer your question, I didn't write this script, I am just making this modification, so I am just going by how the original developer coded it, that's all.
SELECT webcal_entry.cal_id, webcal_entry.cal_name, webcal_entry.cal_priority, webcal_entry.cal_date, webcal_entry.cal_time, webcal_entry_user.cal_status, webcal_entry.cal_create_by, webcal_entry.cal_access, webcal_entry.cal_duration, webcal_entry.cal_description, webcal_entry_user.cal_category FROM webcal_entry, webcal_entry_user WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry_user.cal_login = 'Marc'
AND STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') > DATE_ADD(NOW() , INTERVAL -30 DAY)I couldn't test this version, the comma needed between the NOW() (current date-time) and the interval keyword.
That didn't work either, however I had an idea... it might not be the most correct way to approach the problem but it could avoid me dealing with MySQL syntax...
Since the database holds dates as strings in the form of yyyymmdd, I figured the higher the number the more recent the date, so I could just write the query like this:
$sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
"webcal_entry_user.cal_login = '" . $user . "' AND " .
"webcal_entry.cal_date > '" .date('Y').'0'.(date('n')-1).date('d'). "'";
// I use 'n' which is month digit without leading zero because if I used the double digit 'm' modifier once I take -1 off the zero would disappear anyway
What do you think?
it seems to work (I don't see events older than june 9th today, which is July 8th) but I don't see events in sept so I am wondering what the deal is with that...
string and date format has differences.
use the date to string converter first, as i suggested,
or migrate the existing date field into new date formatted one.
read the manual about the date formats.
if you can't coop with this issue, send the mysql dump with xample data,
and i can send you a tested sql query for the records in 1 month interval.
I am aware that string and date are different but if the date is saved as a string (or I just assume it is) I should be able to handle it as a string...
I can't alter the structure of the dB.
If you tell me how to get you a mysql dump I'll be glad to send it to you.You want me to simply export some newly created data and export the structure as well? Is that what you mean
Thanks for offering to help
yes, dump is an export from your database.
In phpmyadmin you can make it.
6 days later
Sorry it took me a while, I was away on business.
Here's the dump (attached) that you requested with some data in it. Let me know if you need more data. Thanks for helping me out and being willing to look into this.
22 days later
Hello, I never heard back from anyone and I was wondering if djjjozsi was able to take a look at my attachment.
Thanks so much
hello, now i'm back from a holiday,
as i see, in your table structure, all your dates stored as integer!
you should use date type for dates in your programs. mysql can convert between data types, but its a bad design in some cases. in phpmyadmin page you can make a copy of you rexisting table structure, and make the type changes, in these cases suggested to create temporary table to be care of the correct date formats, i use php programs to insert re-formatted data in new tables.
Unfortunately I cannot change the structure of the database because it is not my script, it's a third party open source script (WebCalendar http://www.k5n.us/webcalendar.php) to which I am doing some modifications for my own needs. If I start changing the database I won't be able to upgrade to new versions. Already it is laborious to upgrade because I have to always re-add my code modifications.
Is there a way to do this using the date as integer?
after i uploaded and insetalled you SQL dump, its working for me:
SELECT *
FROM `webcal_entry`
WHERE SUBDATE( `cal_date` , 30 )Means you can test this query:
SELECT date_format(`cal_date` , '%Y-%m-%d')
FROM webcal_entry, webcal_entry_user WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND webcal_entry_user.cal_login = 'Marc'
AND
date_format(`cal_date`,'%Y-%m-%d')< date_add(NOW() , INTERVAL -30 DAY)if i'm not mistaken this selects records earlier then 30 days.
Is this what you expected?
Actually what I expected is to have all the events between 30 days ago and anytime in the future... in other words:
an event that is 45 days old I don't want
an event that is 29 days old I do want
an even that will happen next year I do want
I think your code excludes events in the future, right?
Try to insert this idea in your program, if the test in phpmyadmin gives you the newest
then use the greater then sign in my suggested query.
date_format(`cal_date`,'%Y-%m-%d') > date_add(NOW() , INTERVAL -30 DAY)