CODEWITHSUNDEEP
phpmysqlsql
Thomas De Marez
Thomas De Marez

Reputation: 666

Moving html table content to sql database

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!

Upvotes: 0

Views: 441

Answers (4)

plain jane
plain jane

Reputation: 1007

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);
  }
?>

Upvotes: 1

Thanos Darkadakis
Thanos Darkadakis

Reputation: 1729

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)

Upvotes: -1

Barmar
Barmar

Reputation: 781310

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);
}

Upvotes: 0

NDM
NDM

Reputation: 6830

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.

Upvotes: 1

Related Questions