This FAQ looks at one way to use a ChronoForm to take product data from a database table and display it in rows with a 'quantity' input box so that it is the first step in a mini shop.
We will assume that the data we need is in a database table called #__cf_product_data and including these columns:
- cf_id: an auto-incremented primary key
- p_id: a unique product identifier
- p_name: the product name
- p_description: a fuller description of the product
- p_price: the product price, a number
- p_status: a short string with specific values like 'out of stock'
In our form the first step is to add two actions to the On Load event in the Setup tab: a DB Read action and an HTML (Render Form) action. The DB Read will get the data from the Table; the HTML (Render Form) action is used by ChronoForms to create the Form HTML from the elements in the Designer tab.
Loading the data
We need to configure the DB Read action:
- Action Label: Get product data
- Enabled: Yes
- Table Name: #__cf_product_data
- Multi Read: Yes
- Enable Model ID: Yes
- Model ID: products
- Fields: cf_id,p_id,p_name,p_description,p_price,p_status
- Order: p_name
- Conditions:
<?php
return array('p_status !=' => 'out of stock');
?>
Displaying the data
Now we go to the Designer tab and add a Container and set it to Type Fieldset - this is just to separate out the form sections.
Drag a Custom Code element from the Advanced elements group into the Container. We will use this to create the HTML to display a product table.
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Price</th>
<th>Quantity</th>
</tr>
</thead>
<tbody>
<?php
foreach ( $form->data['products'] as $p ) {
echo "<tr>
<td>{$p['id']}</td>
<td>{$p['name']}</td>
<td>${$p['price']}</td>
<td><input type='text' name='quantity[{$p['id']}]' id='quantity_{$p['id']} size='4' /></td>
</tr>";
}
?>
</tbody>
</table>
This basic HTML creates a table - you can add more styling and attributes as needed. The block of PHP loops through the product data and creates a table row for each product including an input box for the quantity. The name of this input is linked to the product id so that we can identify it after the form is submitted.
You will then want to add more inputs in a second fieldset to capture the User information: name, email, etc.
After submission
When the form submits the product data is in an array like this:
[quantity] => Array ( [P003] => 3 [P001] => 4 [P004] => )
We will need to re-load the information for the products where there are quantities using a Custom Code action in the form On Submit event after any Captcha and/or Validation actions.
This code builds a $sales array from the items where there is a quantity.
<?php
$sales = array();
foreach ( $form->data['quantity'] as $p => $q ) {
if ( (int) $q > 0 ) {
$sales[$p] = $q;
}
}
// . . . more to follow
Then we can get the products list from this and read the related info from the database table.
// . . . continued
// get the array of product ids
$products = array_keys($sales);
// quote the list of product ids from use in a db query
$product_list = "'".implode("', '", $products)."'";
$db = JFactory::getDBO();
$query = "SELECT `cf_id`, `p_id`, `p_name`, `p_description`, `p_price`, `p_status`
FROM `#__cf_product_data`
WHERE `p_id` IN ({$product_list}) ;
";
$db->setQuery($query);
$product_info = $db->loadAssocList('p_id');
// . . . more to follow
Now we have the quantities and the product information and we need to calculate the cost of each item and the total:
// . . . continued
$total = 0;
foreach ( $products as $p ) {
// get the quantity
$product_info[$p]['p_quantity'] = $sales[$p];
// calculate the price for this quantity
$product_info[$p]['p_value'] = $sales[$p] * $product_info[$p]['p_price'];
// update the total
$total += $product_info[$p]['p_value'];
}
// add the results to the $form->data array
$form->data['sales'] = $product_info;
$form->data['total'] = $total;
?>
Once the data is in the $form->data array we can use it in other actions. Here we'll just produce a list suitable for use in a Thank you page or a confirmation email:
<?php
$total = number_format($form->data['total'], 2);
echo '<p>Your order is:</p>';
foreach ( $form->data['sales'] as $s ) {
$price = number_format($s['p_price'], 2);
$value = number_format($s['p_value'], 2);
echo "<p>{$s['p_quantity']} {$s['p_name']} at {$price} = ${$value}</p>";
}
echo "<p>Total price: ${$total}</p>";
?>
This produces an a list like this:
Your order is: 4 Bananas at 12.05 = $48.20 3 Apples at 17.00 = $51.00 Total price: $99.20
Saving the order data
How you save the order data depends on they way you may want to use it. If you think that you may want to analyse the data by product then you can use two tables; one to save the main order, the second to save a list of product ids, quantities and prices linked back to the order id.
A simpler solution is to save all of the product date to a single column in the main order record by using JSON encoding to save it in a compressed format. This can be done in the calculation loop we had above:
// . . . continued
$total = 0;
$order = array();
foreach ( $products as $p ) {
// get the quantity
$product_info[$p]['p_quantity'] = $sales[$p];
// calculate the value of this quantity
$product_info[$p]['p_value'] = $sales[$p] * $product_info[$p]['p_price'];
// update the total
$total += $product_info[$p]['p_value'];
// new lines here
$order[$p] = array (
'product' => $p,
'quantity' => $sales[$p],
'price' => $product_info[$p]['p_price'],
'value' => $product_info[$p]['p_value']
);
}
// add the results to the $form->data array
$form->data['sales'] = $product_info;
$form->data['total'] = $total;
$form->data['order'] = json_encode($order);
?>
This creates a single 'order' string that we can save. It looks like this
[order] => {
"P003":{"product":"P003","quantity":"3","price":"17","value":51},
"P001":{"product":"P001","quantity":"4","price":"12.05","value":48.2}
}
To save this create a column in the orders table of Type TEXT so that the whole string is saved.
If you need to read this back from the table using json_decode($var, false) will restore the array.
