0

I can't work out why the below 'check if mysql table exists function' is no longer working having upgraded my php version to 7.2

It now always returns false/an empty array, even when the table exists.

I have tried checking throughout whether the variables are defined & manually outputting their (string) values at each stage. When I do so I get the expected result (i.e. row/s returned) - but I don't seem to be able to bind them.

public function tableExists($name) {
            // Try a select statement against the table
            // Run it in try/catch in case PDO is in ERRMODE_EXCEPTION.

            $dbname = $this->DB_NAME;

            try {
                $result = $this->select("SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = :DB_NAME AND table_name = :name", array(':DB_NAME' => $dbname, ':name' => $name));

            } catch (Exception $e) {

            // We got an exception == table not found
               $result = FALSE; //$e->getMessage();
            }

        // Result is either boolean FALSE (no table found) or PDOStatement Object (table found)
        return $result;         
        }

My select function below definitely receives the correct values so I must be binding them in a way that no longer works in php7.2.

I've also tried bindParam instead of bindValue in the hope that might work, but to no avail.

    /*
    *Select
    *@param string $sql - An SQL string
    *@param array $array - An array of parameters to bind. Default is empty array
    *@param constant $fetchMode - A PDO fetchmode. Default is fetch_assoc
    *return mixed
    */
    public function select($sql, $array = array(), $fetchMode = PDO::FETCH_ASSOC) {

        $sth = $this->prepare($sql);
            foreach ($array as $key => $value) {    

            if($value == (int)$value)
                $sth->bindValue("$key", $value, PDO::PARAM_INT);
            else
                $sth->bindValue("$key", $value);
            }

        $sth->execute();
        $this->rowCount = $sth->rowCount(); 

        return $sth->fetchAll($fetchMode);      
    }

Any help much appreciated!

I thought this would help - but it didn't: PDO Statement does not work anymore in PHP 7

Simeon
  • 848
  • 1
  • 11
  • 34
  • 1
    `table_schema = :DB_NAME` you can only bind values - not database|table names. – CD001 Feb 05 '20 at 14:48
  • @CD001 doesn't it look like a value? – Your Common Sense Feb 05 '20 at 14:48
  • @YourCommonSense - ooops, you're right, reading it too fast - assumed because it was a table name it was in the `FROM` not the `WHERE`. – CD001 Feb 05 '20 at 14:50
  • You are misusing (or rather abusing) the try catch here. Remove it from the code and return pdostatement right away (like `return $this->select(...`). – Your Common Sense Feb 05 '20 at 14:52
  • Thanks, but I get the same result without the try catch. – Simeon Feb 05 '20 at 14:55
  • What is "the same" exactly? What does $this->select return? In case it's an empty array then your answer is under P.S. in the linked post. – Your Common Sense Feb 05 '20 at 14:58
  • `$dbname = $this->DB_NAME;` <- `DB_NAME` isn't, by any chance, a static variable or class constant is it? You'd generally use the ALL_UPPER_CASE_WITH_UNDERSCORES type format for class constants ... and they'd be accessed with something like `self::DB_NAME` in PHP 7.2 – CD001 Feb 05 '20 at 14:58
  • Thanks both. I wondered about the static variable thing - but I can print the string and it is the same when I replace that variable with a string (assuming I still try to bind it). It still returns an empty array - but I am certain that there is a match between the data in my DB and both $dbname and $name because I have spent about 3 hours trying to fix it, have used the same code in phpmyadmin & without binding the variables, and everything was working before I upgraded to php7.2 – Simeon Feb 05 '20 at 15:14
  • table and dbname you can't bind as dynamic value by using bindValue, you can try like this `$sql = sprintf("SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = %s AND table_name = %s", $dbname, $table)` – udit rawat Feb 05 '20 at 15:16
  • Whatever the reason, we cannot help. Obviously, binding works in php7.2 as well as in any other version. Obviously, in case for some reason it doesn't, it will cause an error exception. Given your code returns an empty array, there was no error. means binding works and the problem is elsewhere. Make sure you are connection to the same database in phpmyadmin. Create an MCVE. – Your Common Sense Feb 05 '20 at 15:23
  • BTW, https://3v4l.org/oEq88 – Your Common Sense Feb 05 '20 at 15:33
  • Thanks - Well, at least I haven't missed anything obvious this time. I'll check if there's a problem with my install of php7.2/mysql/phpmyadmin – Simeon Feb 05 '20 at 15:57

0 Answers0