Hi guys!

Im fairly new to PHP and I am trying to submit some data to an sqlite database, however it seems to not be working as intended. When I run the file, I get "Warning: Invalid argument supplied for foreach() in line 21". Not sure why, as the parameters are appropriate. I suspect its because the specified table isnt being made.

NB: I am using DB Browser for SQLITE.

Any assistance would be appreciated! Thanks!

<?php

try {

$db = new PDO('sqlite:newTest.db');

$db->exec("CREATE TABLE userinfo(id INTEGER PRIMARY KEY, name TEXT, surname TEXT, age TEXT");

$db->exec("INSERT INTO userinfo(id, name, surname, age) VALUES (1, 'nick', 'francis', '21')'; ");
$db->exec("INSERT INTO userinfo(id, name, surname, age) VALUES (2, 'tris', 'test', '23')'; ");
$db->exec("INSERT INTO userinfo(id, name, surname, age) VALUES (3, 'john', 'doe', '24')'; ");
     
print "<table border = 1>"; print "<tr><td>ID</td><td> Name </td> <td> Surname </td> <td> Age </td> <tr>"; $result = $db->query('SELECT * from userinfo'); foreach($result as $row) { print "<tr><td>".$row['id']."</td>";
print "<td>".$row['name']."</td>"; print "<td>".$row['surname']."</td>"; print "<td>".$row['age']."</td></tr>"; }; print "</table>"; } catch(PDOException $e) { echo $e-> getMessage(); }; ?>

    (I edited your post to use [code=php]...[/code] tags; the ` form the editor provides really only works inline.)

    You've got several problems with your SQL:

    1. When creating the table you are missing the closing ) from the CREATE TABLE statement. So no, the table isn't being made (and the error message reported is "Incomplete Input")
    2. When inserting, you have extra ' characters on the ends of your INSERT statements, just before the semicolons.

      Just some suggestions for cleaner/safer code (untested):

      // make the primary key auto-increment, then you don't have to specify it later
      $db->exec("CREATE TABLE userinfo(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, surname TEXT, age TEXT");
      
      // user prepared statement to avoid SQL injection
      $stmt = $db->prepare("INSERT INTO userinfo(name, surname, age) VALUES (:name, :surname, :age)");
      $data = [
          ['nick', 'francis', 21],
          ['tris', 'test', 23],
          ['john', 'doe', 24]
      ];
      foreach($data as $row) {
          $stmt->exec([':name' => $row[0], ':surname' => $row[1], ':age' => $row[2]]);
      }
      
      print "<table border = 1>";
      // etc....

        You should also be checking for errors and result values when you run queries. Your code is just calling $db->exec() with all kinds of SQL and you never bother to check whether it succeeded or if there was some error condition.

          Write a Reply...