Function Issues

rjoel98 · November 8, 2019, 8:13pm

So I have built this function to do a search of the database/table specified to return if it exists or not.

function dataSearch($table, $date, $team, $record, $storeId){
        global $conn;
        $sql = $conn->prepare("SELECT entryId, date FROM ? WHERE date = ? && storeId = ? && record = ? LIMIT 1");
        $sql->bind_param("ssss", $table, $date, $storeId, $record);
        $sql->execute();
        $result = $sql->get_result();
        $result = $result->fetch_assoc();
 if ($date === $result["date"]) {
            echo "Skipping Entry, Data Already Exists in Database";
        } else {
  ***Input Data***

The issue I am running into is with the line:
$sql->bind_param("ssss", $table, $date, $storeId, $record);

And every time I run the code I get this error:
PHP Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in /var/www/html/engine6.0.php:25

Which is weird because all of the variables I am passing are strings, not booleans…

I feel like I am missing something that is fairly obvious, or just taking an odd approach that has a better option. Anyways, any help would be greatly appreciated! And thank you in advance!

phdr · November 8, 2019, 8:31pm

You cannot supply table names via a prepared query place-holder. You can only supply data values. If you had error handling for the database statements (connection, query, prepare, and execute) you should be getting an sql error from the prepare statement. The error you are getting is a ‘follow on’ php error due to the code continuing to run past the point where the actual problem occurred at. The php error you are getting is referring to the bind_param() statement and means that the $sql variable is a boolean (false) due to the prepare statement failing.

To add error handling for all the database statements, without needing to add logic at each one that can fail, use exceptions for database statement errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database errors will get displayed/logged the same as php errors.) To use exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

phdr · November 8, 2019, 8:50pm

As a separate issue. Don’t try to SELECT data in order to decide if it should be inserted.

Instead, define the column(s) in your database table as unique index(es), just insert the data, and detect if a duplicate key error occurs. The data will either get inserted, if it doesn’t exist, or you will get an sql error number that you can test for to setup the duplicate/already exist message. This is a case where your code would catch and handle the exception. If the sql error number is not one that your code can handle, you would re-throw the exception and let php handle it.