Mini course in PHP (Part II 11) - Mysql

(8 votes, average: 5.00 out of 5)

Loading ...

Hello everybody, it takes but the post does not fail! Hehe

Well, in this post, I will give an introduction to MySQL. I will only explain how to use it in an application using PHP and PhpMyAdmin (more here and here ).

As you may have followed the last post, we are seeing some concepts before starting to make our news system. In the previous post, we looked INCLUDES. If you have not read, please Click Here .

Let's get to it: Mysql is a manager database . A database is, roughly speaking, storage and organizing information. This information is stored in tables and organized by fields. A database needs a manager ( DBMS ), it is precisely here that comes into Mysql (as I said at the beginning of this paragraph).

Let me give an example of how much the structure of a database. You remember when working with array ? We had an array that stores data about a person. This kept array: name, email, city, blog and knowledge.

We incorporate the idea of ​​the array to a table. So to store information from people, we would create a table, in this case we call it the 'people'. Now the information you need to store this table will be organized by fields. That would be exactly the data you would like to save name, email, city, blog and knowledge.

Until now it was clear right? Database is a place to store information that is organized by tables and fields.

In web systems, most of the time when we need to perform operations in MySQL, we use a very cool feature called PhpMyAdmin . PhpMyAdmin is a system made in PHP which administers MySQL. So to create database tables, fields, and things ALWAYS ouras (here in mini-course) we will use PhpMyAdmin. I assume you installed WAMP to take this course. The leaves have WAMP phpMyAdmin up and running on your machine. To access PhpMyAdmin from the following address:

http://localhost/phpmyadmin/

You'll see something like the image below:

Imagine this, you want to save very important documents in your database. So for this, the bank provides a safe (1). In this safe, they need to save documents or whatever N customers, so they sort of drawers (2), one for each client is clear. In these drawers, you can save anything (3) ... Since documents, jewelry, money and whatever else crossed his mind.

Then there are three steps:

1. Safe
2. Drawer
3. Information

Mysql works the same way, there are three steps to your data being stored. You need to know where (1) your data will be stored, what is the 'drawer' (named DataBase (2)) should be used and finally, the information (3).

1. Location: It will be our HOST. This is an address at which our application (site, system ..) will connect.
2. DataBase: The safe is the same for N right customer? So the database is the same for N Databases, imagine that you are on your computer running their five customer sites and both need to use MySQL. Through the name of the DataBase you will be able to distinguish each one. Works like the drawers in the copper ... each client has its guard and what you want.
3. Info: Here, as in the vault, you'll save whatever you want. For example, your site has news, users, and downloads a list of partner links. You will have in their database tables: news, users and downloads.

This was all clear? As we begin an application (site, system ..) always have to create a DataBase and then insert the tables.

Our news will be no different. We will create a database and create table 'news'. When we access this data, PHP will more or less this: Connect to 'localhost' (HOST), now open the DataBase 'sistema_noticias' (or any name related to the site). Once the site has reached this point, it is very easy to do anything. For example ask to list all the information table 'news' (look at certain information that is inside the drawer).

GOOD ENOUGH FOR TEORIAAAAAAAAAAA !!!!!!!!!!!!

Let's practice a bit, open PhpMyAdmin again. Note that the top, he's showing you what HOST. So let's learn how to create the database and create the tables.

In the main phpMyAdmin has an option called "Create New Band of Data". In this field, enter the name of the DataBase, as suggested in the previous paragraph, we can use 'sistema_noticias' after typing, click "Create". Do not change the configuration of 'Collation'. See the picture below:

After you create the DataBase, realize that it will be selected on the left side of the screen and the right side displays the following message: "Database sistema_noticias was created."

Now we need to create the tables. How do we define the scope of the project , we have the following tables: users, categories and news

I will explain how to create the table "User". Well, let's create the rest of the tables as we desenvolvedo. Let's create the table, and while we will not enter any data.

As I said, a table is organized by fields. The fields are exactly the information that the table will store. For example, our users table must store: name, email, username, password and date of registration.
But is not that simple to specify the fields, since for each data type, there's kind of appropriate field. See the calendar method below:

Let's say that these types of fields are most commonly used. To learn more about the types of fields click here and see directly into the MySQL documentation.

Every table, to be organized, a field that needs to store the code register (registration). Normally set this field with the name 'ID'. This field is numeric (INT).

Our users table will have the following structure:

• id: INT
• name: VARCHAR (50) - size of 50 characters
• email: VARCHAR (50)
• User: VARCHAR (12)
• password: VARCHAR (12)
• data_cadastro: DATETIME

* Field names can not have accents, spaces, start with numbers.

Another important thing is that when creating the ID field, we define it as AUTOINCREMENT and as PRIMARY KEY. This will make this code will never be repeated and each new record in the database it will automatically increment. This ID will serve only one code to be used in operations in MySQL, you do not need to know what your code.

On the screen that after creating the DabaBase loaded (if closed, select the DataBase in the combobox on the right), go to "Create new table in database sistema_noticias

1. Enter the name of the table "users"
2. The index number is the number of fields that the table will have, in our case is 6 (have to count the ID)
3. Click Run

Now he created a form with all the options to create our table. Register fields in the following order: id, name, email, password, and enter cata_cadastro corresponding field types.

See the picture below:

See? There's not much secret to working with Mysql. Using PhpMyAdmin is easy. Now click Save.

Note that it will display a success message and will display the SQL code that created the table. If we did not use PhpMyAdmin, we had to do it on the nail heheheheheheh

On the left side of the screen you can see the table we just created.

If you want to play around in the top menu click on 'insert' and try to insert some records, then delete them. Just to do with work. In the next post I will explain to access this database from PHP.

If doubt remained, or poorly explained, or said something wrong please post. A big hug!