PDO to connect to web sql from localhost?

问题:

I am trying to do some testing, i am using MAMP and trying to connect to my SQL database on my server. I haven't used PDO before and am having a hard time tracking down what error codes mean what. I am getting this:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'hostname' (60)

My connection function:

  function getConnection() {
    $dbhost="hostname";
    $dbuser="user";
    $dbpass="password";
    $dbname="somedb";
    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbh;
  }

where i am trying to perform the query:

  function getTypes() {
    $sql = "SELECT id, name FROM type";
    try {
      $db = getConnection();
      $stmt = $db->query($sql);
      $types = $stmt->fetchAll(PDO::FETCH_OBJ);
      $db = null;
      echo '{"wine": ' . json_encode($types) . '}';
    } catch(PDOException $e) {
      echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
    echo 'getTypes';
  }

Edit: I can connect using the same credentials with mysqli.

回答1:

It's a security problem. To avoid your database beeing flooded or filled with garbage a configuration setting says "only local ip can access to the database". All shared hosting do that and it is a good practice to do it if you have a dedicated server.

in /var/mysql/my.conf on linux you have got something like that :

skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

回答2:

Your mysql server is either listening to local unix socket or bind to localhost (127.0.0.1). Make sure mysql server is listening on the external IP address of hostname.

Note: The connecting user (user here) must have access (GRANT) from your clients IP on the database (somedb);

回答3:

Are you connecting to the right port?

$dbh = new PDO("mysql:host=$dbhost;port=PORT;dbname=$dbname", $dbuser, $dbpass);

回答4:

If your server that is running the PHP scripts is the server running the SQL Server, you need to not use hostname a connection, you need to refer to the local system. What you have in your config, hostname does not resolve to an IP of a valid SQLServer, where if you reference localhost, it will reference the running server itself, and connect how it needs to. You can use either of the following:

localhost || 127.0.0.1