MySQL with PHP

[Gyan Guru]

This interaction between php and mysql has become quite famous since the language and the database are widely used Web now, and they are fairly simple to master.

Explanations

What is a database?

A database, as its name suggests, is used to store data.
For example, if each load of your web page, you want to save information from visitors to make the latest statistics, or if (another example) you want to make any form to your site, then you'll need a database to store all the information.

A database consists generally of tables that contain records. These records are broken down into fields. Example: a database called "mon_site" may contain a table visitors and a table "members" if I resumed the previous example. The fields of the table "members" can be: id, name, surname, city, date_inscription, etc..

We will not see how to use mysql itself, as this is not the goal. But it is clear that we must create the structure of your database before you can use with PHP. For this you can use the now famous Hawaii for example, which is an outline of a concrete table "sites":

2 - Connect to a database with php mysql

To connect to a php mysql, use the function mysql_connect () as in the example below. Usually we put the code below into a separate file that is included in all pages that need the database. (with include ())

<?. Php 

$ dbhost = "localhost", / / address of the server mysql 
$ dbuname = "Damien"; / / username mysql 
$ dbpass = "7kuPlKKv8"; / / password mysql 
$ dbname = "monsite_www"; / / name of the database to which you connect 


$ idbase = mysql_connect ($ dbhost, $ dbuname, $ dbpass); / / logs on ... 
mysql_select_db ($ dbname); / / selected on the basis of data 

>

So, with this you are connected to your MySQL database, it can only do what we call queries, ie to trade standards on the database, including we will see the most common combinations: add, delete, modify, view recordings.

Note that you have all the complete syntax for functions that can be used in php mysql

3 - Add a record in mysql

To queries mysql, know first that a php recurrent, it is the function mysql_query (). This function will be setting an SQL, if you already know the syntax of SQL another database, the task will be very simple for you, if not read well after ;-)

To add a record with php mysql, php instruction look like this:

<?. Php 

$ reqadd = mysql_query ( "INSERT INTO members (name, surname, date_inscription) VALUES ( 'Desrousseaux', 'Damien', now ())) / / add a record (a) in the table" members " 
/ / Write the function mysql now () which inserts the current date 

>

The result of the request contained in $ reqadd be TRUE if the request was executed correctly or FALSE if it is not good. In the case of false, you can do an echo mysql_error (); "just to view the details of the error, and so easy to debug your script.

[Gyan Guru]

4 - Modify a record with php mysql

To modify a record with php mysql, there is a UPDATE query like this:

<?. Php 



$ reqmod = mysql_query ( "UPDATE members SET name = 'smith' name = 'foo' WHERE id ='1 '"); 
/ / Changing the name and surname of the registration number 1 



>

id is a field of the table members, I have not used in the insertion of registration because it increases automatically. (auto_increment property of the field in your phpmyadmin).

Note also that if I do not say that the registration number 1 I want to change (If I = WHERE id ='1 '), mysql include that: "members in the table, change the name smith and the name in toto ", which will have to change all your records. And attention because if you had members inside, they go all called foo smith now so you will lose everything if you just forget where ...

5 - Delete a record with php mysql

To delete a record with php mysql:

<?. Php 

$ reqmod = mysql_query ( "DELETE FROM members WHERE id ='1 '"); 
/ / Removes the registration table 1 of the members. 

>

Again, do not forget the WHERE ;-)

6 - Record show with php mysql

Now you tell me: but what if I want to see all the members of my site in a page? (if I keep the same example of members but it applies to anything else.)

There are several possibilities for this, I actually see an example:

<?. Php 



/ / Select all the fields of members, increasing class by name 
$ reqmembres = mysql_query ( "T * SELECTION FROM members ORDER BY name ASC"); 

/ / $ Reqmembres is an object that now contains all the information of members 
/ / We'll scan a while to read all the records ... 
while ($ infomembres = mysql_fetch_array ($ reqmembres)) 
( 
echo "Member No."; 
echo $ infomembres [ 'id']; 
echo ""; 
echo $ infomembres [ 'firstname']; 
echo ""; 
echo $ infomembres [ 'name']; 
) 



>

This will result in your display this page:
Member No. 1: foo smith
Member No. 2: .............

attention, since I have classified by name growing membership, the member 2 could arrive before the 1 in my case. For rank by number of member should have been put ORDER BY id ASC.

[Gyan Guru]

Conclusion of tutorial

This is the tutorial is nearing its end, I hope you will have learned a lot and thank you to be more and more likely to bear an interest in such courses.
After reading, you know mysql queries returning in 95% of cases with PHP, so you will be able to make a good small site loo.