Save Records to Database with PHP and MySQL

This is how to save records to database with PHP and MySQL.

I had previously written about how to connect to a database using PHP and MySQL. It was very simple in that it was precise, and right to the point. In many cases, I got burned by people saying that this ’MySQL’ library is not to be used anymore, but the truth is, I have been working on a few very recent projects, and their development team does not want to migrate to the new mysqli library. In essence, this library is still highly used by companies, and will remain that way until it is decided to eliminate it for good.

So, for those of you who do not know how to create an insert using PHP and MySQL, here I will teach you.

PHP insertion utilizes the well known SQL syntax to execute MySQL queries in a PHP script. This same query we will use to save a record to our database. First, let’s create a database table. You can either create it using an apache server installation, or create it via mysql command.

Start the command-line tool mysql and select a database:

shell> mysql db-name

Create Table Users

To be able to understand and grasp the example, we will create a table named ‘Users’ and also create the required fields and datatypes respectively. The Table will have a Primary Key ID in which we will define during creation. This key will auto increment in value, thus, no value needs to be passed into this Prod.

CREATE TABLE users (                     
      users_id int(11) NOT NULL auto_increment,  
      users_fname VARCHAR(60),     
      users_lname VARCHAR(60),
      users_email VARCHAR(60),
      users_pass  VARCHAR(32),
      PRIMARY KEY  (users_id)
    );

Now that we have our database table, we can go ahead and insert(save) records within. We will achieve this by creating a database connection from your website to the server, and writing to the database. The server side script includes a host, username, password and name of the database to which the records are to be added into. To execute our queries, the connection needs to be built between the front-end and MySQL in the backend. Once your connection is built successfully, you can insert(save) the records to the database(tables). The die function( ) depict that the connection could not be established, thus exiting the script from further processing.

The approach would be as follows:

<?php
// Define database credentials
$host = 'localhost';
$user = 'username';
$password = 'password';
$database = 'mydb_name';
// Create db connection
$conn = mysql_connect($host,$user,$password) or die
('Error: Could not connect to database - '.mysql_error());
// Once connected, we can select a database
mysql_select_db($database,$conn) or die('Error in selecting the database: '.mysql_error());

// Grab user input and sanitize 
$fname = mysql_real_escape_string(filter_var(trim($_POST['fname']), FILTER_SANITIZE_STRING));
$lname = mysql_real_escape_string(filter_var(trim($_POST['lname']), FILTER_SANITIZE_STRING));
$pass = mysql_real_escape_string(filter_var(trim($_POST['pass']), FILTER_SANITIZE_STRING));

// Validate the user email
if(! $email = filter_var(trim($_POST['email']), FILTER_VALIDATE_EMAIL)))
{     
    echo 'Please insert a valid email.';
    die();
}

$sql = 'INSERT INTO `$database`.`users` (
`users_id` ,
`users_fname` ,
`users_lname` , 
`users_email`,
`users_pass`
)
VALUES (
NULL ,  ''.$fname.'', ''.$lname.'',  ''.$email.'', ''.$pass.'')';

if(mysql_query($sql))
{
    echo 'User information saved successfully.';
}else
{
    echo 'Error: We encountered an error while inserting the new record.';
}
mysql_close($conn);
?>

The expected output for this script should be:

User information saved successfully.