Custom Quick / Easy PHP Page - SQL Query / Display Data

ProCom · Jun 5, 2024

Hey All!

Please let me start by saying I am NOT an expert... or even novice at any of this stuff. I'm a newbie just trying to hack myself into the quickest way to get the results I need.

BACKGROUND:
For years I was running queries on my Xenforo DB via PHPMyAdmin to pull hidden signals in the noisy data. Some queries I was running often enough that I wished I had a page that would just auto-display the data. I learned how to create "custom XF pages" but that was always a challenge for my non-coding brain (and creating an addon was out of the question. LOL!).

Finally with the help of some awesome peeps way smarter than me, I created a PHP page / script that I could run and access (with correct account access authentication) the data I needed!

Solution:
Below is a very simple version of this page that I can edit (most often using AI/LLMs to help create the query and page) to use for almost anything to query the XF DB!

Take a look and please provide any thoughts / feedback / fixes / optimizations, etc..

I just created a folder off of /public/<folder>/ ... then uploaded the file into it, and accessed it via browser, and voilà... DATA!!

This simple version just displays the "Last 10 Post IDs" of the forum:

PHP:

<?php
require "/home/nginx/domains/backyardchickens.com/public/src/config.php";
$dir = "/home/nginx/domains/backyardchickens.com/public";

require($dir . '/src/XF.php');
XF::start($dir);
$app = XF::setupApp('XF\Pub\App');
$app->start();

$visitor = \XF::visitor();

// Ensure the user is logged in and is an administrator
if (!$visitor->user_id || !in_array(3, array_merge([$visitor->user_group_id], $visitor->secondary_group_ids))) {
    echo "No Permission";
    exit;
}

// Fetch the last 10 most recent post IDs
$posts = XF::db()->fetchAll("
    SELECT post_id
    FROM xf_post
    ORDER BY post_date DESC
    LIMIT 10
");
?>
<!DOCTYPE html>
<html>
<head>
    <title>Recent Posts</title>
</head>
<body>
    <h1>Last 10 Post IDs</h1>
    <?php if (!empty($posts)): ?>
        <ul>
        <?php foreach ($posts as $post): ?>
            <li><?php echo htmlspecialchars($post['post_id']); ?></li>
        <?php endforeach; ?>
        </ul>
    <?php else: ?>
        <p>No recent posts found.</p>
    <?php endif; ?>
</body>
</html>

ProCom · Jun 5, 2024

A related follow-up (novice) question:

I'd like to create, track, and update data that doesn't fit into the existing XF tables. I want to start adding new data without corrupting or conflicting with the existing XF tables. Is it possible for me to manually create a new table in PHPMyAdmin within my existing Xenforo database, ensuring it remains independent of existing tables and won't cause issues now or with future updates?

Basically, I'd like to create a PHP page like the one I mentioned above that will not only READ from the existing XF tables, but allow me to independently update data in tables separate from XF, but within the same DB. Does that make sense?

Korsch · Jul 19, 2024

I’m not an expect with API’s and I could be wrong but wouldn’t xenforos use of RestAPI be ideal for this? I’m currently trying to understand API usage and implementation.

Custom Quick / Easy PHP Page - SQL Query / Display Data

ProCom

Well-known member

ProCom

Well-known member

Korsch

Member

Similar threads

We value your privacy