SQL query in specific forum

spicer

Active member
Hi
I wrote a BASH script that searches the forum for specific entries (links to files in the filehoster directory) using a mysql query.
If it finds an entry in the filehoster directory that does not exist in the forum, it will be deleted from the filehoster directory.
Code:
# BASH While loop
SELECT 'message' FROM xf_post WHERE message LIKE \"${search_string}\";
That works good.

Now I want additional, that filehoster entries to be deleted, from which the link is in the "Archive" forum.
How can I make the SQL query only search the "Archive" forum?

Sorry, my english is terrible. Please answer in simple english ;)

PHP-Version: 7.4.30
MySQL-Version: 10.5.15 (10.5.15-MariaDB-0+deb11u1)
Xenforo 2.2.10 Patch 1

My script:
Code:
#!/bin/bash

########################################
#            JPGs aufraeumen           #
#             V1.0 (c) 2022            #
#               by spicer              #
########################################

# This script must be in /root with 700

pfad="/var/www/vhosts/xxx.xx/xxx.xx/httpdocs/filehoster/jpg"
dbpass="yyyyyyyyyy"
dbuser="forum"
dbname="forum"
forenpfad="filehoster/jpg/"

rm geloeschte_jpg.log
rm gespeicherte_jpg
cd $pfad
find . -iname "*" -exec basename {} \;>/root/gespeicherte_jpg
cd /root

i=0

while read filename; do

((i=i+1))

such_string='%'$forenpfad$filename'%'

resultat=$(mysql -u $dbuser -p$dbpass -D $dbname \
-e "SELECT 'message' FROM xf_post WHERE message LIKE \"${such_string}\";")

size=${#resultat}

endung=${filename: -3}

if [[ $size -eq 0 && "$endung" = "jpg" && "$endung" != "." && "$endung" != "" ]]
then
    echo $i" Loesche: "$filename
    rm $pfad"/"$filename
    echo "Zeile "$i" geloescht: "$filename >> geloeschte_jpg.log
else
    echo $i" Behalte: "$filename
fi;

done < gespeicherte_jpg
 
Last edited:
sql deletes for posts are complicated as there is a lot of related data that needs to be deleted with it. If you don't delete it all, you end up with orphaned data in tables.

This is old, from xf1.3, but it gives the idea:

the _postDelete() method needs to be considered if you do it with sql alone as the interface (XF form) does a lot more than just a simple delete.
 
Back
Top Bottom