Database queries

Jojo provides a number of funtions for accessing data in a database. These are documented in detail on the Jojo class documentation, however this guide provides more overview and usage examples.

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

  $products = Jojo::selectQuery("SELECT * FROM {product}");
Note the braces around the table name. Queries will work without braces, but a warning will be recorded in the event log. Braces are required for table prefixing to work, and for consistency of code.
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

  $products = Jojo::selectQuery("SELECT * FROM {product} WHERE productid=?", $productid);
This example will substitute the ? character with the value of $productid. As part of this process, $productid will be escaped to prevent SQL injection, and string values will be enclosed in single quotes automatically.
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

  $products = Jojo::selectQuery("SELECT * FROM {product} WHERE categoryid=? AND active=?", array($catid, $active));
If you need to use multiple data values in a query, use an array containing your data, in the order the ? characters appear in the query.

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

  $products = Jojo::selectQuery("SELECT * FROM {product} WHERE categoryid=? AND active=?", array($catid, $active));
$products will be populated with the data from the query.

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:
  $products = Jojo::selectQuery("SELECT * FROM {product}");
  $numrows = count($products);

It is always worth testing to ensure that the query returned the data you were expecting.
  $products = Jojo::selectQuery("SELECT * FROM {product}");
  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
  $products = Jojo::selectQuery("SELECT * FROM {product}");
  foreach ($products as $product) {
      echo $product['name'].'<br />';
  }

For loop
  $products = Jojo::selectQuery("SELECT * FROM {product}");
  $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:
  $products = Jojo::selectQuery("SELECT * FROM {product}");
  $smarty->assign('products', $products);

Smarty code:
  <ul>
  {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:
  $newproductid = Jojo::insertQuery("INSERT INTO {product} SET name=?, url=?, description=?", array($name, $url, $desc));

The less popular REPLACE syntax can also be used with Jojo::insertQuery.

REPLACE:
  $newproductid = Jojo::insertQuery("REPLACE INTO {product} SET productid=?, name=?, url=?, description=?", array($id, $name, $url, $desc));

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:
  $numrows = Jojo::updateQuery("UPDATE {product} SET name=? WHERE productid=?", array($newname, $id));
  echo $numrows . ' products were updated';

Example: you may not need the $numrows information
Jojo::updateQuery("UPDATE {product} SET name=? WHERE productid=?", array($newname, $id));