Getting started with PDO

[dnnhater]

I'm not sure if this goes here or in the db category...

I've been working with PHP/MySQL for about 10 years now and have had great success with the small-ish projects that I've worked on - no full-blown apps but simple user-level stuff and small backends (I'm proud of it anyway lol)

I've now been faced with developing a new site at work and they're actually going to allow me to do it with PHP as opposed to .net which is so much easier for me...the catch? the database is MS SQL - no biggie

However, I've been searching high and low for sqlsrv tutorials and almost everything leads back to topics around PDO - a term with which I'm not unfamiliar, but I cannot find a simple tutorial to get me started - just help me to form a simple select statement even

Ultimately I'm hoping that someone here has places other than php.net where I can go to learn more about this - the overall project is super small but knowing how we work around here, if it's successful I see more of these types of projects coming up

Any thoughts and help are greatly appreciated

[tangoforce]

It must be said, I've found the explanation about PDO and MySQLI as clear as mud.

Fou has a link in his signature though that you might find useful, take a look at his profile:
http://www.codingforums.com/member.php?u=2783

I still didn't really quite see the point when reading that php.net page but I only skim read it lol

[Fou-Lu]

Ultimately: native specific things should use the native library (MySQLi or SQLSrv for examples), and more generic stuffs could use PDO. PDO will be slightly slower since it has to go through the abstract layer to be interpreted, but the cost is pretty nominal.
PDO's primary advantage is its cross dbms driver interpretations, but it means you need to stay away from proprietary stuff *if* you intend to allow different types of db's that can be interchanged (so no LIMIT or TOP calls). If there is no immediate desire to support multiple dbms', than PDO is a great option. You can also get the driver name from the PDO, so if at a later time you allow a conversion and discover that you have a TOP call, you can easily use an if check to determine if you need to convert it to MySQL, or Oracle or whatever.

PDO is OO only, and supports no procedural methods. You can of course write your own if you so desire. MySQLi supports both OO and procedural, and SQLSrv support procedural only, so these may factor into your choice of which library to use.

Personally? Ignoring that I have my own abstraction library, if I had to choose between PDO or SQLSrv, I'd probably select the PDO.

[dnnhater]

Originally Posted by tangoforce

Fou has a link in his signature though that you might find useful, take a look at his profile:
http://www.codingforums.com/member.php?u=2783

surprisingly - this was the most helpful link and I actually now have a working select statement

thanks for pointing it out!

[dnnhater]

Originally Posted by Fou-Lu

If there is no immediate desire to support multiple dbms', than PDO is a great option.

I don't think I'll have to support multiple dbms', but with the deprecation of MySQL and our IT dept not willing to support anything other than MS SQL, I thought I would just make the jump to PDO to give it a run and see how it works out

Stupid question: I'm not sure what you mean by "(so no LIMIT or TOP calls)" - like "SELECT * FROM table LIMIT 0,30"?

[Fou-Lu]

That's correct. LIMIT is a proprietary command. SQL Server uses top: SELECT TOP 30 * FROM table. TOP is also a proprietary command. Oracle uses ROWNUM with a WHERE.
Anything non-standard SQL wise should be avoided if you have an intent to support multiple dbms. If not, than there is no concern to using proprietary functionality.

[dnnhater]

thank you for pointing that out - I never would have thought of that and would have been burning up the boards with those errors - then yes my previous statement of not running cross-db is accurate

now the next trick is getting this stuff to work in my classes

[Dormilich]

to add to the list, I’m a big fan of PDO’s (automatic) Exception handling of which I’m not aware in any other set of DB functions.

[Fou-Lu]

Originally Posted by Dormilich

to add to the list, I’m a big fan of PDO’s (automatic) Exception handling of which I’m not aware in any other set of DB functions.

I clearly don't use enough PDO :P
What do you mean when you say automatic exception handling? Can you show me an example of that?

[Dormilich]

example (quite condensed):

PHP Code:

try
{
    $opt = array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION;
        PDO::ATTR_EMULATE_PREPARES => false)
    );
    $pdo = new PDO($dsn, $user, $pass, $opt);

    $sql = "SELECT FROM bar WHERE foo = ?"; // of course there’s an SQL error
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(1, $_GET["foo"], PDO::PARAM_STR);
    $stmt->execute();
    echo "success";
}
catch (PDOException $p)
{
    error_log($p->getMessage());
    echo "error";
} 


[Fou-Lu]

Oh I see, I thought you meant something different when you said automatic exception handling. Yes, PDO is the only one that throws exceptions AFAIK as well.
I personally like dealing with exceptions over result comparisons as well, but yeah it doesn't make sense for exceptions to be used in procedural code. Since Mysqli is both procedural and OO, it makes sense that they never implemented the exception handling on the mysqli in general, and the same goes for sqlsrv since its procedural only.

[Dormilich]

Originally Posted by Fou-Lu

I personally like dealing with exceptions over result comparisons as well, but yeah it doesn't make sense for exceptions to be used in procedural code.

it doesn’t make sense to use Exceptions in procedural code? admittedly, the function stack is rather shallow, but nevertheless ...

[Fou-Lu]

Exceptions themselves are object oriented. If you use them, you are no longer programming just procedural code.
Procedural would be better off triggering an error and capturing the backtrace. As you mentioned, procedural code typically isn't that deep, but object oriented has a lot of depth to it, so unlike the procedural code you can't be sure where the error occurred in the stack until you evaluate the trace. So exceptions are one of the most useful controls in OO, but mostly useless in procedural.

Since procedural code is typically in full control of what it is doing and intends to do, throwing exceptions is a bit counter-intuitive, almost along the line of "I threw an exception and then I caught it". Exceptions can be seen more along the lines of: "oops, something didn't go quite right. But I don't know what I'm supposed to do when it doesn't go right, so I'm just going to send this back up the stack", and the controlling application then decides what to do with it.

Nothing says you cannot hybrid them and throw exceptions in procedural code, it just seems bizarre IMO. Its far easier working with the returned results and trapping only the critical errors.

[Dormilich]

it seems like I was never solely sticking to one programming paradigm in my code ever ...

[Fou-Lu]

Lols, in PHP unless you are *only* using procedural, than you are using a hybrid. OO is an over the top feature of PHP, so even a pure OO script needs to be invoked procedurally in PHP. Even a single command of new MyRunningApplication(); is still procedural.

Personally I view procedural as *only* procedural methods. If I ever, even once, make use of the new keyword or invoked statically, than I consider the program to be object oriented even if I have no custom code that represents an object. So I'd suggest you've been programming OO.
Your view may differ than mine, and you may see procedural as only applying to the code you've written instead of factoring the external stuff like PDO. So using things like try/catch with PDO in an overall procedural programming IMO is OO, but may be seen by others as procedural.