Sort by... Please help

PHP
1

I wonder if somebody could help me please, I’m very new to Symphony and have just started working on a site that’s been built using Silex.

I want to add an ‘order by’ option to database results. I’ve got a page that has lots of information and I want the user to be able to choose the order it’s sorted by, for example if they click on name it sorts the results via name, if they click email address is sorts by that etc.

I’ve spent a few weeks trying to get this to work but haven’t got anywhere so would be grateful for any help. In the query file (php) there’s lots of ‘filterBy’ functions. filterByEmail, filterByName for example, but I don’t know if this is right or if it is how to call it from within the twig file.

Thanks in advance for any help.

2

What are you using to get database results? Are you using a model or a simple query? In any case, you can execute a simple query using filters and ordering like this:

<?php

$filters = isset($_POST['filters']) && is_array($_POST['filters']) 
	? array_filter($_POST['filters'])
	: array();
$order_by = isset($_POST['order_by']) && is_array($_POST['order_by']) 
	? array_filter($_POST['order_by'])
	: array();

$where = array();
$binds = array();
$ordering = array();

/*
 * A switch is used to prevent filtering on non existing columns.
 */
foreach($filters as $filter => $filter_value) {
	switch($filter) {
		case 'id':
			$where[] = $filter.' LIKE :'.$filter;
			$binds[':'.$filter] = $filter_value . '%';
			break;
		case 'username':
		case 'email':
		case 'firstname':
		case 'lastname':
			$where[] = sprintf('%s LIKE :%1$s', $filter);
			$binds[':'.$filter] = '%'.$filter_value.'%';
			break;
	}
}

foreach($order_by as $order_column => $direction) {
	switch($filter) {
		case 'username':
		case 'email':
		case 'firstname':
		case 'lastname':
			$ordering[] = sprintf('%s %s', $order_column, strtoupper($direction) == 'DESC' ? 'DESC' : 'ASC');
			break;
}

$sql = 'SELECT * FROM user ';
if($where) {
	$sql .= 'WHERE '.(implode(', ', $where));
}
if($ordering) {
	$sql .= 'ODER BY '. implode(', ', $ordering);
}

// prepared statement, to prevent SQL injection. Use PDO.
$stmt = $db->prepare($sql);
$stmt->execute($binds);

// you can use fetchall
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

// or foreach
foreach($stmt as $record) {
	// do something...
}
3