PHP and Mysql connection

Create sample database and table


To create a sample database and tables that you will work with,
please open your DOS window and type the following text to log into your database server as shown below:

mysql log in 

In this tutorial, I use root user and root password to log in to my local database server (localhost). If you provided correct user and password, you will successfully log in to your server.

-To create a sample database (e.g.dbstudent), please type the following command:
            mysql>CREATE DATABASE dbstudent;
            -To create a sample table (e.g. tblstudent) in this database, type:
            mysql>USE dbstudent;
            mysql>CREATE TABLE tblstudent(id varchar(5) not null,name varchar(20));
 
Now you have a database and a table created. Next, you will insert some data to the table that will be later retrieved by PHP code.
            -To insert test data, type:
            mysql>INSERT INTO tblstudent(id,name) VALUES ('1 ', 'Dara Yuk ');
           
            mysql>INSERT INTO tblstudent(id,name) VALUES ('2 ','Khorn Channa ');
            -To see your records in the table type:
            mysql>SELECT * FROM tblstudent;

Get data by PHP code

Finally, you will write PHP code to connect and get data as shown below:
 
<html>
<body>
<?php
/*create connection to dbstudent database in local machine with user root and password root*/
$db = mysql_connect("localhost", "root","root");
mysql_select_db("dbstudent",$db);
//Create record set
$result = mysql_query("SELECT * FROM tblstudent",$db);
//Show data
echo "<table border=1>\n";
echo "<tr><td>ID</td><td>Name</tr>\n";
while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s</td><td>%s</td></tr>\n", $myrow[1], $myrow[2]);
 
}
echo "</table>\n";
?>
</body>
</html>
 
Insert data into the database table
To insert data in to the database file, you need to use INSERT INTO statement.

Syntax:
 INSERT INTO table name(field1,field2,..,fieldn)
 Values(val1, val2,...,valn); 

Example: in this example, we will insert the values of '005' and 'Sovatha' to the tblstudent table.
$query = "INSERT INTO tblstudent(id, name) Values('005','Sovatha')";
$result = mysql_query($query) or die ("Error:Couldn't execute query.");
mysql_close($connection);


Update data in a database table
To update data in a table of database, you need to use UPDATE statement.

Syntax:
UPDATE table name SET(field1=val1,field2=val2,...,fieldn=valn)
WHERE condition

Example: in this example, we will update the data in the name field where the value of field id is equal to '005'.
$query = "UPDATE tblstudent SET name='Seng Votha' WHERE id='005'";
$result = mysql_query($query) or die ("Error:Couldn't execute query.");
mysql_close($connection);

Delete data from a database table
To delete data from a table, you need to use DELETE statement.

Syntax:
DELETE * FROM table name WHERE condition

Example: in this example, we will delete all data from the tblstudent table where the value of the id field is equal to '005'.
$query = "DELETE * FROM tblstudent WHERE id='005'";
$result = mysql_query($query) or die ("Error:Couldn't execute query.");
mysql_close($connection);



HTML Comment Box is loading comments...



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.