Problem Using To_days Function

[orionis]

Hello,

I'd like to regularly clean the tables of my databases.

To do so, I've written a short PhP program which searches the cleanning filters in a table nammed 'cleanning', that contains the database's name, the table's name and a char column that contains the filter I want to apply to the table's data, such as “Item_status='RESOLVED'”, for example.

The problem I'm facing comes from the use of the TO_DAYS function. I'd like to save, then to delete the lines older than – say – one year.

The following query works well :

[codebox]select * from database.table where TO_DAYS(NOW()) – TO_DAYS(database.table.item_date) > 365[/codebox]

When I copy/paste the sentence “TO_DAYS(NOW()) – TO_DAYS(database.table.entdate) > 365” in the filter column, the following error appears :

“Error 1109 – Unknown table 'database.table' in field list”

Of course, 'table' is not sheltered in the same database than 'cleanning'.

My SQLYog version is 6.06, but some of my colleagues work with version 4 or 5, and this operation works very well.

One solution could consist in puting the query between quotes, but I don't find it very smart… <_< is somebody has an answer? (Sorry for my poor English 🙁 )

[peterlaursen]

I am afraid I do not understand this

“When I copy/paste the sentence “TO_DAYS(NOW()) – TO_DAYS(database.table.entdate) > 365″ in the filter column”

Where is that 'filter column'? in SQLyog?

I also do not understand

1) where you use PHP and where you use SQLyog

2) what works in SQLyog 4 (or 5) and not in 6?

A few screenshots may tell more than words!

[orionis]

>”When I copy/paste the sentence “TO_DAYS(NOW()) – TO_DAYS(database.table.entdate) > 365″ in the filter column”

>Where is that 'filter column'? in SQLyog?

yes, it's the “filtre” column in the “auth.hades” table, that is used by the cleaning program (see attached picture).

>I also do not understand

>1) where you use PHP and where you use SQLyog

The cleaning program is written in PhP and the cleaned databases have been created and are managed by SQLYog.

>2) what works in SQLyog 4 (or 5) and not in 6?

In versions 4 and 5, I can save the value “TO_DAYS(NOW()) – TO_DAYS(database.table.entdate) > 365” in the 'filtre' column, which cannot be made with version 6.

>A few screenshots may tell more than words!

Here it is :

[attachment=741:sqlyog1.jpg]

Hope it will be clearer for you! 🙂

[peterlaursen]

Now I think I understand.

You simply want to save this SQL snippet as a literal string?

Please try to enclose the string in `backquotes`!

Alternativley disable 'smart keywords and functions' in preferences.

I think SQLyog her attempts to evaluate as a function, refer to

http://webyog.com/faq/8_116_en.html

Please reply back if that was the issue!

[orionis]

>You simply want to save this SQL snippet as a literal string?

Yep 😀

>Please try to enclose the string in `backquotes`!

I already found this trick, but I thought there might be something else 'cleaner'!

>Alternativley disable 'smart keywords and functions' in preferences.

THAT'S IT! 🙄 It works, thank you very much for your help!

Fabrice

[Author]

Posts