|
#1
|
|||
|
|||
PDO Tutorial for MySQL Developers
Why use PDO?
mysql_* functions are getting old. For a long time now mysql_* has been at odds with other common SQL database programming interfaces. It doesn't support modern SQL database concepts such as prepared statements, stored procs, transactions etc... and it's method for escaping parameters with mysql_real_escape_string and concatenating into SQL strings is error prone and old fashioned. The other issue with mysql_* is that it has had a lack of attention lately from developers, it is not being maintained... which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. Also lately the PHP community have seen fit to start a soft deprecation of mysql_* which means you will start seeing a slow process of eventually removing mysql_* functions altogether from the language (Don't worry this will probably be awhile before it actually happens!). PDO has a much nicer interface, you will end up being more productive, and write safer and cleaner code. PDO also has different drivers for different SQL database vendors which will allow you to easily use other vendors without having to relearn a different interface. (though you will have to learn slightly different SQL probably). Instead of concatenating escaped strings into SQL, in PDO you bind parameters which is an easier and cleaner way of securing queries. Binding parameters also allow for a performance increase when calling the same SQL query many times with slightly different parameters. PDO also has multiple methods of error handling. The biggest issue I have seen with mysql_* code is that it lacks consistent handling, or no handling at all! With PDO in exception mode, you can get consistent error handling which will end up saving you loads of time tracking down issues. PDO is enabled by default in PHP installations now, however you need two extensions to be able to use PDO: PDO, and a driver for the database you want to use like pdo_mysql. Installing the MySQL driver is as simple as installing the php-mysql package in most distributions. Connecting to MySQL old way: PHP Code:
A DSN is basically a string of options that tell PDO which driver to use, and the connection details... You can look up all the options here PDO MYSQL DSN. PHP Code:
You can also pass in several driver options as an array to the fourth parameters. I recommend passing the parameter which puts PDO into exception mode, which I will explain in the next section. The other parameter is to turn off prepare emulation which is enabled in MySQL driver by default, but really should be turned off to use PDO safely and is really only usable if you are using an old version of MySQL. PHP Code:
PHP Code:
Error Handling Consider your typical mysql_* error handling: PHP Code:
PDO has three error handling modes.
PHP Code:
PHP Code:
Running Simple Select Statements Consider the mysql_* code: PHP Code:
PHP Code:
PHP Code:
PHP Code:
Note the use of PDO::FETCH_ASSOC in the fetch() and fetchAll() code above. This tells PDO to return the rows as an associative array with the field names as keys. Other fetch modes like PDO::FETCH_NUM returns the row as a numerical array. The default is to fetch with PDO::FETCH_BOTH which duplicates the data with both numerical and associative keys. It's recommended you specify one or the other so you don't have arrays that are double the size! PDO can also fetch objects with PDO::FETCH_OBJ, and can take existing classes with PDO::FETCH_CLASS. It can also bind into specific variables with PDO::FETCH_BOUND and using bindColumn method. There are even more choices! Read about them all here: PDOStatement Fetch documentation. Getting Row Count Instead of using mysql_num_rows to get the number of returned rows you can get a PDOStatement and do rowCount(); PHP Code:
This is because MySQL's protocol is one of the very few that give this information to the client for SELECT statements. Most other database vendors don't bother divulging this information to the client as it would incur more overhead in their implementations. Getting the Last Insert Id Previously in mysql_* you did something like this. PHP Code:
PHP Code:
Consider the mysql_* code. PHP Code:
PHP Code:
Running Statements With Parameters So far we've only shown simple statements that don't take in any variables. These are simple statements and PDO has the shortcut methods query for SELECT statements and exec for INSERT, UPDATE, DELETE statements. For statements that take in variable parameters, you should use bound parameter methods to execute your queries safely. Consider the following mysql_* code. PHP Code:
PHP Code:
NOTE: when you bind parameters, do NOT put quotes around the placeholders. It will cause strange SQL syntax errors, and quotes aren't needed as the type of the parameters are sent during execute so they are not needed to be known at the time of prepare. There's a few other ways you can bind parameters as well. Instead of passing them as an array, which binds each parameter as a String type, you can use bindValue and specify the type for each parameter: PHP Code:
Now if you have lots of parameters to bind, doesn't all those '?' characters make you dizzy and are hard to count? Well, in PDO you can use named placeholders instead of the '?': PHP Code:
PHP Code:
Prepared Statements for INSERT, UPDATE, and DELETE are not different than SELECT. But lets do some examples anyway: PHP Code:
PHP Code:
PHP Code:
You may ask how do you use SQL functions with prepared statements. I've seen people try to bind functions into placeholders like so: PHP Code:
PHP Code:
PHP Code:
PHP Code:
PHP Code:
Executing prepared statements in a loop Prepared statements excel in being called multiple times in a row with different values. Because the sql statement gets compiled first, it can be called multiple times in a row with different arguments, and you'll get a big speed increase vs calling mysql_query over and over again! Typically this is done by binding parameters with bindParam. bindParam is much like bindValue except instead of binding the value of a variable, it binds the variable itself, so that if the variable changes, it will be read at the time of execute. PHP Code:
Here's an example of using transactions in PDO: (note that calling beginTransaction() turns off auto commit automatically): PHP Code:
|
The Following 4 Users Say Thank You to BamBam0077 For This Useful Post: | ||
danii (1st February 2017),
Demon-Cod3rs (5th May 2015),
Protheush (2nd June 2015),
UFFENO1 (5th May 2015)
|
#2
|
|||
|
|||
PDO Tutorial for MySQL Developers
Is there any chance that you can give us the map editor so we can play about with the levels and add new things to it?
|
#3
|
||||
|
||||
Quote:
Even if he knows or not, he's trying to help some people in here.
__________________
|
The Following User Says Thank You to Chez For This Useful Post: | ||
z3ro (22nd March 2019)
|
#4
|
|||
|
|||
yes i do even you do not
|
Tags |
developers , mysql , pdo , tutorial |
|
|