PHP+DOM解析HTML表格：学生成绩抓取入库技术求助

Got it, let's walk through how to parse this HTML table and store the student's score data into MySQL using PHP. Your HTML has a clear structure—first row holds student info, followed by rows for each subject. Here's a step-by-step solution:

Step 1: Create MySQL Database Tables #

First, we need two tables to store the data properly (following database normalization): one for student basic info, another for their scores. Run these SQL queries to create them:

-- Table for student basic information
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    reg_no VARCHAR(20) NOT NULL UNIQUE
);

-- Table for subject scores (linked to students table)
CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    marks INT NOT NULL,
    status VARCHAR(10) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

Step 2: Parse HTML Using PHP's DOM Extension #

PHP's built-in DOMDocument and DOMXPath make parsing HTML straightforward. We'll load the HTML, extract the student info first, then loop through the subject rows.

Here's the parsing code:

// Your HTML content (you can also load it from a file/URL using file_get_contents)
$html = '<table border="2"> <tr> <td>Name: Shanthini</td> <td>Reg.No: 12345</td> </tr> <tr> <td>Maths</td> <td>89</td> <td>Pass</td> </tr> <tr> <td>English</td> <td>90</td> <td>Pass</td> </tr> <tr> <td>Chemistry</td> <td>89</td> <td>Pass</td> </tr> </table>';

// Initialize DOMDocument and suppress HTML parsing warnings (common for messy HTML)
$dom = new DOMDocument();
libxml_use_internal_errors(true);
$dom->loadHTML($html);
libxml_clear_errors();

// Use XPath to easily navigate the DOM
$xpath = new DOMXPath($dom);
$rows = $xpath->query('//table/tr');

if ($rows->length === 0) {
    die("No table rows found in the HTML.");
}

// Extract student info from the first row
$firstRow = $rows->item(0);
$studentCells = $firstRow->getElementsByTagName('td');

// Get name (clean up the text by removing "Name: " prefix)
$nameText = trim($studentCells->item(0)->nodeValue);
$studentName = str_replace('Name: ', '', $nameText);

// Get registration number (clean up "Reg.No: " prefix)
$regNoText = trim($studentCells->item(1)->nodeValue);
$studentRegNo = str_replace('Reg.No: ', '', $regNoText);

// Prepare an array to hold subject scores
$scores = [];

// Loop through remaining rows (starting from index 1)
for ($i = 1; $i < $rows->length; $i++) {
    $row = $rows->item($i);
    $cells = $row->getElementsByTagName('td');
    
    if ($cells->length === 3) {
        $scores[] = [
            'subject' => trim($cells->item(0)->nodeValue),
            'marks' => (int)trim($cells->item(1)->nodeValue),
            'status' => trim($cells->item(2)->nodeValue)
        ];
    }
}

Step 3: Insert Data into MySQL with Prepared Statements #

Always use prepared statements to prevent SQL injection. Here's how to connect to MySQL and insert the parsed data:

// Database connection details (replace with your own)
$dbHost = 'localhost';
$dbUser = 'your_username';
$dbPass = 'your_password';
$dbName = 'your_database';

// Create connection
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// First, insert student info into students table
$stmtStudent = $conn->prepare("INSERT INTO students (name, reg_no) VALUES (?, ?)");
$stmtStudent->bind_param("ss", $studentName, $studentRegNo);

if ($stmtStudent->execute()) {
    // Get the auto-generated student ID
    $studentId = $stmtStudent->insert_id;
    
    // Now insert each subject score into scores table
    $stmtScore = $conn->prepare("INSERT INTO scores (student_id, subject, marks, status) VALUES (?, ?, ?, ?)");
    $stmtScore->bind_param("isis", $studentId, $subject, $marks, $status);
    
    foreach ($scores as $score) {
        $subject = $score['subject'];
        $marks = $score['marks'];
        $status = $score['status'];
        $stmtScore->execute();
    }
    
    echo "Data inserted successfully!";
} else {
    echo "Error inserting student: " . $stmtStudent->error;
}

// Close statements and connection
$stmtStudent->close();
$stmtScore->close();
$conn->close();

Key Notes: #

• Error Handling: I added basic error checks, but you can expand them (e.g., handle duplicate registration numbers, validate marks are numeric).
• Loading HTML: If you're pulling the HTML from a URL, replace the static $html variable with file_get_contents('https://your-school-server-url.com/grades')—just make sure your server allows outgoing requests.
• HTML Variations: If the school's HTML changes slightly (e.g., different class names for the table), you can adjust the XPath query (e.g., //table[@class='grade-table']/tr if the table has a class).

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