Querying the database with PDO - PHP Tutorial

Now that's we've seen how to create a database source name, or DSN for PDO, let's create a couple of files to query to the PHP export database. We're going to be using the same database queries repeatedly throughout the course. So we'll create them as include files, and save them in a folder called Includes at the top level of the testing site. So we need a new PHP file, and save it as carls_pdo.php in the includes folder. If your editing program has inserted any HTML, you need to remove all that. All you need is the opening PHP tag at the top of the page. If you'd like to use PDO to connect to MySQL, create a DSN like this. So we'll call it MySQL. Then it needs to be a string, beginning with 'mysql' as the prefix, followed by a colon, then host and the name of the server, which will be localhost, followed by a semicolon and dbname, and the name of the database that I'm using is phpexport. Lets also create one for SQLite, we call that sqlite, and it begins with sqlite as prefix, followed by colon. And this needs to be followed by a fully qualified path to wherever you've saved phpexport.db. I've put it in a folder called squlite at the top level of my site. I'm working on Windows using Xampp, so the path will be C:xampp/htdocs/phpexport/sqlite/phpexport.db. Obviously the location will depend on where you've saved the file, and on your own operating system. When using PDO to connect to a database, you should always use a tri-catch structure. The tri-block runs the code, but if PDO throws an exception you catch it in the catch block. And this is particularly important with databases like My-SQL that are password protected, because an un-caught PDO exception can expose your user name and password. So let's create that try catch block, try and then we'll have catch and what we want to catch is a PDO exception, and, we'll call that e. And inside the catch block, we'll assign any message from that exception to a variable called error. And we use the getMessage method. Now all of the code that we need to execute goes in the try block. We'll create the connection, store it in a variable called DB, we need a new PDO object, and the first argument to the PDO object is the DSN, so if we're using MySQL, it will be MySQL. If you're using a database that needs a username and a password, the next argument is the username so that'll be exporter, and then the third argument is password, which I'm using Lynda. Now if you're using a database like SQLite that isn't password protected, you just need the DSN as the first argument. So let's create one for SQLite, so it'll be new PDO, and then it will just be that DSN, which is stored in SQLite. We'll use the SQLite connections and I'll just comment out that first connection there, and then we need to have the query, which we'll store in SQL, and to save time There's a copy of the query which is in cars dot txt in the exercise files for this video. I'm just going to paste that in there, and then put a semi colon at the end and then we need to submit that query and store the result. So we'll store the result in result, so db and then the arrow operator query, and our statement is stored in SQL. Everything goes fine, our result will be stored in the variable called result. If anything goes wrong with the query, we need to call the error info method on the PDO object. So we'll store that in a variable called error info ourselves. And the error info method of PDO object returns an array. If there's a problem with the query, the error message is in the third array element. If everything is okay the third element doesn't exist. So we can check for problems with a conditional statement. So if, then is set and we are looking for error info that's an array, and we want the third element so the index is two and if it is set we are going to assign that to our error variable. That's basically all the code complete but to be able to test this, we need to check if error is set. So we'll create another conditional statement down here if isset error if that exists we want to see what it says, so we want to echo it but if everything is okay we'll echo, okay. So if we just save that page, we can then run it in a browser. So, let's open a browser and tha address that we want is localhost/phpexport /Includes/cars_pdo.php. It tells us that it's okay, so that is working fine. If you get an error message it will probably be the connection, so check what the error message says and sort that out. Lets just quickly go back to our editing program and make a copy of cars pdo. Save that as flowers underscore pdo dot php, and we just need to change the SQL query instead of select car ID etcetera. Delete all of that and make it select asterisks. There we're selecting everything from arrangements, and then save that. Go back to the browser, change the URL instead of car_pdo. We need to check flowers. And yes that one is also OK. So as long as there are no problems, you can go back to flowerspdo.php and delete this test here from line 17 to 21. And then we need to do the same with carspdo. Test this time is on lines 21 to 25, just delete those contents, save the page. Now we've prove it today's price we are ready to display the results in a web page.