Connecting to a MySQL database with PDO
Pre-emptive apology: This post contains basic questions.However, I have searched and I have not found an answer, if there is one. sorry.
I am following some youtube tutorials for making a basic ajax web chat, and in the tutorial the person is using MySQLi to connect to the DB. I want to create the same ajax chat application except I want to use PDO instead of MySQLi.
The person uses these two files:
config.php
chat.class.php
I’m trying to replicate the above snippets with PDO. The problem is that I’m not sure how to adapt the PDO examples I have looked at to do this.
First of all I’m confused as to why he defined these things in a separate file.. are there any benefits in doing this?
In another PDO tutorial I am looking at I see it can be done the followings way:
I think this is what I need to use (in a try catch block), but why does he put these things in an array? it seems to over complicate things. why not just variables? But does this code replicate the mysqli example? Howcome I don’t see __construct() being used with PDO?
Some minor questions. When creating a website with a user, is there a standard place to store DB connection? Any book recommendations?
Sorry for all these questions, All help is strongly appreciated!
5 Answers 5
To answer your questions:
First of all I’m confused as to why he defined these things in a separate file
The authentication details are defined in a second file because if you create another query script, now both scripts can include the authentication details. If the authentication details change, you only need to update one file. If you are just writing a simple application, than just keep everything in one file.
but why does he put these things in an array
I think this is just done in-case the authentication details are needed someone else in the script (much like the defined globals from your first sample). Its often best practice to define parameters into variables (even if you use the variable once). This way, if you typo a variable, you will get an error; versus copy and pasting the same string over and over again.
Howcome I don’t see __construct() being used with PDO
When ever you create a new object in PHP, you do not need to call __construct, it is called automatically with the «new» statement.
When creating a website with a user, is there a standard place to store DB connection
Definitely make sure the authentication details are stored in an inaccessible file to the public. The connection object has no harm to be accessed by the public (unless of course you need to authenticate the client (website user) before establishing a database connection). Is is best practice to always begin your (secure) PHP files with:
BUT. never end the file with «?>». If an extra character is inserted after the «?>» on accident, your web server could display your whole script to the world (of course your Apache, etc. would have to be configured wrong). Like I said. best practices.
Googleing «php arcitechture best practices» may help.
You are confusing WAY TOO MUCH things that can be explained in one answer. you don’t even know what to ask.
some of your confusions are:
__construct() method actually has nothing to do with PDO. Nor with mysql. this is a Chat class method. And method which is all wrong. Chat class shouldn’t create its own connection but use already created one.
This thing on variables vs. array vs. constants doesn’t really matter. To have connection options in a separate file is a good thing but nonetheless you need to have a connection code in the separate file as well, to avoid writing connection code in the every file.
You should not use this code in a try catch block (unless you have an idea what to do in case of error, which I doubt you have).
Before starting for a chat, you have to learn smaller, simpler applications, like telephone book or the like, to learn basic database operations, from which you’ll be later able to build ANY application, like any house can be built of bricks.
PDO basics you can get right here, in the tag wiki. But OOP basics is not that easy.
PHP PDO and MySQLi [duplicate]
I just finished an introduction course in PHP, and throughout the stackoverflow forum people have recommended that I switch to PDO, prepared statements or MYSQLi, I briefly checked the manual but most of it went over my head.
I’ve been using mysql_* functions up till now so these concepts are new to me. I think they are used to access and perform database specific actions, but I’m not sure.
So what is the difference between PDO, prepared statements and MySQLi, are they different features that accomplishes the same task? Are they compatible in a script or is it «choose one or the other»? And lastly which offers the best performance?
Update: Thanks for the answers, I’ll be hunting for more PDO tutorials.
For reference I also found the following posts useful:
6 Answers 6
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.
PHP 7 RC3: How to install missing MySQL PDO
I am trying to setup webserver with PHP 7 RC3 + Nginx on Ubuntu 14.04 (for test purposes).
I installed Ubuntu in Vagrant using ubuntu/trusty64 and PHP 7 RC 3 from Ondřej Surý (https://launchpad.net/
I can not find the way to install MySQL PDO (PHP sees PDO class but not anything related to MySQL, like PDO::MYSQL_ATTR_DIRECT_QUERY etc.)
Looks like there is no lib php7.0-mysql (by analogy with standard php5-mysqlnd and php7.0-fpm etc. from Ondřej)
Section PDO in phpinfo() :
12 Answers 12
For thoses running Linux with apache2 you need to install php-mysql
or if you are running ubuntu 16.04 or higher just running the following command will be enought, no need to edit your php.ini file
If you are running ubuntu 15.10 or below:
Edit your php.ini file, it’s located at /etc/php/[version]/apache2/php.ini and search for pdo_mysql you might found something like this
Save the file and restart apache
Check that it’s available in your phpinfo()
First install php-mysql
then enable the module
and restart apache
First, check if your php.ini has the extension enabled «php_pdo_mysql» and «php_mysqli» and the path of «extension_dir» is correct. If you need one of above configuration, then, you must restart the php-fpm to apply the changes.
In my case (where i am using the Windows OS in the company, i really prefer OSX or Linux), i solved the problem putting this values in the php.ini:
If you are on windows, and your php folder is not in your PATH, you have set the absolute directory in your php.ini
Restart apache2.4 and it should work.
make install (as root)
This worked for me
On ubuntu 18.04 following works for me
type this in your terminal
I had, pretty much, the same problem. I was able to see that PDO was enabled but I had no available drivers (using PHP 7-RC4). I managed to resolve the issue by adding the php_pdo_mysql extension to those which were enabled.
I resolved my problem on ubunto 20.4 by reinstalling php-mysql.
Then install php-mysql:
It will add new configurations in php.ini
Had the same issue, resolved by actually enabling the extension in the php.ini with the right file name. It was listed as php_pdo_mysql.so but the module name in /lib/php/modules was called just pdo_mysql.so
So just remove the «php_» prefix from the php.ini file and then restart the httpd service and it worked like a charm.
Please note that I’m using Arch and thus path names and services may be different depending on your distrubution.
I’ll start with the answer then context NOTE this fix was logged above, I’m just re-stating it for anyone googling.
make install (as root)
enable extension=mysqli.so in your php.ini file
This is logged as an answer from here (please upvote it if it helped you too): https://stackoverflow.com/a/39277373/3912517
Context: I’m trying to add LimeSurvey to the standard WordPress Docker. The single point holding me back is «PHP PDO driver library» which is «None found»
Ubuntu 16 (Ubuntu 7.3.0)
Get instructions saying all I have to do is run this:
But then I get this:
By this stage, I’m still getting this on apt-get update:
I start trying to add in php libraries, got Unicode issues, tried to get around that and. you get the idea. whack-a-mole. I gave up and looked to see if I could compile it and I found the answer I started with.
You might be wondering why I wrote so much? So that anyone googling can find this solution (including me!).
PHP: PDO, работа с MySQL.
PDO – PHP Data Objects – это прослойка, которая предлагает универсальный способ работы с несколькими базами данных.
Смысл PDO в том, что отсутствует привязка к конкретной системе управления базами данных. PDO поддерживает СУБД: MySQL, PostgreSQL, SQLite, Oracle, Microsoft SQL Server и другие.
Почему стоит использовать PDO
Функции mysql в PHP для работы с БД давно уже устарели, на сегодняшний день желательно использовать mysqli или PDO (PHP Data Objects). Кроме того, mysqli – эта библиотека, которая по большому счёту, не предназначена для использования напрямую в коде.
При работе с mysqli следует также помнить об обеспечении безопасности вашего приложения, в частности о защите от SQL-инъекций. В случае использования PDO (с его подготовленными запросами), такая защита идёт уже “из коробки”, главное правильно применить необходимые методы.
Подключение
Способы подключения к разным СУБД могут незначительно отличаться. Рассмотрим примеры подключения к наиболее популярным из них:
В блок try/catch – всегда стоит оборачивать все PDO-операции и использовать механизм исключений.
Исключения и PDO
PDO умеет выбрасывать исключения при ошибках, поэтому все должно находиться в блоке try/catch. Сразу после создания подключения, PDO можно перевести в любой из трех режимов ошибок:
PDO::ERRMODE_SILENT
Это режим по умолчанию. Примерно то же самое, что используется для отлавливания ошибок в расширениях mysql и mysqli. Следующие два режима больше подходят для DRY программирования.
PDO::ERRMODE_WARNING
Этот режим вызовет стандартный Warning и позволит скрипту продолжить выполнение. Удобен при отладке.
PDO::ERRMODE_EXCEPTION
Он выбрасывает исключение, что позволяет обрабатывать ошибки и скрывать необходимую информацию.
PDO и MySQL
Рассмотрим пример подключения базы данных MYSQL
Подготовленные и прямые запросы
В PDO существует два способа выполнения запросов:
Прямые запросы
exec() используется для операторов INSERT, DELETE, UPDATE. Возвращает число обработанных запросом строк.
Прямые запросы используются только в том случае, если в запросе отсутствуют переменные и есть уверенность, что запрос безопасен и правильно экранирован.
Подготовленные запросы
Если же в запрос передаётся хотя бы одна переменная, то этот запрос в обязательном порядке должен выполняться только через подготовленные выражения.
Это обычный SQL запрос, в котором вместо переменной ставится специальный маркер – плейсхолдер. PDO поддерживает позиционные плейсхолдеры (?), для которых важен порядок передаваемых переменных, и именованные (:name), для которых порядок не важен. Примеры:
Чтобы выполнить такой запрос, сначала его надо подготовить с помощью метода prepare(). Она также возвращает PDO statement, но ещё без данных.
Чтобы их получить, надо исполнить этот запрос, предварительно передав в него наши переменные. Передать можно двумя способами: Чаще всего можно просто выполнить метод execute(), передав ему массив с переменными:
Далее мы рассмотрим методы PDO для получения данных.
Метод fetch()
Метод fetch() служит для последовательного получения строк из БД. Этот метод является аналогом функции mysq_fetch_array() и ей подобных, но действует по-другому: вместо множества функций здесь используется одна, но ее поведение задается переданным параметром. Пример:
Метод fetchColumn()
Также у PDO есть метод для получения значения единственной колонки. Очень удобно, если мы запрашиваем только одно поле – в этом случае значительно сокращается количество кода. Пример:
Метод fetchAll()
PDOStatement::fetchAll — Возвращает массив, содержащий все строки результирующего набора. Пример:
Insert, Update и delete
Вставка новых, обновление и удаление существующих данных являются одними из наиболее частых операций с БД. В случае с PDO этот процесс обычно состоит из двух шагов. Рассмотрим примеры:
Installing PDO driver on MySQL Linux server
I was suggested, not long ago, to change my code to use PDO in order to parameterize my queries and safely save HTML in the database.
Well, here are the main problems:
The site I’m building actually only requires PDO for one page. While I may consider re-writing it, it would take a while and I need the pages to be running soon, so I can’t turn off MySQL completely. If I do install PDO, will I still be able to use mysql_* handlers?
The server in question is running PHP Version 5.4.6-1ubuntu1 and Apache/2.2.22 (Ubuntu). I’m also running a phpMyAdmin database, if it matters.
6 Answers 6
On Ubuntu you should be able to install the necessary PDO parts from apt using sudo apt-get install php5-mysql
There is no limitation between using PDO and mysql_ simultaneously. You will however need to create two connections to your DB, one with mysql_ and one using PDO.
That’s a good question, but I think you just misunderstand what you read.
Install PDO
Compatibility with mysql_
Apart from the fact mysql_ is really discouraged, they are both independent. If you use PDO mysql_ is not implicated, and if you use mysql_ PDO is not required.
If you turn off PDO without changing any line in your code, you won’t have a problem. But since you started to connect and write queries with PDO, you have to keep it and give up mysql_.
Several years ago the MySQL team published a script to migrate to MySQLi. I don’t know if it can be customised, but it’s official.
Basically the answer from Jani Hartikainen is right! I upvoted his answer. What was missing on my system (based on Ubuntu 15.04) was to enable PDO Extension in my php.ini
To find where your current active php.ini file is located you can use phpinfo() or some other hints from here: https://www.ostraining.com/blog/coding/phpini-file/
At first install necessary PDO parts by running the command
where * is a version name of php like 5.6, 7.0, 7.1, 7.2
After installation you need to mention these two statements
The purpose of using this is to implement an additional layer of security between the user interface and the database. By using this layer, data can be normalized before being inserted into your data structure. (Capitals are Capitals, no leading or trailing spaces, all dates at properly formed.)
But there are a few nuances to this which you might not be aware of.
First of all, up until now, you’ve probably written all your queries in something similar to the URL, and you pass the parameters using the URL itself. Using the PDO, all of this is done under the user interface level. User interface hands off the ball to the PDO which carries it down field and plants it into the database for a 7-point TOUCHDOWN.. he gets seven points, because he got it there and did so much more securely than passing information through the URL.
You can also harden your site to SQL injection by using a data-layer. By using this intermediary layer that is the ONLY ‘player’ who talks to the database itself, I’m sure you can see how this could be much more secure. Interface to datalayer to database, datalayer to database to datalayer to interface.
By implementing best practices while writing your code you will be much happier with the outcome.
Re: MySQL Functions in the url php dot net/manual/en/ref dot pdo-mysql dot php
Re: Object Oriented Design using UML If you really want to learn more about this, this is the best book on the market, Grady Booch was the father of UML http://dl.acm.org/citation.cfm?id=291167&CFID=241218549&CFTOKEN=82813028
Or check with bitmonkey. There’s a group there I’m sure you could learn a lot with.