Asked  7 Months ago    Answers:  5   Viewed   29 times

I had this previously in my normal mysql_* connection:

mysql_set_charset("utf8",$link);
mysql_query("SET NAMES 'UTF8'");

Do I need it for the PDO? And where should I have it?

$connect = new PDO("mysql:host=$host;dbname=$db", $user, $pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

 Answers

85

You'll have it in your connection string like:

"mysql:host=$host;dbname=$db;charset=utf8"

HOWEVER, prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:

$dbh = new PDO("mysql:$connstr",  $user, $password);
$dbh->exec("set names utf8");
Tuesday, June 1, 2021
 
JustSteveKing
answered 7 Months ago
72

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.


* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Thursday, June 10, 2021
 
Zulakis
answered 6 Months ago
55

Question was already edited, but for people who are just heading straight over to the answers, here it is..

You should be using correct namespaces for the objects in your methods, either "use" them or prefix them with the root namespace;

<?php
//... namespace etc...

use PDO;

self::$connection = new PDO("mysql:host=$host;dbname=$base", $user, $pass);

or simply;

self::$connection = new PDO("mysql:host=$host;dbname=$base", $user, $pass);
Wednesday, August 11, 2021
 
QuantumMechanic
answered 4 Months ago
86

Setting it in DSN is the only proper way (although it is only supported since 5.3).
You can this one and SET NAMES at the same time.

All the other ways will make infamous half-fictional GBK injection possible.

Please note that your setting for error_reporting() is utterly wrong. it have to be unconditional -1. If you concerned about displaying errors - there is a proper ini setting for this, called display_errors, can be set at runtime.
While error_reporting sets level of the error and should be at max all the time.

Wednesday, August 11, 2021
 
dkcwd
answered 4 Months ago
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share