Database overview
Database abstraction layer
All database queries should be done using the Jojo database functions. While the PHP mysql_query function will work, you will miss out on some of the benefits provided by the abstraction layer.These are:
* Automatic escaping of data
* Prefixing of table names, so multiple installs can be run from one database
* The potential to run Jojo on databases other than MySQL in the future
* A consistent way of handling database queries between authors
* Less code required to perform a query
Database connection
A database connection is initiated automatically the first time a query is executed. In the case of Javascript, CSS and image files, this means that the connection will not be made if it is not needed, which saves the connection overhead.Multiple database connections
Jojo does not currently have a built-in way of connecting to more than one database. A workaround can be done by using the PHP MySQL functions instead of the Jojo abstraction layer.Query syntax
Throughout these examples, we will use a product table. The following concepts apply to all kinds of queries.Simple example
When braces are used around table names, the `backticks` are added automatically - please do not enclose table names within `backticks`.
As a convention, we use uppercase to differentiate SQL code (SELECT, INSERT, UPDATE, FROM, ORDER BY, GROUP BY, DISTINCT, DESC, AS, AND, OR etc) from data, and to make the queries more readable.
Example with data
To further clarify this point: there is no need to enclose ? characters in single quotes, and doing so will give unexpected results.
Example with several data values
SELECT queries
The Jojo::selectQuery() function is used for getting data from a database. SelectQuery will return a multi-dimensional array of data, which you would normally loop through, or assign to Smarty for use in templates.SELECT example
Counting returned rows
To get a count of the number of rows returned, use the PHP count() function on the output of the selectQuery function.Counting rows:
$numrows = count($products);
It is always worth testing to ensure that the query returned the data you were expecting.
if (!count($products)) {
$error = 'No products were found';
}
Looping through data in PHP
For displaying data to the user, looping is often best done in the Smarty template. However, loops in PHP are often useful as well.Foreach loop
foreach ($products as $product) {
echo $product['name'].'<br />';
}
For loop
$n = count($products);
for ($i=0; $i<$n; $i++) {
echo $products[$i]['name'].'<br />';
}
Foreach loops are easier to read, and require less code. For loops can be more useful for manipulating the query data.
Looping through data in Smarty
It is very easy to perform a database query and send the data directly to Smarty for outputting.This simple example will produce an unordered list of products, with clickable links.
PHP code:
$smarty->assign('products', $products);
Smarty code:
{section name=i loop=$products}
<li><a href="products/{$products.url}/">{$products.name}</a></li>
{/section}
</ul>
Please see the Smarty documentation for more information on a section loop.
INSERT queries
The Jojo::insertQuery() function inserts a new record into the database, and returns the ID that was assigned to the record.Example:
The less popular REPLACE syntax can also be used with Jojo::insertQuery.
REPLACE:
REPLACE differs from INSERT in that it will replace any existing records that have the same primary key. The MySQL manual has more details on REPLACE syntax.
UPDATE queries
Use Jojo::updateQuery for editing and updating existing rows. Jojo::updateQuery() returns the number of rows that were changed.Example:
echo $numrows . ' products were updated';
Example: you may not need the $numrows information
Jojo::updateQuery("UPDATE {product} SET name=? WHERE productid=?", array($newname, $id));