You are currently browsing Crystal’s documentation for the 0.4 version - Switch to version: 0.3

Retrieving data

The following functions allow you to retrieve data from the database

Basic query partials

get(table{string})

Use this function if you don't need to specify columns in your query.

$db->get('table')
// Produces SELECT * FROM `table`

select(columns{array}, {string} )

Use this function if you need to retrieve specific columns from the database. Using select() instead of get() gives you the option to add an alias name for every column

$db->select('products, clients')->from('table')
// Produces SELECT `products`, `clients` FROM `table` - Available in Crystal 0.3

$db->select('very_long_column_name :as column, products')->from('table')
// Produces SELECT `very_long_column_name` AS `column, `products` FROM `table` -- Available in Crystal 0.3


$db->select(array('products','clients')->from('table')
// Produces SELECT `products`, `clients` FROM `table`

// You can use string if you need single column
$db->select('products')->from('table')
// Produces SELECT `products` FROM `table`

// OR if you need complex columns selection, you can skip Crystal's default parameter filtering
$db->select('products, MAX(price), MIN(payment)', FALSE)
// Produces SELECT products, MAX(price), MIN(payment)

from(table{string})

$db->select(array('products','clients')->from('table')
// Produces SELECT `products`, `clients` FROM `table`

where(params {array},{string, string})

$db->where('product_id : 1, client_id : 5')
// Available in Crystal 0.3

$db->where(array('product_id' => '1', 'client_id' => '5')
// Produces WHERE `product_id` = '1' AND `client_id` = '5'


$db->where('product_id','2')
// Produces WHERE `product_id`='2'


// Available in Crysta 0.4.1 
// Doesn't escape the values in where
$db->where('created > "2010-08-10" AND created <= "2010-08-17"', false)
// Produces WHERE created > "2010-08-10" AND created <= "2010-08-17"

and(params {string, string})

$db->and('id','2')
// Produces AND `id`='2'

order_by(params {array}, {string, string})

$db->order_by('product_id, -category_id')
// Products ORDER BY `product_id` DESC, `category_id` ASC -- Availabale in Crystal 0.3

$db->order_by(array('product_id' => 'ASC', 'client_id' => 'DESC')
// Produces ORDER BY  `product_id` ASC, `client_id` DESC


// Or you can pass single parameter, like this: 
$db->orderby('product_id', 'ASC');
// Produces ORDER BY `product_i` ASC

group_by(params {array}, {string, string})

$db->group_by('product_id, client_id')
// Produces GROUP BY `product_id`, `client_id` -- Available in Crystal 0.3

$db->group_by(array('product_id','client_id'));
// Produces GROUP BY `product_id`, `client_id`


$db->group_by('product_id');
// Produces GROUP BY `product_id`

having(params {string}, {string, string})

$db->having('price > 100');
// Produces HAVING price > 100


$db->having('product_id', '45');
// Produces HAVING `product_id` = '45'

limit(offset{integer}, limit{integer})

$db->limit('0', '5')
// Produces LIMIT 0,5 

Raw SQL

The sql() function works only as query holder, you must add the function execute(), if you need to execute the query. When SELECT type queries are run, you can use fetch functions to show your results. When you run INSERT, UPDATE queries, it simply returns TRUE or FALSE depending on success or failure.

sql(query{string}, params{array, optional});

$db->sql('SELECT * FROM `table`')->fetch_all();
// Produces SELECT * FROM `table`


// OR YOU CAN USE sql() only for a complex part of your query
$db->get('posts')->where('post_status', 'published')->sql('AND post_date >= "2010-01-10" AND post_date <= "2010-06-10"')
// Products SELECT * FROM `posts` WHERE `posts_status`= "published" AND post_date >= "2010-01-10" AND post_date <= "2010-06-10"

Joining tables

join(table{string}, columns{array});

$db->join('table','comment.id = post.id')
// Produces JOIN `table` ON comment.id = post.id

If you need something other than a natural JOIN you can specify it via the functions: left_join(), right_join(), outer_join() and inner_join().

$db->left_join('table','comment.id = post.id')
// Produces LEFT JOIN `table` ON comment.id = post.id

$db->right_join('table','comment.id = post.id')
// Produces RIGHT JOIN `table` ON comment.id = post.id

$db->outer_join('table','comment.id = post.id')
// Produces OUTER JOIN `table` ON comment.id = post.id

$db->inner_join('table','comment.id = post.id')
// Produces INNER JOIN `table` ON comment.id = post.id

Generating results

fetch_all();

$result = $db->get('clients')->fetch_all();
/** $result returns 
Array
(
    [0] => Array
        (
            [id] => 1
            [age] => 25
            [client_name] => John
        )

    [1] => Array
        (
            [id] => 2
            [age] => 23
            [client_name] => Adam
        )
*/

fetch_row()

$result = $db->get('clients')->where('id','1')->fetch_row();
/** $result returns 
Array
(
            [id] => 1
            [age] => 25
            [client_name] => John
)

fetch_element(params {string})

You can use fetch_element for returning single string values

//clients table
| id | age | client_name |
| 1  | 25  | John        |
| 2  | 23  | Adam        |
$db->select('clients')->columns('age')->where('id','1')->fetch_element('age')
// return 25

fetch_object()

$result = $db->get('clients')->fetch_object();
/** returns single row
 stdClass Object
(
            [id] => 1
            [age] => 25
            [client_name] => John
)
**/
// You can access the data in OO way using
echo $result->age;
// return 25

Additional methods

print_as_table()

Your database server always returns the result in some kind of array. Arrays are very difficult to scan in a web browser and usually you must right click and view source if you want to see them formatted. In Crystal you can just write print_as_table and you will get all your data nicely formatted in HTML table:

Aggregate functions

Crystal has a built-in layer covering some of the basic aggregate SQL functions.

minimum(column{string});

$db->minimum('age')->from('clients')
// Produces SELECT MIN(age) AS age FROM `clients`


//clients table
| id | age | client_name |
| 1  | 25  | John        |
| 2  | 23  | Adam        |

 $db->minimum('age')->from('clients')->fetch_element('age')
// returns 23

maximum(column{string});

$db-> maximum('age')->from('clients')
// Produces SELECT MAX(age) AS age FROM `clients`


//clients table
| id | age | client_name |
| 1  | 25  | John        |
| 2  | 23  | Adam        |

 $db->maximum('age')->from('clients')->fetch_element('age')
// returns 25

average(column{string});

 $db-> average('age')->from('clients')
// Produces Produces SELECT AVG(age) AS age FROM `clients` 

//clients table
| id | age | client_name |
| 1  | 25  | John        |
| 2  | 23  | Adam        |

 $db->average('age')->from('clients')->fetch_element('age')
// returns 24

summary(column{string});

 $db->summary('age')->from('clients')
// Produces SELECT SUM(age) AS age FROM `clients` 

//clients table
| id | age | client_name |
| 1  | 25  | John        |
| 2  | 23  | Adam        |

 $db->summary('age')->from('clients')->fetch_element('age')
// returns 48