Moving html table content to sql database

Question

I've got a table (grabbed from another website using php) but now I want to import it's content into my database (instead of just printing out the table).

This is the printout (https://i.sstatic.net/NnbAo.png) from the table using the following code:

<?php
$uitvoer = getTable();
echo "<table">;
foreach ($uitvoer as $row) {
    echo "<tr>";
    foreach ($row as $col) {
        echo "<td>" . $col . "</td>";
    }
echo "</tr>";
}
echo"</table>";
?>

And when I try this nothing happens to my database:

<?php
$con = mysqli_connect("mysql1.000webhost.com","a2902119_lowavol","pswd")
$uitvoer = getTable();
foreach ($uitvoer as $row) {
    foreach ($row as $col) {
        $query += "'" . $col . "', ";
    }
$sql="INSERT INTO test (cel1, cel2, cel3, cel4, cel5, cel6, cel7, cel8, cel9) VALUES ($query)";
mysqli_query($con, $sql);
}
?>

I have little to no knowledge about PHP. Thanks for helping me!

possible duplicate of filling a database with html table content — Barmar
– Barmar, Commented Sep 19, 2013 at 8:32
You're close, you just have to remove the extra comma at the end of the VALUES list. — Barmar
– Barmar, Commented Sep 19, 2013 at 8:33
Since you're using mysqli, it would be better to use a prepared statement instead of concatenating strings. — Barmar
– Barmar, Commented Sep 19, 2013 at 8:34

marc_s · Accepted Answer · 2015-05-02 18:29:18Z

1

The concatenation operator for PHP is (.) and not (+)

  <?php
    $con = mysqli_connect("mysql1.000webhost.com","a2902119_lowavol","pswd")
    $uitvoer = getTable();
    foreach ($uitvoer as $row) {
       $query = array();
       $insert = "";
       $insert_values = "";
       foreach ($row as $col) {
           $query[] = $col;
       }
       $insert_values = implode("','", $query) ;
       $insert = "'".$insert_values."'";
       $sql = "INSERT INTO test (cel1, cel2, cel3, cel4, cel5, cel6, cel7, cel8, cel9) VALUES ($insert)";
       mysqli_query($con, $sql);
  }
?>

edited May 2, 2015 at 18:29

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 19, 2013 at 8:48

plain jane

1,0041 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas De Marez Over a year ago

works! thanks just had to use the $querry[] = strip_tags($col); instead of $querry[] = $col; to remove html tags before entering them in the database! AWESOME

plain jane Over a year ago

happy to have helped!

Thomas De Marez Over a year ago

is there a way to put the first 5 rows in table A and the last 5 rows in table B if there were 10 tables in total? So just splitting the number of rows in two and deviding the first half and second half over two different tables?

plain jane Over a year ago

10 tables or 10 rows?

NDM · Accepted Answer · 2013-09-19 08:34:10Z

1

your query will look like this:

INSERT INTO test (....) VALUES (val, val, val, )

which is obviously a syntax error... try and add the fields to an array instead:

foreach ($uitvoer as $row) {
    $fields = array();
    foreach ($row as $col) {
        $fields[] = $col; // TODO: add escaping!
    }
// now you can build the values list with implode
$values = implode(', ', $fields);
$sql="INSERT INTO test (...) VALUES ($values)";

See the docs about implode() for more info.

answered Sep 19, 2013 at 8:34

NDM

6,8483 gold badges42 silver badges55 bronze badges

3 Comments

Thomas De Marez Over a year ago

i'm almost there thanks! but in the echo of the prepared sql statement there are a lot of returns i'm thinking this could be the reason no data is in the database yet? see link thomasdemarez.site88.net/heren1kfetch.php

NDM Over a year ago

try $fields[] = trim($col);, although those returns should not be a problem.

NDM Over a year ago

Looking at the source though, your values are containing HTML!

Barmar · Accepted Answer · 2013-09-19 08:39:14Z

0

Use a prepared statement:

$sql="INSERT INTO test (cel1, cel2, cel3, cel4, cel5, cel6, cel7, cel8, cel9) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($con, $sql);
mysqli_stmt_bind_param($stmt, "sssssssss", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5], $row[6], $row[7], $row[8]);
foreach ($uitvoer as $row) {
    mysqli_stmt_execute($stmt);
}

answered Sep 19, 2013 at 8:39

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

Thomas De Marez Over a year ago

doesn't seem to work.. I get two errors when loading the page 1. Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in /home/a2902119/public_html/heren1kfetch.php on line 22 2. Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /home/a2902119/public_html/heren1kfetch.php on line 20

marc_s · Accepted Answer · 2015-05-02 18:28:00Z

-1

Try echo-ing $sql. I think there is a comma in the end.

Use

$query = substr($query, 0, strlen($query) - 1)

in order to remove the last character of $query

(edited the command)

edited May 2, 2015 at 18:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 19, 2013 at 8:37

Thanos Darkadakis

1,7292 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

Moving html table content to sql database

4 Answers 4

4 Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related