Failure to connect to the database in the local host

Question

<?php
$server="localhost";
$user="root";
$pass="";
$dbname="expert";

$dsn="mysql:host=$server;dbname=$dbname";
try {
    $connect=new PDO($dsn,$user,$pass);
    $connect->exec("SET character_set_connection ='utf8mb4'");
    $connect->exec("SET NAMES ='UTF8'");
}
catch (PDOException $error){
    echo ("unable to connect".$error->getMessage());

}
?>

unable to connectSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

Show us your code - it says there's an error in the SQL query syntax, but we can't see that. And put it in the question in code tags, not as a photograph. — droopsnoot, Feb 03 '23 at 13:10
[Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) — CBroe, Feb 03 '23 at 13:20
The `SET character_set_connection` should not be quoted. But I think `SET NAMES` would override that anyway, so it may not be needed. — aynber, Feb 03 '23 at 13:37
Seems like the connection is established but your char set is failing. Should change the error message/handling. See https://stackoverflow.com/questions/4361459/php-pdo-charset-set-names for setting the character encoding of the connection. — user3783243, Feb 03 '23 at 13:59
Is the = sign required in the SET NAMES command? Examples seem to not have it. — droopsnoot, Feb 03 '23 at 14:05
The error message suggests that there's a syntax error in your SQL code. To resolve it, you should check the SQL query being executed and make sure it follows the correct syntax. Additionally, you may want to verify that the version of MariaDB you are using is supported by the query. — Anas Fanani, Feb 07 '23 at 23:42

score 0 · Answer 1 · answered Feb 05 '23 at 07:36

NAMES is not a variable, SET NAMES is a SQL command.

According to the documentation of SET NAMES, the command requires a character set and optional a collation. Quotes are optional for the character set or collation clauses, e.g.

SET NAMES utf8mb4

However, you should avoid sending extra commands, since client can send the character set already during connection handshake.

This can be done by specifying the character set in your DSN:

$connect = new PDO("mysql:host=$server;dbname=test_db; charset=utf8mb4",$user,$pass);

score -1 · Answer 2 · answered Feb 03 '23 at 14:10

This Worked for me you can try this

   <?php

   $server="localhost";
   $user="root";
   $pass="";

   try {
      $connect=new PDO("mysql:host=$server;dbname=test_db",$user,$pass);
      $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $connect->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND , "SET NAMES utf8");
      $connect->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND , "SET character_set_connection =utf8mb4");
      echo "connected Successfully";

    }
    catch (PDOException $error){
    echo ("unable to connect".$error->getMessage());

    }
    ?>

Failure to connect to the database in the local host

2 Answers2