Can't properly connect to MySql with PDO

Question

First of all I just switched all my PHP files to use PDO, so that's happy. However I'm not very experienced with PHP let alone PDO so I'm having a problem. I run the following query in my SQL databse and it returns the correct values.

SELECT * 
FROM table_gon_0621_516
WHERE id =  '1'

Now I use this in my PHP like so

require_once ("../Android/connect_db.php");

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$table_name = 'table_gon_0621_516';

try { 

    $names = $db->query("
        SELECT * 
        FROM `".$table_name."` 
        WHERE `id` = '1'
        ");

    $rows = $names->fetchAll(PDO::FETCH_ASSOC);
    $col_map = array_flip($rows);
    echo '<pre>', print_r($rows, true), '</pre>';
} catch (PDOException $e){
    die($e->getMessage());
}

and i get this error in my browser

SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected

This is my connection code

<?php

$config['db'] = array (
    'host'      => 'localhost',
    'username'  => 'admin',
    'password'  => 'xxxx', //checked and correct
    'dbname'    => 'xxxx_beta' //checked and correct
);

$db = new PDO(
    'mysql:host = '.$config['db']['host'].';
    dbname = '.$config['db']['dbname'], 
    $config['db']['username'], 
    $config['db']['password']
);
?>

This page loads correctly, there are no errors and I connect successfully. What am I doing wrong?

EDIT

I changed

$data = $names->fetchAll(PDO::FETCH_ASSOC);

to

$data = $names->fetch(PDO::FETCH_ASSOC);

and I'm still having the same problem. I also updated the code above to show this change

2nd EDIT

I changed my code above and now receive a new message, I'm assuming I'm not connected?

Since you are sticking ``$table_name`` into the query, you can replace ``FROM `".$table_name."` `` with ``FROM `$table_name` ``. — mzedeler, Jun 23 '13 at 21:43
@Dagon no, its not. *facepalm* that would probably help wouldn't it? lol — i_me_mine, Jun 23 '13 at 21:45
duplicate of thousand questions on the wrong use of fetchall — Your Common Sense, Jun 23 '13 at 21:45
@YourCommonSense: can you provide a link? (Just tried to find one, but couldn't.) — mzedeler, Jun 23 '13 at 21:48
@mzedeler YourCommonSense has an entire FAQ post about PDO, I doubt he likes all these posts about simple problems. I read his post, but being as new as I am I didn't find my solution there. Meanwhile it probably is there, staring at me in the face — i_me_mine, Jun 23 '13 at 21:52
a recent one http://stackoverflow.com/questions/17262314/pdo-prepared-statement-execution-how-to-get-only-associative-keys — Your Common Sense, Jun 23 '13 at 21:53
One way to find out where the problem is, is to put `die("HERE");` after the first line. If you see HERE then that's not it, and you keep moving the `die("HERE");` down a line until you see the server error instead and that's the line. That's kinda the slow way to debug without the error reporting on but it works. — chrislondon, Jun 23 '13 at 22:03
Show content of `connect_db.php` where you create a PDO object — peterm, Jun 23 '13 at 22:28

peterm · Accepted Answer · 2013-06-23T23:30:43.637

4

Change

$db = new PDO(
    'mysql:host = '.$config['db']['host'].';
    dbname = '.$config['db']['dbname'], 
    $config['db']['username'], 
    $config['db']['password']
);

to

$db = new PDO('mysql:host='.$config['db']['host'].';dbname='.$config['db']['dbname'], 
    $config['db']['username'], 
    $config['db']['password']
);

or

$db = new PDO(
    "mysql:host={$config['db']['host']};dbname={$config['db']['dbname']}",
    $config['db']['username'], 
    $config['db']['password']
);

Apparently MySql PDO driver doesn't like spaces in DSN.

mysql:host='.$config['db']['host'].';dbname='.$config['db']['dbname']
          ^^                               ^^

edited Jun 23 '13 at 23:30

answered Jun 23 '13 at 22:38

peterm

91,357
15
148
157

no kidding? I was just trying to make it easy to read... lol. I'll try this and I sincerely hope you're right – i_me_mine Jun 23 '13 at 22:40
1

It worked! I can't believe that was the problem, well so much for easy to read code. I'll put it all on one line if I have to from now on. Thank you so much. – i_me_mine Jun 23 '13 at 22:42
@i_me_mine You can still make it easy to read. Just move the single quote and semicolon down to the next line, right before "dbname" – landons Jun 23 '13 at 22:58
1

A quick correction that it's probably the MySQL driver interpreting the DSN, not PDO *per se*. Weirdly, even within an abstraction layer like PDO, these strings have no standard format, and different database drivers interpret the details differently. – IMSoP Jun 23 '13 at 23:09
@IMSoP Fair enough. Since I can't check it for other drivers, I made it explicit about mysql pdo driver in the answer. Thanks. – peterm Jun 23 '13 at 23:32

Can't properly connect to MySql with PDO

1 Answers1