clean tables - cli?

[chef-olaf]

I have a small problem.

When I click on clean tables/clean keywords I get a timeout from the server after some time.
Currently 1539787 keywords
Since I can't / don't want to increase the time out in the php settings, the question is:

How can I call clean tables in CLI, because the time out is not limited there?

Regards, Olaf

[captquirk]

On the Clean Tables tab, all of the items EXCEPT Clean Keywords should run very quickly and a timeout should never be an issue.
However, Clean Keywords can be time consuming. The larger the database, the more this will be an issue. A timeout, therefore, can definitely be an issue.
The Clean Keyword process actually consists of 3 different SQL statements, the second using parameters from the first, and the third depending on the results of the second.
The statements:

SELECT keyword_id, keyword FROM keywords;

For each result of this query, $keyID is the keyword_id retrieved, and $X is the first character of the md5 of the retrieved keyword.
The second query will run for each result of the first query. The second query is:

SELECT keyword_id FROM link_keyword$X WHERE keyword_id = $keyID;

Finally, for each of the second query, if the number of rows returned is zero, we do this:

DELETE FROM keywords WHERE keyword_id = $keyID

The full script for the function is:

/**
* Function to clear unassociated keywords
*
* @return void
*/
function cleanKeywords()
{
global $mysql_table_prefix, $db, $Submit, $key;

if (!isset($Submit) || $Submit != $key) {
return;
}
$del = 0;
$query = "SELECT keyword_id, keyword FROM ".$mysql_table_prefix."keywords";
$stmt = $db->prepare($query);
if ($stmt) {
$stmt->execute() or die("Execution failed: ".$stmt->error);
$result = $stmt->get_result();
$stmt->close();
} else {
trigger_error('Statement failed : '.$db->error, E_USER_ERROR);
}
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$keyId = $row['keyword_id'];
$keyword = $row['keyword'];
$wordmd5 = mb_substr(md5($keyword) ?? '', 0, 1, 'utf-8');
$query1 = "SELECT keyword_id FROM ".$mysql_table_prefix."link_keyword"
.$wordmd5." WHERE keyword_id = ? ";
$stmt1 = $db->prepare($query1);
if ($stmt1) {
$stmt1->bind_param("i", $keyId);
$stmt1->execute() or die("Execution failed: ".$stmt1->error);
$stmt1->bind_result($keyword_id);
$stmt1->store_result();
if ($stmt1->num_rows < 1) {
$query2 = "DELETE FROM ".$mysql_table_prefix
."keywords WHERE keyword_id = ? ";
$stmt2 = $db->prepare($query2);
if ($stmt2) {
$stmt2->bind_param("i", $keyId);
$stmt2->execute() or die("Execution failed: ".$stmt2->error);
$stmt2->close();
} else {
trigger_error('Statement failed : '.$db->error, E_USER_ERROR);
}
$del++;
}
$stmt1->free_result();
$stmt1->close();
} else {
trigger_error('Statement failed : '.$db->error, E_USER_ERROR);
}
}
echo "<div id='submenu'>
</div>
<br><div style='text-align:center;'><b>Keywords table cleaned, "
.$del." keywords deleted.</b></div>";
}

Obviously, the function cannot be directly run from the command line interface, BUT there is no reason it can't be adapted to for such use. A database connection needs to be added and irrelevant outputs and security checks deleted.

Give me a few days and I can probably create such a script for you

[captquirk]

Save this as cleankeywords.php in the admin directory:

<{QUESTIONMARK}php
require "../settings/database.php";

$del = 0;
$query = "SELECT keyword_id, keyword FROM ".$mysql_table_prefix."keywords";
$stmt = $db->prepare($query);
if ($stmt) {
$stmt->execute() or die("Execution failed: ".$stmt->error);
$result = $stmt->get_result();
$stmt->close();
} else {
trigger_error('Statement failed : '.$db->error, E_USER_ERROR);
}
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$keyId = $row['keyword_id'];
$keyword = $row['keyword'];
$wordmd5 = mb_substr(md5($keyword) ?? '', 0, 1, 'utf-8');
$query1 = "SELECT keyword_id FROM ".$mysql_table_prefix."link_keyword"
.$wordmd5." WHERE keyword_id = ? ";
$stmt1 = $db->prepare($query1);
if ($stmt1) {
$stmt1->bind_param("i", $keyId);
$stmt1->execute() or die("Execution failed: ".$stmt1->error);
$stmt1->bind_result($keyword_id);
$stmt1->store_result();
if ($stmt1->num_rows < 1) {
$query2 = "DELETE FROM ".$mysql_table_prefix
."keywords WHERE keyword_id = ? ";
$stmt2 = $db->prepare($query2);
if ($stmt2) {
$stmt2->bind_param("i", $keyId);
$stmt2->execute() or die("Execution failed: ".$stmt2->error);
$stmt2->close();
} else {
trigger_error('Statement failed : '.$db->error, E_USER_ERROR);
}
$del++;
}
$stmt1->free_result();
$stmt1->close();
} else {
trigger_error('Statement failed : '.$db->error, E_USER_ERROR);
}
}
echo "Keywords table cleaned. ".$del." keywords deleted./n";

On the very first line, replace {QUESTIONMARK} with "?". No quotes. (I had to do this to prevent an error in posting.)
Then from the command prompt, navigate to the admin directory and run the following:

php cleankeywords.php

Depending on the database size, it may take a bit.

[chef-olaf]

hello,

script works perfectly
I have now set up a cronjob that executes the script once a month

thanks for the once again excellent support

olaf