SQL query in specific forum

[spicer]

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.

# 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:

#!/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

 

[briansol]

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:

How does deleting work?

How does deleting work in xenForo? if i delete a thread, can i be sure anything that references that thread (posts, alerts, likes etc) will all be deleted or will there be "dead" data in the database after the thread is deleted. If xenforo uses the cascade delete model (delete thread will delete...

xenforo.com

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.