如何在XAMPP的PHP index页面末尾查看指定MySQL数据表

Hey there! Let's get your URL shortening service showing that MySQL table data at the end of the page. I'll break this down into backend (PHP) and frontend parts, and give you a complete code example that fits with your existing snippet.

First, you need to connect to your MySQL database and fetch the data from the table containing id, longurl, and shorturl columns. Add this code to your index.php file (replace the placeholder values with your actual database details):

<?php
// Database connection details - update these to match your setup
$host = 'localhost';
$db_user = 'your_db_username';
$db_pass = 'your_db_password';
$db_name = 'your_db_name';
$url_table = 'your_url_table_name'; // Replace with your actual table name

// Connect to MySQL
$conn = mysqli_connect($host, $db_user, $db_pass, $db_name);

// Check if connection succeeded
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Fetch all URL records from the table
$fetch_query = "SELECT id, longurl, shorturl FROM $url_table";
$result = mysqli_query($conn, $fetch_query);

// Store results in an array for frontend rendering
$url_records = [];
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $url_records[] = $row;
    }
}

// Close the database connection
mysqli_close($conn);
?>

Add this section right before the </body> tag to render a table with the fetched database data:

<br><br>
<h2>Stored URL Records</h2>
<table border="1" cellpadding="8" cellspacing="0">
    <tr>
        <th>ID</th>
        <th>Original URL</th>
        <th>Shortened URL</th>
    </tr>
    <?php if (!empty($url_records)): ?>
        <?php foreach ($url_records as $record): ?>
        <tr>
            <td><?php echo htmlspecialchars($record['id']); ?></td>
            <td><a href="<?php echo htmlspecialchars($record['longurl']); ?>" target="_blank"><?php echo htmlspecialchars($record['longurl']); ?></a></td>
            <td><?php echo htmlspecialchars($record['shorturl']); ?></td>
        </tr>
        <?php endforeach; ?>
    <?php else: ?>
        <tr>
            <td colspan="3">No URL records found in the database.</td>
        </tr>
    <?php endif; ?>
</table>

Here's how your full index.php will look, merging your existing form code with the new database logic and table display:

<!DOCTYPE html> 
<html> 
<head> 
    <title>URL Shortening Service </title> 
</head> 
<body> 
<center> 
    <h1>URL Shortening Service</h1> 
    <form method="POST" action="index.php"> 
        Enter your Url: <input type="text" name="urlshort" placeholder="Enter here"> 
        <br><br> 
        <input type="submit" name="submit" value="Shorten URL">
    </form>

    <?php
    // Database connection details - update these to match your setup
    $host = 'localhost';
    $db_user = 'your_db_username';
    $db_pass = 'your_db_password';
    $db_name = 'your_db_name';
    $url_table = 'your_url_table_name'; // Replace with your actual table name

    // Connect to MySQL
    $conn = mysqli_connect($host, $db_user, $db_pass, $db_name);

    // Check if connection succeeded
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    // Add your existing URL shortening logic here (handling form submission)
    if (isset($_POST['submit'])) {
        // Your current code for generating short URLs and saving to DB goes here
        // ...
    }

    // Fetch all URL records from the table
    $fetch_query = "SELECT id, longurl, shorturl FROM $url_table";
    $result = mysqli_query($conn, $fetch_query);

    // Store results in an array for frontend rendering
    $url_records = [];
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $url_records[] = $row;
        }
    }

    // Close the database connection
    mysqli_close($conn);
    ?>

    <br><br>
    <h2>Stored URL Records</h2>
    <table border="1" cellpadding="8" cellspacing="0">
        <tr>
            <th>ID</th>
            <th>Original URL</th>
            <th>Shortened URL</th>
        </tr>
        <?php if (!empty($url_records)): ?>
            <?php foreach ($url_records as $record): ?>
            <tr>
                <td><?php echo htmlspecialchars($record['id']); ?></td>
                <td><a href="<?php echo htmlspecialchars($record['longurl']); ?>" target="_blank"><?php echo htmlspecialchars($record['longurl']); ?></a></td>
                <td><?php echo htmlspecialchars($record['shorturl']); ?></td>
            </tr>
            <?php endforeach; ?>
        <?php else: ?>
            <tr>
                <td colspan="3">No URL records found in the database.</td>
            </tr>
        <?php endif; ?>
    </table>
</center>
</body> 
</html>

• Replace Placeholders: Don't forget to swap out the database credentials and table name with your actual values.
• Security: Using htmlspecialchars() prevents XSS attacks when displaying user-provided URLs. For production, consider using prepared statements for database queries (especially if you're handling user input beyond the URL form).
• Existing Logic: Keep your current URL shortening code in the if (isset($_POST['submit'])) block as before.

内容的提问来源于stack exchange，提问作者Azazel