SQL execute error

[Naroshi]

I'm still a novice, and have read through the manual, stack, and several other places to try to sort this error out. I don't understand why my code works without fail on some days, and then other days I can open it without having changed anything from where I last left it, only to get hit with errors.

 

I'm running this from a USB and using PHPStorm. I have created a small database ran through localhost with three tables, each of which has some test data thrown in. The goal is to load the localhost page and display the table data (read) as well as allow for new data to be entered(create), current data to be edited (update), or allow for the current data to be removed (delete). On the days that the code works and isn't throwing errors, all of that seems to go well, even if the code itself is written in a bit of an unorthodox manner. I've used a foreach to populate all of the table data for a specified table, which has its name hardcoded in only one place at the very top of the index page. However, I have made each row into a form of its own and using hidden fields, the field in each row allows for updating/deleting on that specific row and the action is set to call back to itself and refresh the page. At the bottom, under the table, there's another form that adjusts how many input texts there are according to the number of columns in the given table to allow a new entry to be input into that specified table, and that form also calls back on itself with #. All that aside, when it's not throwing errors, the code works as it should, and despite being a bit convoluted, its surprisingly clean overall. I'm sure there's a better way, and trying to use a bootstrap class to tie things together is still a bit of a shaky concept, but for now I can't seem to make sense of this error in order to even see output so that I can try to make more adjustments.

 

ERROR msg

Quote

Fatal error: Uncaught Error: Call to a member function execute() on boolean in G:\Projects\CRUD\Models\TableBuilder.php:33 Stack trace: #0 G:\Projects\CRUD\Models\TableBuilder.php(11): TableBuilder->setTblCols('Users') #1 G:\Projects\CRUD\Index.php(6): TableBuilder->__construct('Users') #2 {main} thrown in G:\Projects\CRUD\Models\TableBuilder.php on line 33

Index.php

<?php
require 'Bootstrap.php';

$tblSelect = "Users";//Modify this variable to display the different tables

$tblData = new TableBuilder($tblSelect);
$colData = $tblData->getTblCols();

//...POST/GET's here, not shown...

?>

PHP using the colData variable to show the column names (still in index.php)

        <?php for ($i = 0; $i < count($colData); $i++): ?>
            <th scope="col"><?= ucwords(str_replace("_", " ", $colData[$i]))?></th>
        <?php endfor; ?>

PHP using the colData variable to display the textboxes and their labels (still in index.php)

                <?php for ($i = 0; $i < count($colData); $i++): ?>
                    <div class="col">
                        <?= ucwords(str_replace("_", " ", $colData[$i])) . ": "?> <br>
                        <input type="text" name= <?=$colData[$i]?>>
                    </div>
                <?php endfor; ?>

 

All of that uses the $tblSelect variable to send the hardcoded table name to TableBuilder.php to setup the table name and column names accordingly. Here's the TableBuilder function that sets/gets the column names, and the error is pointing to the $stmt->execute(); line

 

TableBuilder.php

    private $tblCols = array();
    private $tblName;

    public function __construct($tbl)
    {
        $this->setTblName($tbl);
        $this->setTblCols($tbl);
    }

	public function setTblCols($tbl)
    {
        $pdo = Database::connect("sqlite", "database.sqlite");
        $stmt = $pdo->prepare("SELECT * FROM $tbl");
        $stmt->execute();
        $colNames = array_keys($stmt->fetchAll(PDO::FETCH_ASSOC)[0]);
        array_shift($colNames); //removes id
        $this->tblCols = $colNames;
    }

    public function getTblCols()
    {
        return $this->tblCols;
    }

 

What am I doing wrong, and can someone please help me to understand what this error means and how to resolve it? I have a very similar function in another page that does a select all to populate all of the table data, and that one works, however I'm using sqlite and to get the column names I copied the working select all statement and just modified the fetchAll style from class to assoc. It was working the last time I closed this project, and now I'm not sure why I'm getting this error. Help is much appreciated, thanks.

[Barand]

I looks like the prepare() is failing for $stmt to be "false".

When you create the PDO connection, use these options so errors get reported. This should tell you why it fails.

$options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ];

Also, connect once to PDO and pass the $pdo connection object to the class constructor. Don't connect every time you run a query.

Your method for getting the column names is very inefficient. You retrieve everything from the tabel just to get the names. What if the table had a million rows or more? If it had no rows, what then?

Try

public function setTblCols($tbl)
{
    $stmt = $this->pdo->query("SHOW COLUMNS FROM product");
    $data = $stmt->fetchAll();
    $colNames = array_column($data,'Field');
    array_shift($colNames); //removes id
    $this->tblCols = $colNames;
}

 

Edited July 21, 2018 by Barand

[Barand]

EDIT: Apologies for my "product" test table name above instead of `$tbl` but this forum software stubbornly refuses my change to the code.

[Naroshi]

The PDO is created from a seperate class with a static function call. Simply because that's how I was told to set it up.  From there I was told to use a bootstrap class to hold all of the requires and includes, and without getting an endless loop, the only way for me to access my PDO with the current setup is to create a new PDO object each time. It's an academic assignment, and the specific class designs we are required to use are what's causing me so much headache.

 

Also, since we are using Sqlite, I don't think the SHOW COLUMNS will work. Isn't that only for MySQL?

I absolutely hate assignments of this nature without freedom to setup classes in a way that makes sense, but the class is setup with different challenges in place to make sure people understand static etc and designed in such a way as to accommodate everyone no matter what level they're at. I'm more comfortable with languages such as C# and PHP simply makes me cringe a little.

So my PDO setup is as follows, with a few else's to work with database options even though we aren't using them:

Database.php

    public static function connect($type, $location, $dbname = null, $username = null, $password = null)
    {
        if($type == "sqlite")
        {
            $pdo = new PDO("$type:$location");
        }

 and a bootstrap class as follows:

bootstrap.php

<?php

require 'DB/QueryBuilder.php';
require_once 'DB/Database.php';
require 'Models/TableBuilder.php';

 The only place in the whole project where i have a require statement for bootstrap is in the index.php file at the top. From my understanding, Include/Require, is similar to the using keyword in a language such as C#, correct?

[Barand]

At least, just use fetch() to get a sigle row instead of fetchAll().

If sqlite supports LIMIT 1 (or TOP 1) use that.

[Naroshi]

The problem with using fetch is that this is ran based on id as a primary key in the main portion of this project where the data is actually displayed and the user given buttons to chose to create, delete, or edit a row, and since there's the option to delete rows there's no way of knowing which rows are actually in the table and which aren't which is why the statement is based on a SELECT *

Edited July 21, 2018 by Naroshi

[Barand]

After working with databases for over 30 years I ought to have been able to understand that sentence, yet I cannot make sense of it at all.

[benanamen]

5 minutes ago, Barand said:

After working with databases for over 30 years I ought to have been able to understand that sentence, yet I cannot make sense of it at all.

Yeah, I was thinking the same exact thing. OP, what ever you are doing is wrong. How about telling us what the real problem is you are trying to solve. We will show you the correct way to solve the problem, then you can attempt to translate it to the bass ackwards requirements imposed on you.

[Barand]

This should be compatible and more efficient than the version have now. Note it won't work with an empty table (but neither will your version). Note it also assumes you correctly passed the PDO connection to your constructor.

public function setTblCols($tbl)
{
    $stmt = $this->pdo->query("SELECT * FROM `$tbl` LIMIT 1");
    $data = $stmt->fetch();
    $colNames = array_keys($data);
    array_shift($colNames); //removes id
    $this->tblCols = $colNames;
}

 

[mac_gyver]

the sqlite query equivalent to SHOW COLUMNS ... is - PRAGMA table_info(table_name_here)

the column name for the array_column() statement would be 'name'

btw - the array_shifting to remove a column assumes the columns are defined in a particular order, that cannot be guaranteed. the PRAGMA  ... query result contains a [pk] element that can be used to identify which column is defined as a primary key, assuming you actually need to remove a column from the list of columns.