Skip to main content

Inserting Data using PHP into MySQL Database


In this course we will focus on how to INSERT data using PHP into MySQL Database. When inserting data into Mysql Database tables we need to remember the names and types of table columns. For example if you set your field as the name of the person so as expected you must insert string value or if you are referring into the age of a person meaning you need to enter a numeric value. Below are the sample code for inserting data into MySQL database table.

  1. <?php
  2. //set up for mysql Connection
  3. $dbhost = 'localhost';
  4. $dbuser = 'root';
  5. $dbpass = '';
  6. $conn = mysql_connect($dbhost, $dbuser, $dbpass);
  7. //test if the connection is established successfully then it will proceed in next process else it will throw an error message
  8. if(! $conn )
  9. {
  10. die('Could not connect: ' . mysql_error());
  11. }
  12.  
  13. //we specify here the Database name we are using
  14. mysql_select_db('studentdb');
  15. //It wiil insert a row to our tblstudent`
  16. $sql = "INSERT INTO `studentdb`.`tblstudent` (`s_id`, `firstname`, `lastname`, `email`, `phone`)
  17. VALUES (NULL, 'Joken', 'Villanueva', '[email protected]', '09983344318');";
  18. //we are using mysql_query function. it returns a resource on true else False on error
  19. $retval = mysql_query( $sql, $conn );
  20. if(! $retval )
  21. {
  22. die('Could not enter data: ' . mysql_error());
  23. }
  24. echo "Entered data successfully\n";
  25. //close of connection
  26. mysql_close($conn);
  27. ?>

After successfully executed, we will proceed now in improving our program because in real world application all the data must be based on user inputs through html forms before it will be taken using PHP scripts and will be inserted into a MySQL database.

This time we're going to create an html file that will accept user input(s) and name it as “AddStudent.php”

  1. <html>
  2. <head>
  3. <title>Add Student Record</title>
  4. <link rel="stylesheet" type="text/css" href="stylesheet/main.css">
  5. </head>
  6. <body>
  7.  
  8. <form method="post" action="process_addstud.php">
  9. <table width="400" border="0" cellspacing="1" cellpadding="2" align="center">
  10.  
  11. <th colspan="2" align="left">Add Student</h2>
  12. <tr>
  13. <td width="100">First Name</td>
  14. <td><input name="f_name" type="text" id="f_name"></td>
  15. </tr>
  16. <tr>
  17. <td width="100">Last Name</td>
  18. <td><input name="l_name" type="text" id="l_name"></td>
  19. </tr>
  20. <tr>
  21. <td width="100">Email</td>
  22. <td><input name="email" type="text" id="email"></td>
  23. </tr>
  24. <tr>
  25. <td width="100">Contact</td>
  26. <td><input name="contact" type="text" id="contact"></td>
  27. </tr>
  28. <td width="100"> </td>
  29. <td>
  30. <input name="save" type="submit" id="save" value="Add Student">
  31. </td>
  32. </tr>
  33. </table>
  34. </form>
  35. </body>
  36. </html>

Then create a new PHP file that will process the user input and save the values to the MySQL database save this as “process_addstud.php”.

  1. <?php
  2.  
  3. //set up for mysql Connection
  4. $dbhost = 'localhost';
  5. $dbuser = 'root';
  6. $dbpass = '';
  7. $conn = mysql_connect($dbhost, $dbuser, $dbpass);
  8. //test if the connection is established successfully then it will proceed in next process else it will throw an error message
  9. if(! $conn )
  10. {
  11. die('Could not connect: ' . mysql_error());
  12. }
  13.  
  14. //we specify here the Database name we are using
  15. mysql_select_db('studentdb');
  16. $f_name = $_POST['f_name'];
  17. $l_name = $_POST['l_name'];
  18. $email = $_POST['email'];
  19. $contact= $_POST['contact'];
  20.  
  21. //It wiil insert a row to our tblstudent`
  22. $sql = "INSERT INTO `studentdb`.`tblstudent` (`s_id`, `firstname`, `lastname`, `email`, `phone`)
  23. VALUES (NULL, '{$f_name}', '{$l_name}', '{$email}', '{$contact}');";
  24. //we are using mysql_query function. it returns a resource on true else False on error
  25. $retval = mysql_query( $sql, $conn );
  26. if(! $retval )
  27. {
  28. die('Could not enter data: ' . mysql_error());
  29. }
  30. ?>
  31. <script type="text/javascript">
  32. alert("New Record is Added to the Database");
  33. window.location = "addStudent.php";
  34. </script>
  35. <?php
  36. //close of connection
  37. mysql_close($conn);
  38. ?>

The process of using this program to work for us. First open any web browser available in the computer then on the address bar type this http://localhost/student/addStudent.php and when you press enter you will see looks like as shown below.

addstudent

Add fill up the following like the First Name, Last Name, Email and Contact and finally click “add Student” button you will be redirected into “process_addstud.php”. And if saved successful it will give a message like as shown below. And when you click OK button you be again redirected into "addStudent.php".

process_student

In this project I attached the studentdb for your convenience. If you have some question or any suggestion you can send a message. Hope you enjoy reading this stuff.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.