Syntax error or access violation using PDO

Question

I'm migrating my local files online and having PHP issues with my db script. Works fine locally (running PHP 5.3.8) but gives me the following errors on my server (PHP 5.3.10)

Without $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Fatal error: Call to a member function setFetchMode() on a non-object in /.../...php on line 108

With $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user '...'@'205.186.180.26' for table 'invited'

Here is my code:

class guest {
    public $id;
    public $guest_name;
    public $url_phrase;
}

$guest = new guest;

if (isset($_GET['p'])) {
    $url_phrase = urldecode($_GET['p']); 
} else if (isset($_POST['p'])) {
    $url_phrase = urldecode($_POST['p']); 
}

if (isset($url_phrase)) {

    try {

    $DBH = new PDO("mysql:host=myhost.com;dbname=mydbname", "myusername", "mypassword");
    $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $url_phrase = $DBH->quote($url_phrase);
    $sql = "SELECT id, guest_name, url_phrase FROM mydbname.invited WHERE url_phrase = $url_phrase";

    $stmt = $DBH->query($sql);
    $stmt->setFetchMode(PDO::FETCH_INTO, new guest);

    $guestNumber = $stmt->rowCount();

    if($guestNumber > 0) {
        etc...  
    }
    if($guestType == "solo") {
        foreach($stmt as $guest) {
            $name = $guest->guest_name;
            etc...  
        }
    }
} else {
    other stuff.. etc..
    $DBH = null;
} catch (PDOException $e) {
    echo $e->getMessage();
}
}

I've gotten simple select statements to work fine as this user (I don't think its permission-related), my problem seems to be with my implementation of PDO. How can I get rid of this error? Am I preparing/executing the statement in a weird way that's messing this up? Thanks for any help

Well it seems like you're certainly able to connect as well since the exception is occurring during the call to setFetchMode. Have you tried just a simple mysql_connect & mysql_connect just as a sanity check? (As a side note looks like they're discouraging those methods now, I'm getting to old for this, lol!) — quickshiftin
try with your root user. If you can run the queries with that one, then it is permissions. I had a similar issue, all seemed fine, but then some queries were not running right with the ddbb user. — Alejandro Moreno

Jorge Mariano Paz Flores Jorge Mariano Paz Flores · Accepted Answer · 2014-12-13T20:01:37

I had the same problem. After I uploaded my code from local to production server, and the user had all privileges. The problem was the database name -- in my local environment it was something like dattabase1 and in production the name was different...prefix_ddatabas1. I changed the name of the database to the correct one and everything was ok.

Syntax error or access violation using PDO

5 Answers