Syntax error or access violation using PDO

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

---

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.

---

Sounds like an access issue on the server:

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

Have you tested from the CLI (or some other sql client outside of PHP) to ensure your script will have access?

You will likely need to log into the db to give access to a user your script is running as or check the credentials you're using in the script are accurate for the db in the server environment.

---

I had same issue, code working at dev server but not at prod server, and other scripts working with same table were working. I had missed to correct database table prefixes between dev and prod environment. (the database's names were not the same) ^^

链接地址: http://www.djcxy.com/p/69566.html

上一篇: 查询失败：在php网页下拉菜单

下一篇: 使用PDO的语法错误或访问冲突