Import HTML Table into MySQL Database

By | November 6, 2015

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.

Create a New Database

Log in to MySQL from the command line:

Create a new database:

Grant your username all privileges to the new database:

Select the database just created:

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:

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:

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:

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

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:

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:

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:

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.

 

 

Leave a Reply

Your email address will not be published.

*