1

Some weeks ago I posted this and I realized from the referrals that it may help to be as a code snippet. This is an example of getting products details from categories – subcategories using ajax call. I am fond of OOP and this is not , but I believe that will help others understand of how this things can happen.

Demand: There are some sound systems of cars (products) each of them has an article link and some other details (e.g. Voltage) ,those products can be categorized , so we have categories of them. The categories can have either subcategories or are a category that has products. We want to display all the categories that have products in a select, and when a user selects one of them to see all the products of it with an AJAX call.

We have two tables, categories and products, here are the create table of them

CREATE TABLE `categories` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`PARENT_ID` int(10) NOT NULL,
`TYPE` smallint(1) NOT NULL COMMENT '0 Category with subcategories | 1 Category with products',
`TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`STATUS` smallint(1) NOT NULL DEFAULT '1' COMMENT '0 Inactive | 1 Active',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM

When a gategory has parent id 0 than it is a top category

CREATE TABLE `products` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`CATEGORY_ID` int(10) NOT NULL,
`ARTICLE_LINK` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`PRODUCT_LINE` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`VOLTAGE` int(4) NOT NULL,
`BATTERY` int(4) NOT NULL,
`ELECTRIC_POWER` int(4) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM

An OOP MVC implementation of that is here http://phpwebframework.com/an-elementary-example-with-categories-subcategories-products-and-ajax using PWF

<?php
    session_start();
    // Change these with your own database,user and password
    $database = "test_cars";
    $dbUser = "testcarsuser";
    $dbPassword = "testcarspassword";
    $db = new PDO("mysql:dbname=".$database.";host=localhost",$dbUser,$dbPassword);
    //There is no reason to get categories each time, so we will use visit scope caching through session
    //(although if we were using a framework this could be done by application scope caching)
    if(isset($_SESSION["CATSARRAY"]) && isset($_SESSION["CATSINPUT"]))
    {
    $catsArray = $_SESSION["CATSARRAY"];
    $catsInput= $_SESSION["CATSINPUT"];
    }
    else
    {
    // $catsArray We will create an array with the categories rows. It's first key will be the id of the category
    // second key will be the column name of each row
    // $prodCatsIds We will create also an array with all the ids of the categories that have products
    $catsArray = array();
    $catsInput = "";
    $prodCatsIds = array();
    $statement = $db->prepare("SELECT * FROM categories WHERE STATUS = 1");
    $statement->execute(array());
    $r = $statement->fetchAll(PDO::FETCH_ASSOC);
    // Now $r will be an array with the first key being the number of the row returned and the second the
    // name of the column
    if(count($r) > 0)
    {
    foreach($r as $row)
    {
    $catsArray[$row["ID"]] = $row;
    // If it is a products category we will keep it's id in $prodCatsIds to use it later
    if($row["TYPE"] == 1)
    {
    $prodCatsIds[] = $row["ID"];
    }
    }
    }
    $_SESSION["CATSARRAY"] = $catsArray;
    // Using $prodCatsIds we will create an array whose key will be the path to the product category
    // (from parent to child) and the value will be the id of that category
    if(count($prodCatsIds) > 0)
    {
    foreach($prodCatsIds as $id)
    {
    $selectArray[getCatPath($id)] = $id;
    }
    // Let's short the selectArray by keys (path's names)
    ksort($selectArray);
    }
    //It's time to create our select input for the form
    if(count($selectArray) > 0)
    {
    $catsInput = "<select name='category' id='category' onchange='javascript:categorySelected()'>";
    $catsInput .= "<option value='none'>Select Category</option>";
    foreach($selectArray as $value => $key)
    {
    $catsInput .= "<option value='$key'>$value</option>";
    }
    $catsInput .= "</select>";
    }
    $_SESSION["CATSINPUT"] = $catsInput;
    }
    // A function that will work recursively to generate the 'path' to the category
    function getCatPath($id)
    {
    $re = "";
    global $catsArray;
    $row = $catsArray[$id];
    if($row["PARENT_ID"] != 0)
    {
    $re .= getCatPath($row["PARENT_ID"])." &raquo; ";
    }
    $re .= $row["TITLE"];
    return $re;
    }
    // We will use the same scipt to post by AJAX the category id and retrieve the table with products
    // Of course could be better to return an array with data and not the html table but that would
    // make this example more complicated. Any security check that would validate that it is a post
    // through AJAX call and that the post came out of the same script is needed.
    if(isset($_POST["categoryId"]))
    {
    $id = $_POST["categoryId"];
    $statement = $db->prepare("SELECT * FROM products WHERE CATEGORY_ID = ?");
    $statement->execute(array($id));
    $r = $statement->fetchAll(PDO::FETCH_ASSOC);
    $re = getCatPath($id) ." Products<br/>";
    if(count($r) == 0)
    {
    $re .= "There aren't any product yet in that category";
    }
    else
    {
    $re .= "<table><tr><th>Article Link</th><th>Product Line</th><th>Voltage</th><th>Battery</th><th>Electric Power</th></tr>";
    foreach ($r as $row)
    {
    $re .= "<tr><td><a href='".$row["ARTICLE_LINK"]."' target='_blank'>".$row["ARTICLE_LINK"]."</a></td>
    <td>".$row["PRODUCT_LINE"]."</td><td>".$row["VOLTAGE"]."</td><td>".$row["BATTERY"]."</td>
    <td>".$row["ELECTRIC_POWER"]."</td></tr>";
    }
    $re .= "</table>";
    }
    $result = array("HTML" => $re);
    header("Content-Type: application/json; charset=utf-8");
    echo json_encode($result);
    exit;
    }
    // LETS MOVE TO THE NORMAL PAGE OUTPUT
    header('Content-Type: text/html; charset=utf-8');
    ?>
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
    <title>Example</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <script type="text/javascript">
    // This will happen every time the user change the selected category
    function categorySelected()
    {
    catsEl = document.getElementById("category");
    id = catsEl.options[catsEl.selectedIndex].value;
    if(id != "none")
    {
    document.getElementById("result").innerHTML = "loading...";
    params = "categoryId="+id;
    http = window.XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject("Microsoft.XMLHTTP");
    http.open("POST", document.URL, true);
    http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    http.setRequestHeader("X-Requested-With","XMLHttpRequest");
    http.setRequestHeader("Content-length", params.length);
    http.setRequestHeader("Connection", "close");
    http.onreadystatechange = function()
    {
    if(http.readyState == 4)
    {
    if(http.status == 200)
    {
    response = http.responseText;
    r = eval("(" + response + ")");
    document.getElementById("result").innerHTML = r["HTML"];
    }
    else
    {
    document.getElementById("result").innerHTML = "Sorry this service is currently unavailable";
    }
    }
    }
    http.send(params);
    }
    }
    </script>
    </head>
    <body>
    <?=$catsInput?>
    <br/>
    <br/>
    <br/>
    <div id="result"></div>
    </body>
    </html>
4
Contributors
3
Replies
120
Views
5 Years
Discussion Span
Last Post by pritaeas
0

Thank you for this! Do you by any chance have a sample code for repopulating the content of a select box depending on the selected value from the previous select box? The values that will fill the second select box is from a database.. thank you :)

0

Do you by any chance have a sample code for repopulating the content of a select box depending on the selected value from the previous select box?

That's exactly what the code above does.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.