如何连接ODBC SQL Server（MRP系统）并在网页展示自定义SQL查询结果？

Hey there! Let's work through this together—you're trying to build a factory visualization page that pulls data from your MRP system's ODBC SQL Server, but stuck on connections and displaying results, plus you're unsure about the tech stack. No worries, let's break this down simply:

You don't need to overcomplicate things. Since you've seen a lot of PHP+MySQL implementations, PHP + HTML is perfect for your needs—PHP has solid support for ODBC connections, and combining it with HTML lets you build a functional visualization page quickly. It's low-effort, aligns with what you've researched, and will get the job done for factory floor displays. If you're curious about alternatives, Python's Flask is also an option, but PHP is more direct for web deployment here.

First, make sure your environment has the right setup:

• Install the matching ODBC Driver for SQL Server (e.g., ODBC Driver 17 for SQL Server—check your SQL Server version to pick the right one)
• Configure the ODBC data source (use Windows' ODBC Data Source Manager, or unixODBC if you're on Linux)

Then here's a simple PHP connection snippet you can adapt:

<?php
// Update these values to match your MRP system
$dsn = "Driver={ODBC Driver 17 for SQL Server};Server=YOUR_SERVER_ADDRESS;Database=YOUR_MRP_DB_NAME;";
$db_user = "YOUR_DB_USERNAME";
$db_pass = "YOUR_DB_PASSWORD";

// Attempt connection
$conn = odbc_connect($dsn, $db_user, $db_pass);

if (!$conn) {
    die("Connection failed: " . odbc_errormsg());
} else {
    echo "Connected successfully!";
}
?>

Quick note for Linux servers: Make sure you have unixODBC and PHP's ODBC extension installed—run sudo apt-get install unixodbc unixodbc-dev php-odbc on Ubuntu/Debian.

Once connected, you can pull your data and render it with HTML. Here's a combined PHP+HTML example that shows production order status (tweak the query to match your MRP's tables):

<?php
// Connection code from above
$conn = odbc_connect($dsn, $db_user, $db_pass);

if ($conn) {
    // Your custom query—adjust this to get the data you need
    $query = "SELECT OrderID, ProductName, CompletionRate FROM ProductionOrders WHERE Workshop='Assembly'";
    $result = odbc_exec($conn, $query);

    if (!$result) {
        die("Query failed: " . odbc_errormsg($conn));
    }
?>
<!DOCTYPE html>
<html>
<head>
    <title>MRP Factory Visualization</title>
    <style>
        /* Simple styling for factory screen readability */
        body { font-family: Arial, sans-serif; margin: 2rem; }
        h1 { text-align: center; color: #333; }
        table { width: 100%; border-collapse: collapse; margin-top: 2rem; }
        th, td { border: 1px solid #ddd; padding: 1rem; text-align: center; }
        th { background-color: #f0f0f0; }
        .high-progress { background-color: #d4edda; }
        .low-progress { background-color: #f8d7da; }
    </style>
</head>
<body>
    <h1>Assembly Workshop Production Status</h1>
    <table>
        <tr>
            <th>Order Number</th>
            <th>Product Name</th>
            <th>Completion Rate</th>
        </tr>
        <?php
        // Loop through results and render rows
        while ($row = odbc_fetch_array($result)) {
            // Add color coding based on completion rate
            $rowClass = $row['CompletionRate'] >= 80 ? 'high-progress' : ($row['CompletionRate'] < 30 ? 'low-progress' : '');
        ?>
        <tr class="<?php echo $rowClass; ?>">
            <td><?php echo $row['OrderID']; ?></td>
            <td><?php echo $row['ProductName']; ?></td>
            <td><?php echo $row['CompletionRate'] . '%'; ?></td>
        </tr>
        <?php } ?>
    </table>
</body>
</html>
<?php
    // Clean up connection
    odbc_close($conn);
}
?>

• For more intuitive displays (like progress bars), add simple inline styles or use a lightweight JS library like Chart.js (no heavy frameworks needed):

<td>
    <div style="width: 100%; background: #eee; border-radius: 4px;">
        <div style="width: <?php echo $row['CompletionRate']; ?>%; height: 20px; background: #4CAF50; border-radius: 4px;"></div>
    </div>
    <p><?php echo $row['CompletionRate'] . '%'; ?></p>
</td>

• Test locally first with tools like XAMPP (Windows) or LAMP (Linux) before deploying to a factory server.
• Restrict the database user's permissions to read-only—this prevents accidental changes to your MRP system's data.

内容的提问来源于stack exchange，提问作者RBC Kyle Bullard