PHP Getting Started Tutorial-database operations

Introduction to PHP Tutorials-Database Tutorial operations

In this tutorial, we will build a small web site step-by-step, using the following features of PHP and MySQL:


1. View the database;


2. Edit the records of the database;


3. Modify the records of the database;


4. Delete the records of the database.


We will learn both MySQL and PHP together to feel them. This article directly from here to learn, if not install the configuration apache+php+mysql please check the Web page teaching related articles!


Run the Web server first (the PHP extension has been added), and run MySQL.


Create and manipulate a MySQL database:


First we need to create the database and table to use. The database is named "Example" and the table is named "TBL" with the following fields: Identification number, first name, last name, and information. To complete the work of building and defining tables through the MySQL tutorial terminal, just double-click or run C:mysqlbinmysql.exe.


If you want to see which tables are already defined in MySQL, you can use (note that mysql> is a terminal prompt):


mysql> show databases; < return >


This command may display the following information:


+----------+


| Database |


+----------+


| MySQL |


| Test |


+----------+


2 rows in Set (0.01 sec)


To define a new database (example), type:


mysql> CREATE DATABASE example; < return >


You will see an answer, such as:


Query OK, 1 row affected (0.17 sec) is very fat, we now have a new database. Now we can create a new table in the library, but first we need to select a new database:


Mysql> use example; < return >


The answer should be:


Database changed


Now we can build the table with the following fields:


Index number-integer


Username-a string with a maximum length of 30


User last name-a string with a maximum length of 50


Free information-a string with a maximum length of 100


At the MySQL prompt, type the following command to create the table:


Mysql> CREATE TABLE tbl (idx integer (3), UserName varchar (), LastName varchar (a), FreeText varchar (MB));< return >


The answer should be:


Query OK, 0 rows affected (0.01 sec)


OK, let's take a look at what the table looks like from the MySQL prompt, type the command:


Mysql> show columns from TBL; < return >


We will get the following results:


+----------+--------------+------+-----+---------+-------+


| Field | Type | Null | Key | Default | Extra |


+----------+--------------+------+-----+---------+-------+


| IDX | Int (3) | YES | | NULL | |


| UserName | varchar (30) | YES | | NULL | |


| LastName | varchar (50) | YES | | NULL | |


| FreeText | varchar (100) | YES | | NULL | |


+----------+--------------+------+-----+---------+-------+

4 rows in Set (0.00 sec)


Here, we can see the contents of the table "TBL" just created.


Now let's look at what's in the table. Type the following command:


Mysql> SELECT * from tbl;< carriage return >


This command is used to display all the data in the table "TBL". The output may be:


Empty Set (0.07 sec) gets this result because we haven't inserted any data in the table yet. Let's insert some data into the table and type:


mysql> INSERT into TBL values (1, ' Rafi ', ' Ton ', ' Just a test ');< carriage return >


Query OK, 1 row affected (0.04 sec)


As you can see, the values we insert into the table follow the order in which we defined the table, because the default order is used. We can set the order of the data, the syntax is as follows:


mysql> INSERT INTO TBL (Idx,username,lastname,freetext) VALUES (1, ' Rafi ', ' Ton ', ' Just a test ');< carriage return >


OK, now we can look at the contents of the table again:


Mysql> SELECT * from tbl;< carriage return >


The result of this time is:


+------+----------+----------+-------------+


| IDX | UserName | LastName | FreeText |


+------+----------+----------+-------------+


| 1 | Rafi | Ton | Just a Test |


+------+----------+----------+-------------+


1 row in Set (0.00 sec)


Now we can see the structure of the table and the contents of each cell.


Now we want to delete the data. In order to implement we should type:


Mysql> Delete from tbl where idx=1 limit 1;< carriage return > Query OK, 1 row Affected (0.00 sec)


Well, give some explanations. We're telling MySQL to delete records from the TBL table, delete those records with a IDX field value of 1, and restrict the deletion of only one record. If we do not limit the number of deleted records to 1, then all records with IDX 1 will be deleted (we have only one record in this example, but I just want to make this a little clearer, though).


Unfortunately, we got an empty table again, so let's go in again:


mysql> INSERT into TBL values (1, ' Rafi ', ' Ton ', ' Just a test ');< carriage return >


Query OK, 1 row affected (0.04 sec)


Another thing you can do is modify the contents of the specified field and use the "Update" command:


Mysql>update tbl Set username= ' Berber ' where username= ' Rafi ';< ' return >


Query OK, 1 row affected (0.01 sec)


Rows matched:1 changed:1 warnings:0


This command searches all records username as "Rafi" and changes it to "Berber". Note that the set part and where part are not necessarily the same. We can search a field but change another field. Also, we can perform two or more search criteria.


Mysql>update tbl Set username= ' Rafi ' where username= ' Berber ' and lastname= ' Ton ';< ' to return >


Query OK, 1 row affected (0.04 sec)


This query searches for two fields, changing the value of the username

Combining PHP with MySQL
In this section, we'll build a single, PHP-based Web site to control the MySQL tables we built earlier.
We will build the following site structure (assuming you already know some basic HTML knowledge):
1. INDEX.PHP3 for front view table 2. ADD.PHP3 for inserting data into a table
3. MODIFY.PHP3 is used to modify the records in the table 4. Del.php3 used to delete records in a table
First, we want to look at the database and see the following script:
--------------------------------------------------------------------------------
index.php

<html>


<head><title>web Database Sample index</title>


</head>


<body bgcolor= #ffffff >


<h2>data from Tbl</h2>


?


Mysql_connect () or Die ("Problem Connecting to DataBase");


$query = "SELECT * from TBL";


$result = Mysql_db_query ("Example", $query);


if ($result) {


echo "Found These entries in the database:<br><p></p>";


echo "<table width=90% align=center border=1><tr>


<TD align=center bgcolor= #00FFFF >user name</td>


<TD align=center bgcolor= #00FFFF >last name</td>


<TD align=center bgcolor= #00FFFF >domain name</td>


<TD align=center bgcolor= #00FFFF >request date</td>


</tr> ";


while ($r = Mysql_fetch_array ($result))


{


$idx = $r ["idx"];


$user = $r ["UserName"];


$last = $r ["LastName"];


$text = $r ["FreeText"];


echo "<tr>


<td> $idx </td>


<td> $user </td>


<td> $last </td>


<td> $text </td>


</tr> ";


}


echo "</table>";


}


Else


{


echo "No data."


}


Mysql_free_result ($result);


Include (' links.x ');


?>


</body>


</html>


--------------------------------------------------------------------------------


OK, here are some instructions:


We first create the THML document with the normal HTML tags. When we want to go from HTML to PHP, we use the To open PHP section, which tells the Web server to think of the following text as PHP syntax rather than general HTML. Use?> to end the PHP section.


The mysql_connect () command tells PHP to establish a connection to the MySQL server. If the connection is successful, the script will continue and, if unsuccessful, print out the information for the die command "Problem connecting to Database" (if you want to see more information about mysql_connect and other PHP functions, you can go to http:// Find in the document under Www.php.net).


Now, if MySQL is installed as we discussed above, that's enough. But if you're using a pre-installed MySQL (like an ISP), you should use the following command:


mysql_connect (localhost, username, password);


We can set the $query to the query we want to execute in MySQL, and then use the Mysql_db_query command to execute it:


$result = Mysql_db_query ("Example", $query);


At this point, "example" represents the name of the database and $query is the query to be made.


We use the MySQL command select (as described above) to get all the data from the table:


$query = "SELECT * from TBL";


To explain briefly the role of $result, if executed successfully, the function returns a MySQL result identifier for the query result, and returns False if an error occurs. The return is not a result but an identifier, which can be converted to the information we need later.


Now, we want to check if there are records in the database, and if so, print the results in the HTML table structure. To check for data, we use the IF command and the following syntax:


if (argument) {


"Do something;"


} else {


"Do something different;"


}


At this time, "do something" when argument=true the command you want to execute, "do something different" as the argument =false when the command to execute.


Note that we use the echo command to output some HTML tags to create the HTML table structure. Only text that is output from the PHP command is considered HTML content-the PHP command itself is not considered HTML content. Another command we use is the while instruction, which uses the following format:


while (argument)) {


"Something to Do";


}


The while loop repeats repeatedly at Argument=true, executing the instruction set in {}.


Here we combine the while loop and the PHP function $r=mysql_fetch_array ($result). This function retrieves a record based on the corresponding result identifier and places the result in an associated array (associative array) $r, which uses the name of the field as the key value of the array. In our script we'll get an array: $r [' idx '], $r [' UserName '], $r [' LastName '] and


$r [' FreeText '].


We can also use the Mysql_fetch_row function, which places the result in an ordered array, we can use $r[0], $r [1], $r [2] and $r[3] to get the corresponding value.


Now that we have all the information, we can print it out in the HTML table:


The following are the referenced contents:


echo "<tr>


<td> $idx </td>


<td> $user </td>


<td> $last </td>


<td> $text </td>


</tr> ";


Now we can release the MySQL connection and release some resources by using the Mysql_free_result ($result) function.


Another useful feature of PHP is the ability to include text files in your scripts. Let's assume you have some reusable code (such as a link to another page) and we can use the Include function to save some code and time. And, if you want to change the code, we just need to change the contents of the containing file, which will take effect in all the files that include it.


Here we create a text file called Links.x that will store all of the link menus we want to use on each page.


<p></p>


<ul>


<li><a href= "Index.php3" >Home</a>


<li><a href= "Add.php3" >add a new entry to the database</a>


<li><a href= "Edit.php3" >edit an entry</a>


<li><a href= "DEL.PHP3" >delete a entry from the database</a>


</ul>


The syntax for include is:


Include (' Included_text_file ');


Now we can use?> to close the PHP section and use </body></html> to end the HTML page.


Use the form to add data let's take a look at the following code:


--------------------------------------------------------------------------------


<html>


<head><title>add an entry to the database</title>


</head>


<body bgcolor= #ffffff >


<h1>add an entry</h1>


<form method= "POST" action= "ADD2TBL.PHP3" >


<table width=90% align=center>


<tr><td>index:</td><td><input type=text name= "idx" size=3 maxlength=3></td> </tr> <tr><td>username:</td><td><input type=text name= "UserName" size=40


Maxlength=100></td></tr>


<tr><td>lastname:</td><td><input type=text name= "LastName" size=40


Maxlength=100></td></tr> <tr><td>freetext:</td><td><input Type=text Name= "FreeText" s=40 maxlength=100></td></tr> <tr><td></td><td><input Type=submit value=add></td></tr>


</form>


</table>


<?php include (' links.x ');? >


</body>


</html>


--------------------------------------------------------------------------------


Assuming you are familiar with the form, this is a fairly simple script. We designed a form based on an HTML page that calls the Add2tbl.php3 script after it commits. The form now corresponds to a MySQL table with 4 fields: Index Number,firstname,lastname and FreeText. Note that the field name in this form is the same as the field name in the MySQL table, but this is only for convenience rather than necessity.


We have once again used the include command. Include (' links.x ');? > (as explained earlier) to add links.


Let's take a look at the add2tbl.php3 script:


--------------------------------------------------------------------------------


<html>


<body>


?


if ($UserName)


{


Mysql_connect () or Die ("Problem Connecting to DataBase");


$query = "INSERT into TBL values (' $idx ', ' $UserName ', ' $LastName ', ' $FreeText ')";


$result = Mysql_db_query ("Example", $query);


echo "Data inserted. New Table:<br><p></p> ";


$query = "SELECT * from TBL";


$result = Mysql_db_query ("Example", $query);


if ($result)


{


echo "<table width=90% align=center border=1><tr>


<TD align=center bgcolor= #00FFFF >idx</td>


<TD align=center bgcolor= #00FFFF >user name</td>


<TD align=center bgcolor= #00FFFF >last name</td>


<TD align=center bgcolor= #00FFFF >free text</td>


</tr> ";


while ($r = Mysql_fetch_array ($result))


{


$idx = $r ["idx"];


$user


Note the comments I made in the script. Use a comment to "//", and the server ignores the later part of the row.

Simple, isn't it? To edit a record from a database: Let's assume that we want to modify the records that exist in the database. In the front, we saw a SQL command called set used to set the value of a field in the database. We will use this command to modify the entire record in the database. Consider the following script:


--------------------------------------------------------------------------------


edit.php:


<html>


<head><title>editing an entry from the database</title>


</head>


<body bgcolor= #ffffff >


<h1>edit an ENTRY</H1


?


Mysql_connect () or Die ("Problem Connecting to DataBase");


$query = "SELECT * from TBL";


$result = Mysql_db_query ("Example", $query);


if ($result)


{


echo "Found These entries in the database:<br>";


echo "<table width=90% align=center border=1><tr>


<TD align=center bgcolor= #00ffff >idx</td>


<TD align=center bgcolor= #00FFFF >user name</td>


<TD align=center bgcolor= #00FFFF >last name</td>


<TD align=center bgcolor= #00FFFF >free text</td>


</tr> ";


while ($r = Mysql_fetch_array ($result))


{


$idx = $r ["idx"];


$user = $r ["UserName"];


$last = $r ["LastName"];


$text = $r ["FreeText"];


echo "<tr>


<TD align=center>


<a href= "editing.php3?idx= $idx &user= $user &last= $last &text= $text" > $idx </a></td>


<td> $user </td>


<td> $last </td>


<td> $text </td>


</tr> ";


}


echo "</table>";


}


Else


{


echo "No data."


}


Mysql_free_result ($result);


Include (' links.x ');


?>


</body>


</html>


--------------------------------------------------------------------------------


As you can see, the code here is somewhat familiar. The first part just prints out the contents of the table in the database. Note that one line is not the same:


<a href= "editing.php3?idx= $idx &user= $user &last= $last &text= $text" > $idx </a>


This line creates a link to the EDITING.PHP3 and passes some variables to the new script. It's very much like a form, just using a link. We convert information to: variables and values. Note that in order to print out "symbols, we need to use" otherwise the server will see it as part of the PHP script and as printed information.


We wanted to convert all of the records in the database so that we could get the exact data in the table so that we could modify it a little easier.


--------------------------------------------------------------------------------


editing.php


<html>


<head><title>editing an entry</title>


</head>


<body bgcolor= #ffffff >


<h1>editing an entry</h1>


<form method= "POST" action= "EDITDB.PHP3" >


<table width=90% align=center>


<tr><td>idx:</td><td><?php echo "$idx";? ></td></tr>


<tr><td>username:</td><td><input type=text name=username size=40 maxlength=100


Value= "<?php echo" $user ";? > "></td></tr>


<tr><td>lastname:</td><td><input type=text name=lastname size=40 maxlength=100


Value= "<?php echo" $last ";? > "></td></tr>


<tr><td>free text:</td><td><input type=text name=freetext size=40 maxlength=100


Value= "<?php echo" $text ";? > "></td></tr>


<tr><td></td><td><input type=submit value= "Edit it!" ></td></tr></table>


<input type=hidden name=idx value= "<?php echo" $idx "? > ">


</form>


<?php include (' links.x ');? >


</body>


</html>


--------------------------------------------------------------------------------


OK, this script is very simple. We want to be concerned that when the form is printed, it records the data of the current record, through the value attribute in the <input type= > command. This data is passed from the previous page.


Now, if we don't change the record, it returns the current value, which is the default value. If we change the value of the field, the value of the field becomes the new value. We can then pass the new value to another script that will change the value in the MySQL table.


--------------------------------------------------------------------------------


editdb.php:


<?php


Mysql_connect () or Die ("Problem Connecting to DataBase");


$query = "Update tbl Set


idx= ' $idx ', username= ' $UserName ', lastname= ' $LastName ', freetext= ' $FreeText ' where


idx= ' $idx ';


$result = Mysql_db_query ("Example", $query);


$query = "SELECT * from TBL";


$result = Mysql_db_query ("Example", $query);


if ($result)


{


echo "Found These entries in the database:<br><p></p>";


echo "<table width=90% align=center border=1><tr>


<TD align=center bgcolor= #00FFFF >idx</td>


<TD align=center bgcolor= #00FFFF >user name</td>


<TD align=center bgcolor= #00FFFF >last name</td>


<TD align=center bgcolor= #00FFFF >free text</td>


</tr> ";


while ($r = Mysql_fetch_array ($result))


{


$idx = $r ["idx"];


$user = $r ["UserName"];


$last = $r ["LastName"];


$text = $r ["FreeText"];


echo "<tr>


<td> $idx </td>


<td> $user </td>


<td> $last </td>


<td> $text </td>


</tr> ";


}


echo "</table>";


}


Else


{


echo "No data."


}


Mysql_free_result ($result);


Include (' links.x ');


?>


--------------------------------------------------------------------------------


One of the things that basically concerns you is the following line:


$query = "Update tbl set idx= ' $idx ', username= ' $UserName ', lastname= ' $LastName ', freetext= ' $FreeText ' where idx= ' $idx '";


Note that it is the same syntax we explained in the previous MySQL section. Another thing, note that this script changes the record of the idx= $idx, and if there are multiple IDX equals $IDX records in the table, the records will be changed. If we want to be more strict, we can change the WHERE clause as follows:


$query = "Update tbl set idx= ' $idx ', username= ' $UserName ', lastname= ' $LastName ', freetext= ' $FreeText ' where idx= ' $idx ' and Username= ' $UserName ' and lastname= ' $LastName ' and freetext= ' $FreeText ';


This syntax will check all the fields, not just check the IDX.


To delete a record from the database:


Well, it's easy to remove. We still need two scripts: one to select the records to delete (basically the same as the record you selected to edit), one to actually delete and print the new table.


--------------------------------------------------------------------------------


del.php


<html>


<head><title>deleting an entry from the database</title>


</head>


<body bgcolor= #ffffff >


<h1>del an entry</h1>


?


Mysql_connect () or Die ("Problem Connecting to DataBase");


$query = "SELECT * from TBL";


$result = Mysql_db_query ("Example", $query);


if ($result)


{


echo "Found These entries in the database:<br><p></p>";


echo "<table width=90% align=center border=1><tr>


<TD align=center bgcolor= #00ffff >idx</td>


<TD align=center bgcolor= #00FFFF >user name</td>


<TD align=center bgcolor= #00FFFF >last name</td>


<TD align=center bgcolor= #00FFFF >free text</td>


</tr> ";


while ($r = Mysql_fetch_array ($result))


{


$idx = $r ["idx"];


$user = $r ["UserName"];


$last = $r ["LastName"];


$text = $r ["FreeText"];


echo "<tr>


<TD align=center>


<a href= "Dele.php3?


idx= $idx &username= $user &lastname= $last &freetext= $text "> $idx </a></td>


<td> $user </td>


<td> $last </td>


<td> $dtext </td>


</tr> ";


}


echo "</table>";


}


Else


{


echo "No data."


}


Mysql_free_result ($result);


Include (' links.x ');


?>


</body>


</html>