March 16, 2012 learn_php

Save records to database with PHP and MySQL

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:

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.

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:

The expected output for this script should be:

User information saved successfully.

7 thoughts on “Save records to database with PHP and MySQL

  1. Couple of issues:

    CREATE TABLE user – attempts to create primary key index on a field that doesn’t exist: should be:

    PRIMARY KEY (users_id)

    Your current code example has a SQL injection vulnerability on the email field. Also, it’s only validating the email is a string, not an email address.

  2. i am using xammp mysql
    in the above code snippet
    $conn = mysql_connect($host,$user,$password)

    is it necessary to mention the username and password
    is it possible to connect directly using database name coz i dont know whats my username and password

    pls reply

    1. Hi Pratik,

      How about I put it this way. Have you ever connected to your email without a username or password or even your facebook account. It is the same logic. You cannot have a database being administered from anyone, or else it would be chaos. Username and passwords are necessary for security reasons.

      You mention that you have xampp though. Give this a try:

      Normally that would be the standard for any new xampp installation.

      Hope that helps.

  3. hi we are making an Attendance Monitoring System of our school personnel and we want that once the personnel will log in it will automatically record to database the date and time when they log in using their username and password only…please help us we are going to depend it on monday, your help will be highly appreciated..thank you and God bless.

Leave a Reply

Your email address will not be published. Required fields are marked *

The qTranslate Editor has disabled itself because it hasn't been tested with your Wordpress version yet. This is done to prevent Wordpress from malfunctioning. You can reenable it by clicking here (may cause data loss! Use at own risk!). To remove this message permanently, please update qTranslate to the corresponding version.