Simple MySQL Tutorial - Printable Version +- Support Forums (https://www.supportforums.net) +-- Forum: Categories (https://www.supportforums.net/forumdisplay.php?fid=87) +--- Forum: Coding Support Forums (https://www.supportforums.net/forumdisplay.php?fid=18) +---- Forum: PHP The Hypertext Preprocessor (https://www.supportforums.net/forumdisplay.php?fid=21) +---- Thread: Simple MySQL Tutorial (/showthread.php?tid=18695) |
Simple MySQL Tutorial - Sly - 05-12-2011 Posted this on HF earlier. (http://www.hackforums.net/showthread.php?tid=1285020) ----------------------------------------------------------------------- Any code that contains [ ]s denotes an optional field. This tutorial assumes that you already have experience with PHP. If you are unsure that you will understand this tutorial, then you probably won't. Visit http://php.net to become familiar with PHP. You can also visit http://w3schools.com to learn PHP step-by-step, and view realtime code that you can edit and play around with. Connecting to your database Some people like to use the following for their code to connect to their database. PHP Code: <?php I like to make my database connection static, because I'm not going to change the username or password a lot, and can easily edit it to the right password without the use of a variable taking up space. Example: PHP Code: <?php Selecting Database Once you're connected to your database, you have to select your database. I had to help someone the other day with a null fetch_array return (covered later in this tutorial), and the problem ended up being that he wasn't selecting his database. You have to select your database before you can retrieve any results, or send any queries that need to access the database. You do this by the following: PHP Code: <?php So, now your code should look something like this: PHP Code: <?php Queries The next step is to make your query for the database. There are a few different queries to cover. The first query we're going to cover is the SELECT query. This is the query you want to use when you are trying to grab information from your database. For this example, our database has a table called "users", with the columns being "id", "username", "password", "email". If you want to grab only certain columns from your table, you can use the following: PHP Code: <?php Now, let's break this code down.
For our example, we're going to use the SELECT command, but we want all information to be returned, from all columns. You would do this with the following: PHP Code: <?php Now, let's break this code down.
So, at this point.. your code should look like this: PHP Code: <?php So, now say that you want to read all of the users that were returned by the database. Let's say we have 3 users. The following is an example of what our database information would be. Quote:ID, Username, Password, E-Mail Now, this data will be returned and assigned to the $result variable, as we used $result = (our query). A very popular mistake at this point, is people try to jump directly into mysql_fetch_array(). Example: PHP Code: <?php Now, even though this code IS correct, it only grabs 1 row of the entire result. That means you'll only get the first user from the database. The best way to grab the multirow results from $result is the following: PHP Code: <?php When you use this while(){} loop, $data is assigned to the row that is fetched from $result. You could use mysql_fetch_row($result, 1), mysql_fetch_row($result, 2), etc., but who wants to go through all of that when you can get so simple? All together, so far, your code should look like this: PHP Code: <?php The output of this code should be: Quote:ID: 1, Username: Test, Password: lol123, E-Mail: test@domain.com Now, that is the VERY basics of MySQL. Some of the other queries that you can use with mysql_query() are UPDATE and INSERT. These look like the following: PHP Code: <?php We can break this code down like this:
The following will insert a completely new user into our database. PHP Code: <?php Simply put:
So, by the end of these processes, our database the we began with: Quote:ID, Username, Password, E-Mail Now looks like: Quote:ID, Username, Password, E-Mail Closing your MySQL connection Once you're done executing your code, you can close the MySQL connection to save bandwidth. PHP Code: <?php I don't personally use this, because my database logs would be huge with the opening and closing of the link after every execution. If you want to, that's your choice. It's not required when using the database. Getting information from the database with an HTML form Let's say we have a form, and we want to use the information from that form to grab information from the database. For this example, I will use a simple login form. PHP Code: <?php Now, we definitely want to break this code down. (We will only be working with the PHP code, not HTML code.) I'm also assuming that you do know basic PHP, so I'm not going through the PHP functions.
The rest of the code should be self explanitory, as we have already covered it above. The output of this code, using the account "Test5" (refer back to our last database output), and assuming you use the correct password, should be: Quote:You have logged in. Your email address is: test5@domain.com End of Tutorial This is the end of this tutorial. For more on MySQL, please refer to the PHP functions manual, provided by http://php.net. Hopefully this tutorial will help some of you guys out that are still learning PHP. Note: If anyone has anything to add to what's already here, post it in this thread. RE: Simple MySQL Tutorial - Gaijin - 05-12-2011 This is the best first post any user had on SF. The tutorial is simple to follow and understand, you cover the basics and you cover them good, great work mate. +rep ya later, can't do it now. Keep up the quality. btw: Welcome to the Forums. RE: Simple MySQL Tutorial - KoBE - 05-12-2011 Nice tutorial. Keep up the HQ posts! RE: Simple MySQL Tutorial - begginer - 05-12-2011 Thanks for tutorial buddy! RE: Simple MySQL Tutorial - Sly - 05-14-2011 Thanks for the feedback, and the welcoming. Posted a couple more tutorials. One of which I couldn't get on HF, due to it's 15,000 character limitation on posts. |