Can't connect to MySQL with PDO

Question

I'm following this tutorial, I'm currently around minute 04:00 and I want to make a connection with my MySQL database through PDO. But my webpage will always give "Could not connect." when I'm trying to make the connection. When I used PHPStorms inside Database program, I had to change my serverTimezone to Europe/Amsterdam and then I was able to connect to my db.

I tried to add the port number in the 'new PDO()' code. I tried to change the timezone in the code and on my MySQL server but it gives this error;

mysql> SET GLOBAL time_zone = 'Europe/Amsterdam';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Amsterdam'

<?php

try {
    $pdo = new PDO('mysql:host=localhost:dbname=mytodo', 'root', '');
} catch (PDOException $e) {
    die('Could not connect.');
}

$statement = $pdo->prepare('select * from todos');

$statement->execute();

var_dump($statement->fetchAll());

require 'index.view.php';

Extra information:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

`mysql> SET GLOBAL time_zone = 'Europe/Amsterdam';` is not PHP, nor PDO. Please clarify the question. Is it a SQL query or PDO connection? — user3783243, Oct 26 '19 at 04:43
Okay, so I used the code of 'your common sense', I restarted my mysql services and then got this error: Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client You can fix that with: ALTER USER 'YOUR_USER_NAME'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ENTER_YOUR_PASSWORD_HERE'; — , Oct 26 '19 at 13:52
`ALTER USER 'YOUR_USER_NAME'` is not in this question.. also what are you attempting to do with that statement? — user3783243, Oct 26 '19 at 14:33

Your Common Sense · Accepted Answer · 2023-02-02T14:42:42.147

3

The tutorial you are following is very outdated.

A code connecting to PDO has to follow certain rules explained in my article How to connect to MySQL with PDO. In brief

it should configure error reporting mode
it should set the connection charset, the right way
it shouldn't catch it's own errors to report them
it should follow correct DSN syntax, without any extra decorations
it also coud set a couple useful settings

So the code should be like this

$host = '127.0.0.1';
$db   = 'mytodo';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$pdo = new \PDO($dsn, $user, $pass, $options);

Here you can see the proper connection code that does a lot of things. Among them, it tells you what the actual problem is when your code cannot connect to database.

edited Feb 02 '23 at 14:42

answered Oct 26 '19 at 07:22

Your Common Sense

156,878
40
214
345

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client – Oct 26 '19 at 13:46
Yes! Now you know the problem. And the solution for this particular problem is easily Google-able, just like I said: this is a [well known problem](https://stackoverflow.com/questions/52364415/php-with-mysql-8-0-error-the-server-requested-authentication-method-unknown-to). Just remember - the problem could be anything and the key is to get the actual error message. The rest is simple – Your Common Sense Oct 26 '19 at 13:50
Okay, so I used the code of 'your common sense', I restarted my mysql services and then got this error: Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client. You can fix that with: ALTER USER 'YOUR_USER_NAME'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ENTER_YOUR_PASSWORD_HERE'; – Oct 27 '19 at 14:04

score 0 · Answer 2 · edited Dec 18 '19 at 17:05

0

You have error in code near localhost

Use semicolon in place of colon

Write this instead:

$pdo=new PDO('mysql:host=localhost;dbname=mytodo','root','');

edited Dec 18 '19 at 17:05

Dharman

30,962
25
85
135

answered Oct 26 '19 at 11:13

Anurag sharma

21
1

Can't connect to MySQL with PDO

2 Answers2

Linked

Related