Do you have an HTML table that you’d like to import into a MySQL database? This post will explain how to do it. The process involves creating a new database (unless you already have one), creating a new table, and importing the table using an HTML DOM parser script.

Contents

Create a New Database

mysql -u username -p

Create a new database:

CREATE DATABASE mywebsite;

Grant your username all privileges to the new database:

GRANT ALL PRIVILEGES ON mywebsite.* TO "mywebsite"@"localhost" IDENTIFIED BY "CantGuessThisPassword";

Select the database just created:

USE mywebsite

Tip: To sign into MySQL with the database already selected, issue the command mysql -u root -p [databasename]

Create a New Table

Create a new table into which we will import the HTML table:

CREATE TABLE members (id INT PRIMARY_KEY AUTO_INCREMENT, date DATE, firstname VARCHAR(20), lastname VARCHAR(20), country VARCHAR(20));

It’s a good idea to have a primary key that autoincrements, to make identifying and deleting rows easier.

If you forget to add an auto-increment ID field during table creation, you can add it after the fact with the following statement:

ALTER TABLE members ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

The FIRST keyword at the end of the statement will make make it the first column in the table.

To see all the tables that now exist in the mywebsite database, issue the command:

SHOW TABLES;

To verify the column names and data types of your new table:

DESCRIBE members;

Import the HTML Table

Let’s move on to importing the HTML table into the newly created MySQL table. For this step, you will need to download an HTML DOM Parser to parse the HTML table.

You may want to validate the extracted data with mysqli_real_escape_string() before inserting it into MySQL. The data will not import correctly if it contains single quotes, for example.

Let’s say you have a file named table.html and it contains the following table:

<html>
<body>
<table border="1">
<!-- Do not process table heading <tr>
<th>First name</th>
<th>Last name</th>
<th>Country</th>
</tr> -->
<tr>
<td>John</td>
<td>Smith</td>
<td>England</td>
</tr>
<tr>
<td>Terry</td>
<td>Smith</td>
<td>Canada</td>
</tr>
</table>
</body>
</html>

Upload it to a directory on your web server, along with the PHP DOM Parser file.

Create a PHP script in the same directory containing your own tailored version of the following:

require_once ('simple_html_dom.php');

$table = file_get_html('table.html');   // This could also be a php file

foreach($table ->find('tr') as $tr) {     // Foreach row in the table!
$firstname = $tr->find('td', 0)->plaintext; // Find the first TD (starts with 0)
$lastname = $tr->find('td', 1)->plaintext; // Find the second TD (which will be 1)
$country = $tr->find('td', 2)->plaintext;

//Now validate the data with mysqli_real_escape_string(). This function will escape characters that cause problems, like single quotes.
//Note there needs to be an open connection to the MySQL server for this work, otherwise you'll have blank strings returned.
$firstname_c = mysqli_real_escape_string($conn, $firstname);
$lastname_c = mysqli_real_escape_string($conn, $lastname);
$country_c = mysqli_real_escape_string($conn, $country);

//Echo the INSERT statements before commiting them to the MySQL table. Note that the NOW() function will insert the current date into the date column.
echo "INSERT INTO members (date, firstname, lastname, country) VALUES (NOW(), '$firstname_c', '$lastname_c', '$country_c') <br />";
//Create your SQL query
$sql = "INSERT INTO members (date, firstname, lastname, country) VALUES (NOW(), '$firstname_c', '$lastname_c', '$country_c')";

// Uncomment this code after verifying that the echo statements produce valid INSERT queries.
//                  if ($conn->query($sql) === TRUE) {
//                      echo "New record created successfully <br>";
//                  } else {
//                      echo "Error: " . $sql . "<br>" . $conn->error;
//                  }

}

After you execute the script, the data should be extracted from the HTML table and copied into your new MySQL table.

To verify that the data entered the database correctly, check from the MySQL console by issuing:

SELECT * FROM members;

Another way to verify would be to dynamically generate an HTML table using PHP and an active MySQL connection.

Sources

Inserting Data into a MySQL Database Table

Check out this answer on Stack Overflow that explains how to use the HTML DOM parser to extract data from an HTML table.

Import HTML Table into MySQL Database

Create a New Database

Create a New Table

Import the HTML Table

Sources

Leave a Reply Cancel reply