Click here to Skip to main content
Click here to Skip to main content

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.

Save records to database with PHP and MySQL.

I had previously wrote 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 an insert(save) records within. We will achieve this by creating a database connection from your website to the server, and writing to the databse. 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.

License

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

Share

About the Author

jotorres
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.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMike Barthold18-Oct-12 1:25 
GeneralVery informative Pinmembermiguelss15-Oct-12 6:04 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 12 Oct 2012
Article Copyright 2012 by jotorres
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid