PDO vs MYSQLi vs MYSQL

[birbal]

till now i was using mysql with php. somewhere i was reading about prepared statements which is more better as view of performance. though i did not use mysqli. and in other hand i saw some code in book where they were using PDO. dont have idea with it also. So someone please clarify the diffrence beetwin these. if want to move on from mysql which should i learn.

[boen_robot]

There's a comparrison table in MySQLi's overview. MySQLi is the most powerful extension for dealing with MySQL, but PDO allows you to interact with any PDO aware database (MySQL included) by just replacing the so called "database driver" (basically, altering the initialization a little), and it still has pretty much all features you'll need.

[birbal]

There's a comparrison table in MySQLi's overview. MySQLi is the most powerful extension for dealing with MySQL, but PDO allows you to interact with any PDO aware database (MySQL included) by just replacing the so called "database driver" (basically, altering the initialization a little), and it still has pretty much all features you'll need.

but 1) what is the benifit of prepared statement?2) what is client side prepared statment?3) in mysqli or PDO is the all data escaped?

[boen_robot]

1. A prepared statement is an SQL query in which the query itself, and the external data (parameters) are defined separately. In addition, the query is compiled once, so a reissuing of the query only swaps the parameters with the newly supplied data and reexecutes the query - no recompilcation of the query needed. That's the kind of scenario in which prepared statements make a performance difference. If you call one or several different SQL queries per PHP file, you probably won't see any performance difference between prepared statements and plain SQL quieries.2. A prepared statement that's compiled on the client, and is only sent to the server in a compiled form, ready for execution. Using a client side prepared statement decreases the load on the SQL server, for the price of a slightly larger load on the SQL client. If the SQL server and SQL client are on the same machine, you won't see any difference.3. If you're using prepared statements of any kind in either of those extensions - no. The external data is defined separately, so there's no need for escaping. As soon as you put the data as a parameter, the extension will know it's a parameter, and not part of the query to be compiled. Escaping is needed only when the data is embedded within the SQL query itself.

[birbal]

1)

In addition, the query is compiled once

do you mean server side compilation something like stored procedure3) ok it is clear now.

[boen_robot]

Yeah, something like that... except it persist only for the duration of the connection.Normally, every time you do a plain query, the query is send as is to the MySQL server where it's compiled into actual instructions for the MySQL server to do in order to create a result set. When you have a prepared statement, the MySQL server prepares (i.e. compiles) the query, and keeps the generated instructions available for reuse later during the same connection. It makes no difference if you're going to be executing the instructions once, but if you want to execute the same thing twice or more times, you're saving the time that would otherwise be needed for the query compilation.

[birbal]

It makes no difference if you're going to be executing the instructions once

is that mean that if i use a query with two diffrent external parameter. then two result set will be avalibale for later use?and what about client side prepared statement? how does it work? as i guess it will also store the result set for later use. but how?trying to match up the working proccess of server side and client side prepared statement.

[boen_robot]

The result set has nothing to do with this.When you write for example "SELECT * FROM users WHERE username='something'", the SQL server takes this text, and turns it into a set of computer instructions. After that, it executes those instructions.With prepared statements, the server keeps the generated instructions. The client can then just ask the server to again execute those instructions, instead of giving the server new text (and therefore waste the server's time with another compilation).Among the generated instructions, there are places where they are adjustable upon being called - the so called parameters. If you write for example "SELECT * FROM users WHERE username=?", the questionmark is assumed to be such a replaceable part. When reexecuting the generated instructions, you can provide new values for those replaceable places. Again, the server won't waste time trying to turn text into instructions. It will just execute the instructions, but with the newly supplied values in mind.Client side prepared statements work the same way, except the MySQL client is the one that does the text-to-instructions compilation.

[birbal]

all right. it is now rendering clearly.what is mysql client by the way. i knew about only the mysql server.

[boen_robot]

PHP is one MySQL client. "MySQL Workbench" is another client. Any program that connects to a MySQL server is a MySQL client.(phpMyAdmin isn't a MySQL client... it's a user interface for manipulating MySQL databases, written in PHP, but it's still PHP that's really the client)

[birbal]

ohh..so php is mysql client who helps to connect with mysql server. but php itself runs on server side. so when it will compile the instruction it will also go in server (client side prepare statement). is not it?

[boen_robot]

The "server" in "php itself runs on server side" reffers to your point of view as the user of a browser (the "client" in the same view)... the client in "php is mysql client" reffers to your point of view as a PHP developer.I knew I'd one day reuse this diagram for the purpose of the PHP-MySQL relationship. It was originally made to compare PHP to JSP (i.e. it shows it's pretty much the same deal).

[birbal]

great explanation and nice diagram. i think it is the basic stuff which was not clear to me. .my bad. .thats the disadvantge of self learning lol.... now all are crystal clear. and it was a great start up briefing for PDO mysqli. may be some query arise after i go into these later.i am realy thankful to you. :)thanks vasil!