Understanding how PDO / rendered reports work with multiple types of databases
I just recently started working with PDO
and Prepared Statements
and understand how they work fine, however now I want to support multiple databases and ask a few questions about how they work.
As I understand it, if you are using PDO in PHP, it will support many different types of databases; however I want to understand how it works.
For example, if I do this:
$data = array( 'name' => 'Cathy', 'addr' => '123 fake st', 'city' => 'fakesville' );
$sql = $db->("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");
$sql->execute($data);
I understand that will work on whatever database you want as long as it's maintained?
If so, how does it work? How does it translate the SQL statement into the appropriate syntax for each database engine?
If the above value is true, is there something I should know about this that is not supported? I think I read that you shouldn't be using database specific functions?
If so, how do you do it if you need to? Do you need to write SQL for every database you want to support with their respective functions?
Commonly used MySQL would be COUNT
and SUM
in MySQL; which would be the database language I would mostly write in.
source to share
PDO is really just a database connection driver - which means it is a function that will support passing a SQL statement, preparing a statement, parameter bindings, returning a recordset, etc. to different types of databases - instead of having one driver for each database provider.
It won't change your SQL in any way, and if you have a statement that is only supported by one database type or function in your query supported by one database engine and not another - the query will fail if passed to the wrong database ...
The vast majority of queries will work on almost an entire SQL database, since the language has a standard written for it and most databases follow that standard - however, as your SQL is more familiar with a particular database engine, you may find it becomes less portable. for other engines.
If you are writing a query for a specific database, you can use all the tricks the db engine has to offer, but if you want your code to function in any of the PDO supported databases, you might have to write a few more "vanilla" even if they are slightly longer.
Edit: The best way to ensure that your code is supported across multiple databases is to simply write "simple" or "simple" queries. When you are familiar with a specific database, you usually know what tricks each has sleeves and you know that trick is only in that database. For example, mySQL has a function group_concat()
that will not work in any other database, oracle has many functions that only work in an Oracle database.
However, for the most part, a query written with a normal selection, almost any aggregate functions, regular (left, right, inner) joins work fine on every engine. You can also use one of the many websites (e.g. http://sqlfiddle.com/ ) to check which machines the request will run on, or check http://php.net/manual/en/book.pdo. php
source to share