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 — Phantom
– Phantom, Commented Jul 1, 2014 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
– Jay Blanchard, Commented Jul 1, 2014 at 13:42

Heaven42 · Accepted Answer · 2014-07-01 13:39:57Z

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 1, 2014 at 13:39

Heaven42

3291 silver badge13 bronze badges

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

Comments

ebo · Accepted Answer · 2014-07-01 13:44:27Z

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 1, 2014 at 13:44

ebo

2,7471 gold badge27 silver badges22 bronze badges

2 Comments

WhiteLine Over a year ago

$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()

ebo Over a year ago

Which version of PHP are you using? Depending on which version you might need to install this extension if possible.

Collectives™ on Stack Overflow

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 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE 1 :

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

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related