Click here to Skip to main content
13,557,860 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 12 Oct 2012
Licenced CPOL

Save Records to Database with PHP and MySQL

, 12 Oct 2012
Rate this:
Please Sign up or sign in to vote.
For those of you who do not know how to create an insert using PHP and MySQL, here I will teach you.

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:

// 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.';

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

    echo 'User information saved successfully.';
    echo 'Error: We encountered an error while inserting the new record.';

The expected output for this script should be:

User information saved successfully.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer
Puerto Rico Puerto Rico
As a computer scientist, I strive to learn more everyday in my field. Everything I learn, I share with my community by writing articles for future references.

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Mike Barthold18-Oct-12 1:25
memberMike Barthold18-Oct-12 1:25 
GeneralVery informative Pin
miguelss15-Oct-12 6:04
membermiguelss15-Oct-12 6:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180515.1 | Last Updated 12 Oct 2012
Article Copyright 2012 by jotorres
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid