How to store a product in a mysql database and load it from the same database?

In this article I am going to explain how you can store a customized product in a mysql database and load it from the same database.

Step 1:

First of all create a mysql structure like that:

Step 2:

You also need a PHP script which is storing and loading a product in the database. I will send an action via POST to this script, that tells the script what kind of action (store or load) should be executed.

product_in_db.php  

<?php
if(!isset($_POST['action']))
    die("No action set!");

$db_host = 'localhost'; //database host
$db_user = 'root';//database username
$db_pwd = 'root';//database password
$db_database = 'fpd'; //database name

//connect to database
$connection = mysql_connect($db_host, $db_user, $db_pwd) or die('Connection failed to mysql database. Check the database host, username and password.');
//select database
mysql_select_db($db_database) or die("Database could not be selected.");

//store product
if($_POST['action'] == 'store') {
    //get product views
    $views = $_POST['views'];
    //insert views in database table
    $sql = "INSERT INTO products (views) VALUES('$views')";
    $result = mysql_query($sql) or die('Product could not be stored.');

    if($result) {
        //get ID of insert product and echo it as json
        $id = mysql_insert_id();
        header('Content-Type: application/json');
        echo json_encode($id);
    }
}
//load product
else if($_POST['action'] == 'load') {
    //get product ID
    $id = $_POST['id'];
    //select product by ID
    $sql = "SELECT views FROM products WHERE ID=$id";
    $query = mysql_query($sql) or die('Product could not be selected.');
    //get the product views
    $result = mysql_result($query,0,"views");
    //echo result as json
    header('Content-Type: application/json');
    echo json_encode(stripslashes($result));
}

mysql_close();

?>

Step 3:

I am using AJAX to call the action handlers for storing or loading a product. I also create 2 buttons for that action and give them unique ID’s to use them in jQuery.

My buttons

scripts.js

//click handler for #store-product-db
$('#store-product-db').click(function() {
    //Send data (action and product views) to PHP script
    $.post("php/product_in_db.php", { action: 'store', views: JSON.stringify(productDesigner.getProduct()) }, function(data) {
        //check for errors
        if(parseInt(data) > 0) {
            //successfully added
            alert('Product with ID '+data+' stored!');
        }
        else {
            alert('Error: '+data+'');
        }
    });
});

//click handler for #load-product-db
$('#load-product-db').click(function() {
    //Send data (action and id) to PHP script, in this case I am loading a product with ID 17
    $.post("php/product_in_db.php", { action: 'load', id: 17 }, function(data) {
        productDesigner.loadProduct(JSON.parse(data));
    });
});

So whats happening in this Javascript snippet?
It contains 2 click handlers, the first is storing a new product in the database. I am using the getProductDataURL() method to get the data url of the product and stringify it with JSON.stringify() to create a database-friendly string . This product string together with the action „store“ will be sent to the product_in_db.php script, that contains the PHP script we created in the second step.
The other click handler is loading a product from the database by an ID. In my example I just set that ID manually in the code. In the callback function of the post call, you have access to product (views) which is still stringified. You transform it back into an Javascript array with JSON.parse(). Now you have a product array for the product designer, which can be added to the designer via the loadProduct() method. You can also use the addProduct() method to add it only to the products sidebar.