Sorting mysql results via arrays

PHP
1

One of my biggest goals is to minimize queries on a page. Good programming practice, right?

I’ve got the results of a query going into arrays like so:

$count = 0;
while ($z = $result->fetch()) {
	$storyid[$count] = $z["storyid"];
	$story[$count] = $z["story"];
	$subject[$count] = $z["subject"];
	$date_submitted[$count] = $z["date_submitted"];
	$likes[$count] = $z["likes"];
	$dislikes[$count] = $z["dislikes"];
	$count++;
}

What would be the best way to sort this data via the date_submitted variable, then in another instance on the page by “likes,” and another instance by “dislikes?” I don’t want to sort the data through the query to save having to do the query 3 times on the page.

The variables MUST maintain their indexes too so that the subject matches the story, and so forth.

Thanks!

2

usort() can sort anything

3

Yes. I believe you.

Problem is, when I sort it, the other values of the other arrays are thrown off. So a subject won’t match it’s story, for instance.

4

why do you have all those arrays? why not just fetchAll() as arrays?

5

This is due to the improper use of the arrays. The data in each array is tightly related to each other.

I would suggest first using MySQL to sort the data before it’s fetched. That is something that databases are more effective at doing, than other available techniques.

If the data is then retrieved as an associated array, you can use custom sort methods to rearrange the data in a variety of acceptable manners.

You can pass to usort the name of a function that will specify how the ordering is to be decided. It won’t work on multiple arrays though, the data must be contained within the one (potentially associated) array.

6

Is there a Fetchall function for mysql? I’m not seeing it in the online documentation…

7

What would be an effective way to put the arrays generated from the mysql results into one associative array, then sorting from there?

If I can’t find a way I’ll do the 3 queries on one page… but I’d rather not. Good to plan ahead in case my little project here becomes massive and I’m already optimized for the server load. :slight_smile:

8 

$rows = array();
while ($row = $result->fetchAssoc()) { 
    array_push($rows, $row);
}
$count = count($rows);

$rows will be a numerically indexed array, each item will be an associated array.


array (
    [0] => array(
        'storyid' => ...,
        'subject' => ...,
        'date_submitted' => ...,
        'likes' => ...,
        'dislikes' => ...
    ),
    [1] => array(
        'storyid' => ...,
        'subject' => ...,
        'date_submitted' => ...,
        'likes' => ...,
        'dislikes' => ...
    ),
    ...
)

Then you can use usort, with the following type of function for the sorting


function likes($a, $b)
{
    return strcmp($a['likes'], $b['likes']);
}
$likes = usort($rows, 'likes');
9

Perfect, think I got my head wrapped around this. Thanks!

10

If you would like to sort by more than 1 column here is a function that uses array_multisort, and does not require you to write too much code, in just 1 line:

Example:


$arr2 = array_msort($arr1, array('name'=>array(SORT_ASC,SORT_REGULAR), 'cat'=>SORT_DESC));

Source:


function array_msort($array, $cols)
{
	$colarr = array();
	foreach ($cols as $col => $order) {
		$colarr[$col] = array();
		foreach ($array as $k => $row) {
			$colarr[$col]['_'.$k] = strtolower($row[$col]);
		}
	}
	$params = array();
	foreach ($cols as $col => $order) {
		$params[] =& $colarr[$col];
		$cols[$col] = (array) $cols[$col];
		foreach ($cols[$col] as $k => $ordval) {
			$params[] =& $cols[$col][$k];
		}
	}
	call_user_func_array('array_multisort', $params);
	$ret = array();
	$keys = array();
	$first = true;
	foreach ($colarr as $col => $arr) {
		foreach ($arr as $k => $v) {
			if ($first) {
				$keys[$k] = substr($k, 1);
			}
			$k = $keys[$k];
			if (!isset($ret[$k])) {
				$ret[$k] = $array[$k];
			}
			$ret[$k][$col] = $array[$k][$col];
		}
		$first = false;
	}
	return $ret;
}