help translating from PHP to MySQL

marcnyc · Jul 6, 2009

I need some help with MySQL syntax... I would know how to write it in PHP but I am not sure how to write it for MySQL.

I have a SQL table 'webcal_entry' and a column in it called 'cal_date' which contains dates in the numeric format yyyymmdd

I am trying to write a query that only takes events that are no older than 1 month.

Let's assume that:
$date = webcal_entry_user.cal_login
if it were PHP I would write this:

$year = (int) substr($date,0,-4);
$month = (int) substr($date,-4,2);
if ( $year == date('Y') && $month > ( date('n') - 1 ) )

however I have no idea how to write this in my SQL query:

  $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "webcal_entry.cal_date = '" . ??? . "'";

I would appreciate your help.
Thank you

djjjozsi · Jul 6, 2009

example:

SELECT * FROM `table`
WHERE `date_time`> date_add(NOW() , INTERVAL -30 DAY)

records in 30day.

marcnyc · Jul 6, 2009

thank you so much! I will try this right away!

marcnyc · Jul 6, 2009

unfortunately that didn't seem to work...

In other words I see events that are much older than 1 month...

is that comma supposed to be there? I don't see it in any of the examples on the mysql documentation page...

My query right now:

  $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "webcal_entry.cal_date < DATE_ADD(NOW(), INTERVAL -30 DAY)";

djjjozsi · Jul 6, 2009

then try to change the relation, as you see i changed the example...

jjozsi

marcnyc · Jul 6, 2009

I am not sure what you mean by changing the relation...

djjjozsi · Jul 6, 2009

  $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "webcal_entry.cal_date > DATE_ADD(NOW(), INTERVAL -30 DAY)";

i mean the ( > ) greater then mark should be used.

marcnyc · Jul 6, 2009

of I see... I had not noticed that...
I will give this a try right now

djjjozsi · Jul 6, 2009

in the Mysql manual you will find at DATE_ADD() function, where you can add or substract two dates. LEts try the examples on that page, its very useful.

jjozsi

marcnyc · Jul 6, 2009

Still doesn't work...
I looked at the examples and it seems that all examples have date formats with dashes or something... my date format is 20090706... do I need to convert it first?

djjjozsi · Jul 6, 2009

oh, i see.
mysql can handle strings as dates if you add the correct format.

STR_TO_DATE(`date_field`,'%Y%m%d') AS datefield

marcnyc · Jul 7, 2009

So the correct MySQL syntax would be the following?

  $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "STR_TO_DATE(webcal_entry.cal_date,'%Y%m%d') AS datefield AND ".
    "datefield > DATE_ADD(NOW() INTERVAL -30 DAY)";

or do I need to use STR_TO_DATE with SELECT like this?

  $sql = "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 ";

  $sql .= "WHERE webcal_entry.cal_id = webcal_entry_user.cal_id AND " .
    "webcal_entry_user.cal_login = '" . $user . "' AND " .
    "datefield > DATE_ADD(NOW() INTERVAL -30 DAY)";

djjjozsi · Jul 7, 2009

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.

marcnyc · Jul 7, 2009

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.

djjjozsi · Jul 7, 2009

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.

marcnyc · Jul 8, 2009

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...

djjjozsi · Jul 8, 2009

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.

marcnyc · Jul 8, 2009

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

djjjozsi · Jul 8, 2009

yes, dump is an export from your database.
In phpmyadmin you can make it.

marcnyc · Jul 14, 2009

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.

help translating from PHP to MySQL

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc

Ddjjjozsi

Mmarcnyc