How to run SQL command through cron

Hello . I have a question about mysql
I got a query to delete duplicate content on my websites and i would like to be able to schedule cron jobs to run the queries twice a day.
Currently I have to do it through phpmyadmin .

Could anybody please help me with this matter ?

Thanks!

below is a code:

--------------

DELETE bad_rows.*
from wp_posts as bad_rows
inner join (
select post_title, MIN(id) as min_id
from wp_posts
group by post_title
having count(*) > 1
) as good_rows on good_rows.post_title = bad_rows.post_title
and good_rows.min_id <> bad_rows.id

 

You need to put those SQL commands into a PHP page first, then call cron to run that particular PHP file periodically.

 

just put this script into a php or any archiving file then run this under linux cron jobs

 

Php is definitely a way but i suppose you could using it directly as a mysql command.
try this:

mysql -h hostname -u username -p password -e "QUERYHERE"

Code (markup):

let us know if it works!

 

Could one of you experts PLEASE tell me why the following doesnt work...

1-i made a php file with the following inside :

$sql = "DELETE bad_rows.*\n"
. "from wp_posts as bad_rows\n"
. "inner join (\n"
. "select post_title, MIN(id) as min_id\n"
. "from wp_posts\n"
. "group by post_title\n"
. "having count(*) > 1\n"
. ") as good_rows on good_rows.post_title = bad_rows.post_title\n"
. "and good_rows.min_id <> bad_rows.id";

Then i set up cron "10 * * * * lynx -dump http://domain.com/file.php"

it didnt work.
Please ,somebody help.

Thankyou

 

Hi again.
I made a php file to connect to db but it still doesnt work. Duplicate posts are still present.

Please help me with sql synthax below:


<?php
mysql_connect("localhost", "x3_coffe", "password") or die(mysql_error());
mysql_select_db("x3_coffe") or die(mysql_error());
$sql = "DELETE bad_rows.*
from wp_posts as bad_rows
inner join (
select post_title, MIN(id) as min_id
from wp_posts
group by post_title
having count(*) > 1
) as good_rows on good_rows.post_title = bad_rows.post_title
and good_rows.min_id <> bad_rows.id";
?>

 

a) You are better off using the command line PHP client and not lynx to do this; its what it was built for....

b) You syntax above is incorrect because by /default/ wordpress will automatically not allow duplicate posts. (unless you are inserting them yourself and have broken the rule). If you are doing this, stop, and recode what is broken alreadt rather than trying to clean up afterwards.

E.G If you have a title of "this is a test" it will become "this-is-a-test". The next one you try to add will be "this-is-a-test-2". This is why your query is most likely not working.

c) Also, your subselect is wrong. See above for reasons why