SQLITE : Check if column exists without trigger an error in php

Question

My situation is this:

I have an array that contains the names of the columns of a table
I must, with a loop , iterates through the array and check if that field (this column name) exists in the table

If it exists, I do not do anything, otherwise I have to add

$array_column = ["column1 NOT NULL","column2 NOT NULL","column3 NOT NULL","column4 NOT NULL"];

for($j = 0 ; $j < count($array_column) ; $j++){

  $query = "SELECT ".$array_column[$j]." FROM my_table";
  $result = $db->query($query);

  $check = false;
  //try{
     foreach ($result as $row) { //error here
        $check = true;
     }
  //}catch (Exception $e) {
    //echo "here";
    //$check = false;
  //}

  if(!$check){
    echo "column [".$array_column[$j]."] to add <br>";
  } 

}

But when I find a column that does not exist in the table, php gives me error

Warning: Invalid argument supplied for foreach ()

I also tried a TryCatch, but the same error. I wish that php did not return that error when it doesn't finds a column

UPDATE 1 :

i try this , it works, but unfortunately not when there are no records in the table

$select = $db->query('SELECT * FROM mytable');

$total_column = $select->columnCount();
var_dump($total_column);

for ($counter = 0; $counter <= $total_column; $counter ++) {
      $meta = $select->getColumnMeta($counter);
      $column[] = $meta['name'];
}
print_r($column);

UPDATE 2 : This solution works perfectly, even if there are no records in the table!

$result = $db->query("PRAGMA table_info(test2)");
$result->setFetchMode(PDO::FETCH_ASSOC);
$meta = array();
foreach ($result as $row) {
   array_push($meta, $row['name']);
}

possible duplicate of [MySQL, Check if a column exists in a table with SQL](http://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql) — Phantom, Jul 01 '14 at 13:39
Not a duplicate @Phantom because of the way that you have to get the data back from SQLite vs. the way you would with MySQL. — Jay Blanchard, Jul 01 '14 at 13:42

score 4 · Answer 1 · answered Jul 01 '14 at 13:39

4

PRAGMA table_info('table_name') will return a list of row ( one for each column on your table) Unfortunately you can't use it in a select query but you can parse it and try to look for your column name before querying on your table. Hope that's help

answered Jul 01 '14 at 13:39

Heaven42

329
1
13

score 1 · Answer 2 · answered Jul 01 '14 at 13:44

1

You can use the sqlite_fetch_column_types function to get an array with the column names of your table. This array can then be used to in your loop.

answered Jul 01 '14 at 13:44

ebo

2,717
1
27
22

1

$db = new PDO('sqlite:info.sqlite3'); $cols = sqlite_fetch_column_types('telecamere', $db, SQLITE_ASSOC); //Fatal error: Call to undefined function sqlite_fetch_column_types() – WhiteLine Jul 01 '14 at 13:48
Which version of PHP are you using? Depending on which version you might need to [install this extension](http://www.php.net/manual/en/sqlite.installation.php) if possible. – ebo Jul 01 '14 at 14:07

SQLITE : Check if column exists without trigger an error in php

UPDATE 1 :

UPDATE 2 : This solution works perfectly, even if there are no records in the table!

2 Answers2