From the course: PHP: Exporting Data to Files

Querying the database with MySQL Improved - PHP Tutorial

From the course: PHP: Exporting Data to Files

Start my 1-month free trial

Querying the database with MySQL Improved

“

We're going to be using the same database queries repeatedly throughout this course, so it's more convenient to store them in external files. In this video, I'll explain the setup for MySQL. If you're using SQLite, or a different database, skip to the next video. In your editing program, create a folder called includes at the top level of the testing site. Next, we need to create a PHP file inside that folder. So a new PHP file, and call the file car underscore MySQLi.PHP. MySQLi stands for MySQL Improved API, which is now the recommended way for PHP to communicate with MySQL. It's different from the original MySQL functions which are now deprecated and will eventually be removed from PHP. If necessary remove any HTML code to speed add it by your editing program. All that you want is an opening PHP tag at the beginning of the page. We going to use the object oriented version of MySQL Improved. So we need to create a MySQLi object with the new operator. So on line 2 create a variable we'll call it db. Then the new operator, and MySQLi. And we'll add four arguments to this, all of them strings. The first one is the server. We'll be using localhost. Then the user account, the name is exporter. And the user account's password. My password is lynda. And then, the database that we want to connect to, which is php-export. To make sure we're connected, we can check the connect error property of the MySQLi object we've just created. So, in a conditional statement, if db then the arrow operator. Connect error. And inside the conditional statement, we want to save the value of connect error to a variable, which we'll call error. Assuming nothing has gone wrong, we can have an else clause where we can create the database query and submit it. So, let's have an else clause. Add inside there we'll save the SQL query to a variable called SQL. And to save time, there's a text file in the exercise files for this video, where you can copy all of that SQL query, and then paste it into our page. And we need a semicolon on, the end. And let's just tidy that up a little bit. Now we need to submit the query and store the result in a variable we'll call the result, result. We'll pass it SQL as our argument. I just in case something goes wrong with getting the result we need to check if there's an error. So, another conditional statement if then db Error, and if there is an error, then we'll save that also to the error variable. So if we fail to make a connection, error up here will get the connection error message, and if we get a problem with the SQL query, error will have the message here. So we can test our page, by having another conditional statement down here. If error has been set, we know there's a problem. So, is set, and if it has been set, we want to see what it is, so we'll echo it, echo error, but if everything is okay, we'll echo, okay. So, we can now save that page, and then test it in a browser. So, let's open a browser. So, its localhost, php export includes, and then cars, underscore MySQLI dot php. Everything is working fine. If you get an error message you need to find out what the problem is whether it's a connection to the database or with the SQL query. The SQL query should be okay because you're copying it from a text file. So we now know that, that is working. Let's go back to our editing program and we'll make a copy of this page. So Save As, and we'll call this flowers underscore MySQLi.php that needs to be saved in the includes folder. And all we will do with this is we'll change the value of SQL. Instead of this long query here, delete everything after select, then an asterisk, and then from arrangements. And then we can save that. And also test that let's go back to the browser and instead of cars underscore MySQLi we want flowers underscore MySQLi.php, run that and we also got okay. So both of those are working. So assuming that both of them work, go back to your editing program, and we can get rid of this from line 12 down to line 16, this test here. Just save that, and then open the other file. Get rid of that, line sixteen to twenty. And save the file. An alternative way to connect to a database with PHP is using PDO, which isn't vendor-specific. In other words, PDO supports not only MySQL, but many other widely used databases, and we'll look at that next.

Contents