Problem with PDO php 5.5.9 and mysql 5.4 on Ubuntu 14.04

March 18, 2017 343 views
PHP MySQL LAMP Stack Ubuntu

Hi,

I have created a droplet with one click app LAMP 14.04. I have read several help pages on how to enable PDO and have added extension=pdo.so and extension=pdo_mysql.so -lines to /etc/php5/apache2/php.ini and restarted apache.

In info.php:

Additional .ini files parsed include
/etc/php5/apache2/conf.d/05-opcache.ini, /etc/php5/apache2/conf.d/10-pdo.ini, /etc/php5/apache2/conf.d/20-apcu.ini, /etc/php5/apache2/conf.d/20-gd.ini, /etc/php5/apache2/conf.d/20-json.ini, /etc/php5/apache2/conf.d/20-mysql.ini, /etc/php5/apache2/conf.d/20-mysqli.ini, /etc/php5/apache2/conf.d/20-pdo_mysql.ini, /etc/php5/apache2/conf.d/20-readline.ini 

php -m on the command line shows PDO and pdo_mysql.

I have tried out learn what is wrong for a few hours now and can't get the problem solved.

Thank you for your help in advance,

Antti Karttunen

2 Answers
jtittle March 19, 2017
Accepted Answer

@AnttiKarttunen

I actually work with PHP quite a bit, so if you can share the rest of the class, I can take a look at what's running to see where there may be an issue. The snippet you provided cites a getPdo method, but I don't see a reference to that.

As a quick reference, here's a really simple MySQL Class that uses PDO.

<?php
use PDO;
use PDOException;

class MySql
{
    protected $db;
    protected $dbChar;
    protected $dbHost;
    protected $dbName;
    protected $dbOpts               =   [];
    protected $dbPass;
    protected $dbPort;
    protected $dbUser;
    protected $dsn;

    public function __construct( array $params )
    {
        list(
            'dbChar' => $this->dbChar,
            'dbHost' => $this->dbHost,
            'dbName' => $this->dbName,
            'dbOpts' => $this->dbOpts,
            'dbPass' => $this->dbPass,
            'dbPort' => $this->dbPort,
            'dbUser' => $this->dbUser
        ) = $params;

        $this->dsn   = "mysql:";
        $this->dsn  .= "host=$this->dbHost;";
        $this->dsn  .= "dbname=$this->dbName;";

        if ( ! empty( $this->dbPort ) )
        {
            $this->dsn  .= "port=$this->dbPort;";
        }
        else
        {
            $this->dsn  .= "port:3306;";
        }

        if ( ! empty( $this->dbChar ) )
        {
            $this->dsn  .= "charset=$this->dbChar;";
        }
    }

    public function connect()
    {
        try
        {
            $this->db = new PDO( $this->dsn, $this->dbUser, $this->dbPass, $this->dbOpts );

            if ( isset( $this->db ) )
            {
                return $this->db;
            }

            return false;
        }
        catch ( PDOException $e )
        {
            return $e->getMessage();
        }
    }
}

When constructed, you need to pass an array $params, for example:

$params =  [
    'dbChar'                        =>  'utf8',
    'dbHost'                        =>  'localhost',
    'dbName'                        =>  'mysqldbname',
    'dbOpts'                        =>  [
        PDO::ATTR_DEFAULT_FETCH_MODE    =>  PDO::FETCH_OBJ,
        PDO::ATTR_EMULATE_PREPARES      =>  false,
        PDO::ATTR_ERRMODE               =>  PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_PERSISTENT            =>  true,
        PDO::ATTR_TIMEOUT               =>  30
    ],
    'dbPass'                        =>  'mysqldbpass',
    'dbPort'                        =>  '3306',
    'dbUser'                        =>  'mysqldbuser'
];

Note: You'll need to change mysqldbname, mysqldbpass, and mysqldbuser to match yours.

So to create an instance of this MySql class, I'd use:

$db = new MySql( $params );

To connect, I'd then use:

$dbc = $db->connect();

Now I can run:

$vars    = [
    ':id' => 1
];

$query   = $dbc->prepare('SELECT * FROM tableName WHERE id = :id LIMIT 1');

$query->execute( $vars );

$results = $query->fetchAll();

var_dump( $results );

And that should dump the results from my query.

I just tested this locally and on a Droplet with PHP 7.x and 7.1 and it works, so if you run in to issues with the above, then there most likely is an issue with PDO not working. If that works, then the issue would be in your code.

  • Thank you so much for your help!

    I will try to test your class and compare it to my class and see if I run to problems. I'll let you know what happened :)

  • The issue is now solved. The problem was that the code was also using sqlite in addition to MySQL and sqlite PDO drivers were missing.

    Appreciate your help jtittle!

@AnttiKarttunen

What error(s) are you receiving when trying to make a connection via PDO and what does your code look like (for the connection)?

  • Thankyou jtittle for you quick answer :)

    The error is:
    PDO ERROR: could not find driver
    Fatal error: Call to a member function prepare() on a non-object in /var/www/html/src/Database/Database.php on line 95

        /**
         * Execute given SQL statement.
         * @tested 2016-11-18
         *
         * @param string $query SQL query
         * @param array $data SQL variables
         * @return PDOStatement
         */
        function execute($query, $values=Array())
        {
            $Query = $this->getPdo()->prepare($query);
            $Query->execute($values);
            return $Query;
        }
    
    

    Someone had a suggestion on a forum that write this code in php to find out if PDO is installed (I'm not a programmer, just trying to set up the environment :D

    if (!defined('PDO::ATTR_DRIVER_NAME')) {
    echo 'PDO unavailable';
    }
    elseif (defined('PDO::ATTR_DRIVER_NAME')) {
    echo 'PDO available';
    }
    

    I tried this and it returned PDO available.. So maybe the problem is in the code, although the error message was "Could not find driver".

Have another answer? Share your knowledge.