Fatal error: Call to a member function fetchAll() on boolean in C:\xampp\htdocs\studysite\test.php on line 16

Question

I'm trying to create columns in my database ,but when I run the code I get

Fatal error: Call to a member function fetchAll() on boolean on line 16

$con = connect(DNS,USERNAME,PASSWORD);
//columns as arrays that i need to create if it doesn't exist
$columnname = array("urine_re","lab_investigation","a_records","crazy_gus");
$table='anc_hsd';

//function to check if column name exist if not create it
function columnExist($item,$table,$column){

  $exists = false;
  $sql = "SHOW COLUMNS FROM  $table";
  $result = $item->query($sql);
  $results=$result->fetchAll(PDO::FETCH_COLUMN);


  if(in_array($column,$results)){
    $exists=true ;
  }

  if(!$exists){
    $sql="ALTER TABLE $table ADD $column  INT(30) NULL";
    $item->exec($sql);
  }
}


//this is where I use the function

foreach($columnname as $key=>$value){
  columnExist($con, $table, $value);
}

`PDO::query() returns a PDOStatement object, or FALSE on failure. ` your query returns a Boolean false — Vishnu Nair, Mar 16 '16 at 20:50
Not related to your question but your code isn't very efficient. You are running a query that gets all the columns from the table multiple times. But you should only run the query once and then compare the results array with your `$columnname` array and add a column if it doesn't exist. — Cave Johnson, Mar 16 '16 at 21:06
what you saying @Andrew is, i should run the query outside of the function? — , Mar 16 '16 at 21:20
Yes, you could do it that way, and then instead of passing the `$table` into `columnExists`, you would pass in the results from the query. Like this: `columnExist($con,$results,$value)` and remove all the code above `if(in_array($column,$results)){` in that function. — Cave Johnson, Mar 16 '16 at 21:28
I don't see why this was downvoted - the question of the OP seems clear to me. — cslotty, Feb 04 '20 at 11:46

score 6 · Answer 1 · answered Mar 16 '16 at 21:01

6

The query() method can return false if the query fails. It is a good habit to check if there are any results before processing them:

 $result = $item->query($sql);
 if ($result) {
     $results=$result->fetchAll(PDO::FETCH_COLUMN);
 }
 else {
      // Handle errors
 }

But that is only error handling and the query still fails. This is most likely because COLUMNS is not the name of a column in your database table.

answered Mar 16 '16 at 21:01

Gentle153

327
2
5
15

you are right,i used a wrong table name,thanks for your input and i really appreciate it – Mar 16 '16 at 21:16

score 0 · Answer 2 · answered Mar 16 '16 at 21:00

Your query failed and it returned FALSE. Then you are applying fetchAll() in a boolean value, and this is impossible. Check the result of this query before using the resource $result.

If you want to be a good programmer, don't be naïve. Never assume your function calls, queries and things like that will work. Always consider the possibility they will fail and check it someway.

In this case you should have a protection like:

$sql = "SHOW COLUMNS FROM  $table";
$result = $item->query($sql);
if ($result !== FALSE) {
    $results=$result->fetchAll(PDO::FETCH_COLUMN);
} else {
    // Alternative code telling what to do when the query fails
}

Hope this helps!

i want to be a good programmer so i just added your error check to my code,thank you too — , Mar 16 '16 at 21:18

Fatal error: Call to a member function fetchAll() on boolean in C:\xampp\htdocs\studysite\test.php on line 16

2 Answers2