Mike Heavers

08/08/2015

I am not a backend developer, so I'm constantly struggling with remembering the best way to insert simple data into a database. I need to do this often for prototypes running on my local host, and while I sometimes lean on frameworks such as codeigniter or expression engine to facilitate this, often these frameworks are overblown or time consuming to install, and I just want a simple method of inserting data. So, to remind myself as much as to help others, here's a basic walkthrough. Note that no security is built in to the following example - so if you're running this somewhere publicly, you should protect your database details.

mySQL: Create a database:

In mySQL, click on databases.

Under create database, in the field with the text database name, type a name for your database. I'll call mine mydatabase.

In the ensuing page (or by clicking your database on the list on the left of the screen), at the bottom under create table, provide a table name. I'll call mine mycontent.

Click on the table, and then click the tab called structure.

For the first field, create a column called id, with a data type of int and an extra type of AUTO_INCREMENT. This ensures all your tables are unique.

For the next field, I'm going to create a field with a type of TEXT called content. My database table will simply store content passed along from an HTML form.

Now to the HTML / Javascript:

Frontend: Capture user data

$('form').serialize() 

Assuming you have the following HTML:

<input class="input">Insert content here</input>
<button class="submit">Submit</button>

You could write the following in jQuery:

$('.submit').on('click',function(){
  var content = $('.input').val();
  $.ajax({
            cache: false,
            type: "POST",
            url: "insert.php",
            data: "content=" + content,
            dataType: "text",
            success: function (data) {
                console.log('success',data);
            },
            error: function (xhr, ajaxOptions, thrownError) {
                console.log('error',thrownError);
            }
    });
});

Here, we're capturing the data entered into the input field when we click submit, and creating an ajax request with jQuery.

The type is POST meaning we're posting the data to a PHP script, and the path to that script is simply nsert.php

We need to give that script our data, so here we're just providing a string, with the format: content="[content from input field], and telling jQuery that the format is text(a string). We could, alternately, create a JSON object and pass that along to the script, or, as previously mentioned, just use a form and serialize the data.

Lastly, we're providing two functions for either the success or error of our submission. In this case, they just log the details of the message from the server to our console, telling us whether our submission has succeeded or failed.

Backend: Posting to the database with PHP

PHP provides a few different APIs for interacting with the database (mySQL,mySQLi, PDO...). In this example we'll be using PDO as I find it easier and more flexible.

In it, we'll write the following:

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "mydatabase";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO mycontent (id, content)
    VALUES (:id, :content)");
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':content', $content);

    // insert a row
    $id = "NULL";
    $content = $_POST['content'];
    $stmt->execute();

    echo "New records created successfully";
} catch(PDOException $e){
    echo "Error: " . $e->getMessage();
}

$conn = null;

?>

Here, we first provide a few variables for connecting to the database. We'll need our host, username, password, and database that we're connecting to.

Since I'm using MAMP, my host is by default "localhost" (often web hosts will allow you to use this, but check with your web host for the proper host name", and my username and password are both, by default, root. My database was called, if you remember from the first step, "mydatabase", and the table I created was called "mycontent".

So next I create a variable for the connection called $conn, and I give it all my connection information.

The next line tells PHP what info to provide us if it encounters any errors.

Then I create a variable called $stmtwhich will hold all the data I want to insert into my database. I tell it to insert into mycontent the fields id and content.I map those fields to the variables $id and $content with the bindParam function:

$stmt->bindParam(':id', $id);
$stmt->bindParam(':content', $content);

Now, I can submit all my data. The $id is NULL because we've already set our database to create an auto-incrementing number (0 for the first entry, 1 for the second, etc). The $content variable is mapped to the content javascript variable from our post (remember that in AJAX we created a string where content=[content])

The last thing we do is tell our statement to execute:

$stmt->execute();

If we wanted to submit more data, we'd just create another set of bindings:

$stmt->bindParam(':id', $id);
$stmt->bindParam(':content', 'my new content');

and run

$stmt->execute();