How to post data to a database table using PHP MySQL Into

How to post data to a database table using PHP MySQL Into

As MySql extension has been deprecated, the usual way to insert data into a database using php involves the use of mysqli(either procedural or oop version) or PDO.
Here we'll use mysqli. You can check the Php manual here: http://www.php.net/manual/en/book.mysqli.php

First of all, the database connection:

<?php
                      $host = "www.mysite.com";
                      $username = "marqusG";
                      $pwd = "12Ab34cD";
                      $db = "mydatabase";
                      $dbconn = mysqli_connect($host, $username, $pwd, $db);
                      if (mysqli_connect_errno()) {
                        echo "Connection to the database failed with the following error: " . mysqli_connect_error();
                      }

Select all Open in new window

Now we can insert some data in the database:

<?php
                      mysqli_query(
                      	$dbconn, 
                      	"INSERT INTO coders (NickName, Language, Level) VALUES ('marqusG', 'PHP', 'Poor')"
                      );

Select all Open in new window

As you guess, the most usual situation in the real world is inserting data from a form, so now we'll go to build a simple html form:

<form method="post">
                      NickName: <input type="text" name="nickname" />
                      Language: <input type="text" name="language" />
                      Level: <input type="text" name="level" />
                      <input type="submit" />
                      </form>

Select all Open in new window

Note that omitting action for our form will make the form send data to the same page and later we'll see how to build the whole page. See now how the php code receives data and insert them into database (we'll use filter_input function available as PHP 5.2.0 to get form values from the $_POST array: http://www.php.net/manual/en/function.filter-input.php):

$nickname = filter_input(INPUT_POST, 'nickname');
                      $language = filter_input(INPUT_POST, 'language');
                      $level = filter_input(INPUT_POST, 'level');
                      $query = "INSERT INTO coders (NickName, Language, Level) VALUES ('$nickname', '$language', '$level');
                      
                      if (!mysqli_query($dbconn, $query)) {
                        die("Error inserting data into database: " . mysqli_error($dbconn));
                      }
                      echo "Data inserted successfully!";

Select all Open in new window

Finally we can build our page. You'll see I tried to gently present output to the user editing slightly the code above, but the core remains the same.

<?php
                      //SET DATABASE CONNECTION
                      $host = "www.mysite.com";
                      $username = "marqusG";
                      $pwd = "12Ab34cD";
                      $db = "mydatabase";
                      $dbconn = mysqli_connect($host, $username, $pwd, $db);
                      if (mysqli_connect_errno()) {
                        echo "Connection to the database failed with the following error: " . mysqli_connect_error();
                      }
                      
                      //CHECK IF FORM HAS BEEN SUBMITTED USING FILTER_HAS_VAR (http://www.php.net/manual/en/function.filter-has-var.php)
                      if (filter_has_var(INPUT_POST, 'nickname')) {
                      	//GET VALUES FROM THE $_POST ARRAY USING FILTER_INPUT (http://www.php.net/manual/en/function.filter-input.php)
                      	$nickname = filter_input(INPUT_POST, 'nickname');
                      	$language = filter_input(INPUT_POST, 'language');
                      	$level = filter_input(INPUT_POST, 'level');
                      	if (!mysqli_query($dbconn, $query)) {
                      		$output = "Error inserting data into database: " . mysqli_error($dbconn) . "<br />";
                      		$output .= "Please, retry.<br />";
                      		$output .= <<<FRM
                      		<form method="post">
                      		NickName: <input type="text" name="nickname" />
                      		Language: <input type="text" name="language" />
                      		Level: <input type="text" name="level" />
                      		<input type="submit" />
                      		</form>
                      FRM;
                      	} else {
                      	  $output = "Data inserted successfully!";
                      	  $ouput .= "Following data have been added to your database:<br />";
                      	  $ouput .= "NickName: $nickname<br />";
                      	  $ouput .= "Language: $language<br />";
                      	  $ouput .= "Level: $level<br />";
                      	}
                      }else{
                      	$ouput = "Please, insert your data in the form below:<br />";
                      	$output .= <<<FRM
                      	<form method="post">
                      	NickName: <input type="text" name="nickname" />
                      	Language: <input type="text" name="language" />
                      	Level: <input type="text" name="level" />
                      	<input type="submit" />
                      	</form>
                      FRM;
                      mysqli_close($dbconn);
                      }
                      ?>
                      <html> 
                      <body>
                      <?php echo $output; ?>
                      </body>
                      </html> 

Select all Open in new window