Delete from table

[maxtemp]

Hello
i was trying to create a short php code to delete certain data in one of my db table(all the raw older then the actual date). Searching in other post i finally created these few lines

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$now = JFactory::getDate();
$query
->delete('fab_trips')
->where(`datetime`) < $now ;
$db->setQuery($query);
$db->execute();

But still isn't working.
Can anyone give me some advice?
Thanks everybody

 

[Bauer]

mySql already has date constants, so you can just use them instead of assigning the date via php. (The mySql equivalent to your $now would be NOW() ). But you should use mysSql's CURDATE() instead of NOW(). If you use NOW, you would also delete records that were added on the same date - i.e. 1 minute prior to $now/NOW().
http://www.w3schools.com/sql/func_now.asp

But all that aside, the glaring problems I see with your code is the ->delete('fab_trips') which is using single quotes. It should be ->delete(`fab_trips`) rather than ->delete('fab_trips') ....and the syntax of the where condition is also incorrect, the closing parenthesis belongs at the end. Have a look at the bottom of this page for the correct syntax... https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase

Because you only have one where condition, this code removes some of complexity of the examples given at joomla.org - and should work in your case...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->delete( $db->quoteName('fab_trips') )
->where( $db->quoteName('datetime') . '< CURDATE()' ) ;
$db->setQuery($query);
$db->execute();

 

[maxtemp]

Thanks Bauer
your code was perfect, now it works and also you have given me god tips for future coding.
For me it's solved!

 

[Bauer]

Thanks Bauer
your code was perfect, now it works and also you have given me god tips for future coding.
For me it's solved!

No problem - always glad to help.