Change position in array, messing about with DB result order

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Change position in array, messing about with DB result order

Post by batfastad »

Hi everyone

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 :oops:

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;
In principle I thought this should be pretty easy... loop through the array and move all array elements where pos > 0 into a fresh array with value of pos as key.
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>';
And you'll see what the problem is from the output of the array.

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 :lol:

Cheers, B
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Change position in array, messing about with DB result order

Post by mattpointblank »

Can't you do this in your SQL? ORDER BY the sorting field then by ID?
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Change position in array, messing about with DB result order

Post by batfastad »

That doesn't do what I'm looking for. I have tried many different ways in SQL queries (COALESCE can be persuaded to almost give the desired output) and am pretty sure this can't be done in MySQL 8O

I should have mentioned, the value of position is optional and will be NULL in most cases. So if there's only 1 record with a value entered for position eg: position=8, then using that simple ORDER BY that record would appear 1st in the list. Not 8th in the list as required :?
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Change position in array, messing about with DB result order

Post by mattpointblank »

Maybe when you let the user choose the sequence, number the other rows accordingly then instead, rather than looping through complex arrays each time? Say the user chooses a certain item as 8, then go through the other rows and number them in order, dropping the selected row in at the 8th position and numbering them all? That kinda structure would require less processing through php just to show your news stories.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Change position in array, messing about with DB result order

Post by batfastad »

Yeah I agree, however there will eventually be 10000+ news stories all ticked into different criteria, so it won't be possible for the user to manually re-order each one each time.
Also there will be multiple "position" fields for different websites. So it should act as a sort of override system.

I will think further on that though, and see if I can work out a way to automatically set the position value for records where its not been specified manually, to get the desired effect.
Doing it in DB would be much quicker than looping through nested arrays each time a user visits!
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Change position in array, messing about with DB result order

Post by mattpointblank »

Yeah, this is definitely the sort of operation you should try to do when adding content, not viewing it, since the ratio will be much higher (in terms of adding:viewing) - it's still worth it even if you have to update 10,000 rows each time, in my mind, rather than merge complex arrays every pageview. Maybe have a second table just to define sorting orders and join your content to it?
Post Reply