Post
Ask Question

Database Connectivity (Insert and Select) With MySQL in PHP

This article explains how to insert and select data from a database into a HTML table in PHP. Using an insert statement you can insert data into a database. Using a select statement you can select the entire data from a database into a HTML table.

First of all we will create a database.

Create The Database

  1. <html>  
  2. <body bgcolor="pink">  
  3. <center>  
  4. <?php  
  5. $con = mysql_connect ("localhost","root","");  
  6. $db="MCN";  
  7. if (!$con)  
  8.   {  
  9.   die ('Could not connect: ' . mysql_error());  
  10.   }  
  11. if (mysql_query ("CREATE DATABASE $db",$con))  
  12.   {  
  13.   echo "Your Database Created Which Name is : $db";  
  14.   }  
  15. else  
  16.   {  
  17.   echo "Error creating database: " . mysql_error();  
  18.   }  
  19. mysql_close ($con);  
  20. ?>  
  21. </center>  
  22. }</body>  
  23. </html>  
Output

In the above code, we created a database named MCN. Now we will create a table in the MCN database.

image1.jpg

Create The Table

  1. <html>  
  2. <body bgcolor="pink">  
  3. <center>  
  4. <?php  
  5. $con = mysql_connect ("localhost","root","");  
  6. if (!$con)  
  7.   {  
  8.   die ('Could not connect: ' . mysql_error());  
  9.   }  
  10. mysql_select_db ("MCN", $con);  
  11. $sql = "CREATE TABLE EMPLOYEE  
  12. (  
  13. Name varchar(50),  
  14. Designation varchar(50),  
  15. Sal int,  
  16. Qualification varchar(50)  
  17. )";  
  18. mysql_query($sql,$con);  
  19. echo "Your  table created which is as follows";  
  20. mysql_close($con);  
  21. ?>  
  22. <table bgcolor="skyblue" border="2">  
  23. <tr>  
  24. <td colspan=2 align="center">Employee Table</td>  
  25. </tr>  
  26. <td>Name</td><td><input type="text" name="txt1"></td>  
  27. </tr>  
  28. <tr>  
  29. <td>Designation</td><td><input type="text" name="txt2"></td>  
  30. </tr>  
  31. <tr>  
  32. <td>Sal</td><td><input type="text" name="txt3"></td>  
  33. </tr>  
  34. <tr>  
  35. <td>Qualification</td><td><input type="text" name="txt4"></td>  
  36. </tr>  
  37. </table>  
  38. </center>  
  39. </body>  
  40. </html>  

 

Output

In the above code we created a table named employee. There are four fields in the employee table i.e. name, designation, sal, qualification.

image2.jpg

Insert Data in Table

Now we will write the code to insert data into the database table. The code for inserting data into the database table is as follows:

  1. <?php  
  2. $con=mysql_connect ("localhost","root","");  
  3. mysql_select_db("mcn",$con);  
  4. @$a=$_POST['txt1'];  
  5. @$b=$_POST['txt2'];  
  6. @$c=$_POST['txt3'];  
  7. @$d=$_POST['txt4'];  
  8. if(@$_POST['inser'])  
  9. {  
  10.  $s="insert into employee values ('$a','$b','$c','$d')";  
  11. echo "Your Data Inserted";  
  12.  mysql_query ($s);  
  13. }  
  14. $con=mysql_connect ("localhost","root","");  
  15. mysql_select_db ("mcn",$con);  
  16. if(@$_POST ['sel'])  
  17. {  
  18. echo $ins=mysql_query ("select * from employee");  
  19. echo "<table bgcolor=skyblue border='2'>  
  20.    <tr>  
  21.    <th colspan=4>Select Data From Employee Table</th></tr>  
  22.    <tr>  
  23.    <th>Nmae</th>  
  24.    <th>Designation</th>  
  25.    <th>Sal</th>  
  26.    <th>Qualification</th>  
  27.    </tr>";  
  28.    while ($row=mysql_fetch_array ($ins))  
  29.    {  
  30.    echo "<tr>";  
  31.    echo  "<th>".$row ['Name']."</th>";  
  32.    echo  "<th>". $row ['Designation']. "</th>";  
  33.    echo  "<th>". $row ['Sal']."</th>";  
  34.    echo  "<th>".$row ['Qualification']."</th>";  
  35.    echo "</tr>";  
  36.    }  
  37.    }  
  38.    echo "</table>"  
  39. ?>  
  40. <html>  
  41. <head>  
  42. </head>  
  43. <body bgcolor="pink">  
  44. <table bgcolor="skyblue" border="2">  
  45. <form method="post">  
  46. <tr>  
  47. <td colspan=2 align="center">Employee Table</td>  
  48. </tr>  
  49. <td>Name</td><td><input type="text" name="txt1"></td>  
  50. </tr>  
  51. <tr>  
  52. <td>Designation</td><td><input type="text" name="txt2"></td>  
  53. </tr>  
  54. <tr>  
  55. <td>Sal</td><td><input type="text" name="txt3"></td>  
  56. </tr>  
  57. <tr>  
  58. <td>Qualification</td><td><input type="text" name="txt4"></td>  
  59. </tr>  
  60. <tr>  
  61. <td><input type="submit" name="inser" value="Insert"></td>  
  62. <td><input type="submit" name="sel" value="Select"></td>  
  63. </tr>  
  64. </form>  
  65. </table>  
  66. </body>  
  67. </html>  
Output

Now we will insert the data into a HTML table then click on the insert button.

image3.jpg

When you click on the insert button then your data will be inserted in the database table. Like as in the following image.

image4.jpg

Select Data From Database

If you want to select all data from the database into the HTML table then you can do that. For this purpose you will be click on the select button. When you click on the select button then your data will be displayed in the HTML table, such as in the following image:

image5.jpg

Conclusion

So in this article, you saw how to insert and select data from a database into an HTML table. Using this article one can easily understand insert and select data from database into HTML table.

Some Helpful Resources


Similar Articles