Originally I've been trying to do this with MySQL but it's proved pretty impossible to get the desired effect so far.
So I'm going to have to rely on trusty PHP to sort this out (here was the previous thread viewtopic.php?f=2&t=100077).
Here goes...
Basically I've got a database full of news items which are output on a page ordered by a Unix timestamp.
But I want to be able to override the position in the results by having a "position" field, so entering 2 in that field makes it position 2 in the results.
This should be pretty easy to do with PHP... until I started getting into a horrible tangled web of multidimensional arrays and looping within a loop etc
Here's my array of test data which simulates my (at the moment) very simple DB structure:
Code: Select all
//array key is the actual id from DB
$array[1]['headline'] = 'id1';
$array[1]['pos'] = '';
$array[2]['headline'] = 'id2 but shd be 3rd in results';
$array[2]['pos'] = 3;
$array[3]['headline'] = 'id3';
$array[3]['pos'] = '';
$array[4]['headline'] = 'id4';
$array[4]['pos'] = '';
$array[5]['headline'] = 'id5';
$array[5]['pos'] = '';
$array[6]['headline'] = 'id6';
$array[6]['pos'] = '';
$array[7]['headline'] = 'id7 but shd be 1st!';
$array[7]['pos'] = 1;
$array[8]['headline'] = 'id8';
$array[8]['pos'] = '';
$array[9]['headline'] = 'id9';
$array[9]['pos'] = '';
$array[10]['headline'] = 'id10 but shd be 2nd record';
$array[10]['pos'] = 2;Then loop through array 1st array again with a counter and check each time if the value of counter equals a key in the pos array.
But this caused me issues where 2 or 3 different records had the same value of pos. Because the key wasn't unique, I was just overwriting that element in the pos array.
Also it didn't quite create the desired order, here was my code that works with the above array:
Code: Select all
// LOOP THROUGH NEWS ITEMS, MOVING POS ELEMENTS TO news_pos ARRAY
foreach ($array as $key => $val) {
$id = $key;
$headline = $val['headline'];
$pos = $val['pos'];
if ($pos > 0) {
$news_pos["$pos"]['id'] = $id;
$news_pos["$pos"]['headline'] = $val['headline'];
$news_pos["$pos"]['pos'] = $val['pos'];
unset($array[$key]);
}
}
$count = 1;
foreach ($array as $key => $val) {
$id = $key;
$headline = $val['headline'];
if ( array_key_exists($count, $news_pos)) {
$news_pos_id = $news_pos[$count]['id'];
$news[$news_pos_id]['headline'] = $news_pos[$count]['headline'];
}
$news[$id]['headline'] = $headline;
$count++;
}
echo '<pre>';
print_r($news);
echo '</pre>';Can anyone think of a better and more accurate way to do what I'm trying to do here?
Or does noone actually understand what I'm trying to do
Cheers, B