Database Queries

You can perform queries on a database using SQL statements you directly build yourself as a string. You can also use the database query classes to build queries.

Query Strings

Prepare your own query SQL statement as a string and pass it to any of the database methods that accept a query.

$sql = "SELECT * FROM colors WHERE id=5;";
$row = $core->db->return_one_row_assoc($sql);

Array
(
    [id] => 5
    [deleted] => 0
    [name] => Orange
)

Below is an example of creating a SQL statement string that joins multiple tables. Note that the result will be a single dimensional result row.

$sql = "SELECT cars.*, colors.name AS color_name FROM cars LEFT JOIN colors ON cars.colors_id=colors.id WHERE cars.id=2;";
$row = $core->db->return_one_row_assoc($sql);

Array
(
    [id] => 2
    [name] => Cool
    [colors_id] => 7
    [makes_id] => 1
    [color_name] => Black
)

Query Objects

Query Objects can sometimes require more code to implement the same query. However, by generating a query using a query object the core framework can understand your query and add many additional features automatically.

$query = $core->db->new_select_query('cars');
$query->add_where("id=5");
$row = $core->db->return_one_row_assoc($query);

Array
(
    [id] => 5
    [deleted] => 0
    [name] => Orange
)

Below is an example of using a query object to create a query that joins multiple tables. The advantage in using the query object is that because we have defined foreign keys for the database in the configuration options the resulting row will be multi-dimensional with joined tables being represented by sub-records.

$query = $core->db->new_select_query(array('cars', 'colors'));
$query->add_where("cars.id=2");
$row = $core->db->return_one_row_assoc($query);

Array
(
    [id] => 2
    [name] => Cool
    [makes_id] => 1
    [color] => Array
        (
            [id] => 7
            [deleted] => 0
            [name] => Black
        )

)