Capture the value of previous or next iteration in a loop. Possible?

[BuddyH]

Hi,
I am trying to make a function in my db driven site where I can move articles up & down on a page. Here's my article table..

sort_id | page_id | title | text

This way I can auto increment the sort_id (not a primary key) so every new article gets a new number. I did look at ways that would +1 or -1 from the sort_id, but since the prev or next article may be any number higher or lower, the only way I can be sure they will swap place is if they physically swap sort_id.

So I have a foreach loop going something like this..

foreach(articles as article){
          echo $title.' <a href=move.php?sort_id='.$article['sort_id'].'&direction=up>Move up</a> |<a href=move.php?sort_id='.$article['sort_id'].'&direction=down>Move down</a>';
} 

So now if I click move up, how can I capture the value of the previous iteration so that I can add it to the query string to make the swap happen. Or am I just barking up the wrong tree here?

[wonshikee]

One way is to use a for loop instead of a foreach loop, assuming $articles is stored with a sequential integer index.

[BuddyH]

Hmm ok I haven't used a for loop to output content before (still learning).
The articles sort_id column will be sequential yes, but numbers will almost certainly jump due to articles being deleted. Each new entry will be incremented, but if I delete article #1 & #3 for example, my artilces table will be 0,2,4,5,6,7...

However, when I output them, they will be displayed using WHERE page_id =(whichever page). They will still display in numerical order, but the numbers could by now be completely random which is why I need to capture the previous & next iteration's value so that I can tell mysql to update the sort_id for both articles.

Thanks a lot for taking a look
Hope I'm making sense, here's what I have so far. I tried to output using prev & next, but it's not working. I don't really understand these functions properly I have to admit. I just looked them up on php.net & tried this out.

// Turn a result into an assoc array
function result_to_assoc($result)
    {
      $result_array = array();
        for ($i=0; $row = mysql_fetch_assoc($result) ; $i++)
        {
           $result_array[$i] = $row; 
        }
        
        return $result_array;
    }

// output the articles
function show_articles()
    {
        $connection = db_connect();            
        $query = ("select * from article_test"); 
            
            $result = mysql_query($query);                
            $number_of_posts = mysql_num_rows($result);
            if ($number_of_posts == 0) 
            {
              return false;    
            }            
            $row = result_to_assoc($result);            
            return $row;            
    }
$articles = show_articles();
//print_r($articles);

foreach($articles as $article){
          $prev = prev($article);
          $next = next($article);
          echo '<p>'.$article['title'].' <a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&direction=up>Move up</a> |<a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&direction=down>Move down</a></p><p>'.$article['text'].'</p><h1>'.$prev['sort_id'].' - '.$next['sort_id'].'</h1><hr />';
}
//'&prev='.prev($article['sort_id']).
//'&next='.next($article['sort_id']). 

[wonshikee]

You can use this:

foreach ($articles as $key=>$article) { .... $prev = $article[$key-1]; $next = $article[$key+1]; }

[BuddyH]

Thanks for that. It looks like it makes sense to me, but alas I tried it and it outputs nothing.

foreach($articles as $key => $article){
          $prev = $article[$key-1]; $next = $article[$key+1];
          echo '<p>'.$article['title'].' <a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&direction=up>Move up</a> |<a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&direction=down>Move down</a></p><p>'.$article['text'].'</p><h1>'.$prev.' - '.$next.'</h1><hr />';
          
          //print_r($prev);
          //print_r($next);
} 

The $prev & $next variables are empty.

[StarLion]

because $article[$key-1] doesnt make any sense. wonshikee meant to just use $articles[$key-1] and $articles[$key+1].And then i assume you want the title element, so $articles[$key-1]['title'] ....

[BuddyH]

Whoooaaaaaa!!! I freakin love this place! You guys are my new best friends!

Oh... that works exactly how I want it to by the way..

I was managing to get an array after trying a few things, but I haven't used $arr[$key]['value'] before. Another new feather, thank you so much!!

I have this now, so its onto styling and writing the query to update sql and finish my site.

//print_r($articles);

foreach($articles as $key => $article){
          $prev = $articles[$key-1]['sort_id']; $next = $articles[$key+1]['sort_id'];
          echo '<p>'.$article['title'].' <a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&prev='.$prev.'&direction=up>Move up</a> | <a href=_test-show-articles.php?sort_id='.$article['sort_id'].'&next='.$next.'&direction=down>Move down</a></p><p>'.$article['text'].'</p><hr />';
} 

[wonshikee]

Oops yeah that "s" is required lol, was typing that during load screen while playing League of Legends =D

Just to go a little deeper, the code will throw a notice because $key-1 and $key+1 will go out of bounds, not a real issue for PHP but it is a run-time error for strictly typed languages, just something to keep in mind.

[BuddyH]

Ah you mean $prev on the 1st move up and $next on the last move down? I'm thinking I can just test to see if they have a value and if not, no move up/down link.
Thanks a million for your help here, happily coding away at my pages now

[BuddyH]

Actually it's not throwing a notice, its just that the query string on the first iteration has no value for prev and the last iteration has no value for next. I'll still try to clean it up tho, but the important thing is I can successfully swap them now.

[dresden_phoenix]

If you got the WHOLE table back for the DB you could simply set aside a variables for prev/next and retrieve TWO records at a time... something like

$prev=$next=false;
while ($thisRow = mysql_fetch_assoc($result)){
   $next=mysql_fetch_assoc($result);
   if ($prev){ //Call your output foo:  'prev' link code //}
   //Call your output  'current link' link code goes here//
   if ($next){  //Call your output  'next' link code goes here //}
   $prev=thisRow;
}
 if ($next){  
   //Call your output foo:  'prev' link code, but this time send $thisRow  as an argument //        
   //Call your output  'current link', but this time send $next;
}

voila.. this will scale anyway way you want it and you can arrange the order you get your results via your mySQL statement!



Of course, I don't understand why one would retrieve a whole table for a specific article. You could use a couple of mySQL queries ( possibly one , but I didn't want to start explain JOINS etc, when I myself rarely use them) .


for example here am sorting by alphabetically ( around a chosen article ID=19 )

SELECT * FROM `article_test` WHERE  `title`<=(SELECT `title` FROM `article_test` WHERE `articleID`=19)  ORDER BY `title` DESC limit 2

This gives your previous and current article...

SELECT * FROM `article_test` WHERE  `title`>(SELECT `title` FROM `article_test` WHERE `articleID`=19)  ORDER BY `title` ASC limit 2

and this gives you your next. The beauty of this method is it conserves resources, and its scalable. Wanna confuse users by presenting links to 5 previous articles and 10 following articles ... change the limit on the corresponding mySQL query. You can order change the ordering method by changing the SORT BY and the column which is selected inside the parenthesis (these two must be the same) .


It also has the added advantage of giving you convenient falses. That is the LAST entry will return 'false' on the NEXT query, and you will only get ONE entry ( the article itself) if your target article is the first article. These could be used to skip echoing back/forth button when they dont apply.
Anyway, I hope that helps.

[BuddyH]

Absolutely it helps! That's my homework for this week then, I'm going to try all that out

Just having some trouble with my query now tho. I thought I had it sussed, but no. I want to try updating both rows in one query. I found this syntax, but it doesn't play ball when I use variables as clauses & values. I tried using quotes around them but to no avail. I wonder if this just doesn't whork when the case is the same as the row I am updating?

// Run the query & update sql according to the url.
$direction = $_GET['direction'];
$id_active = $_GET['sort_id'];

// See if we are moving up or down
switch ($_GET['direction']){    
    case 'down':
    $id_swap = $_GET['next'];
    break;
    
    case 'up':
    $id_swap = $_GET['prev'];
    break;    
}

// Now let's swap!
function swap_articles(){
db_connect();
$query = "UPDATE article_test
    SET sort_id = CASE sort_id
        WHEN $id_active THEN $id_swap
        WHEN $id_swap THEN $id_active
WHERE sort_id IN ($id_swap,$id_active)";
$result = mysql_query($query);
if (!$result)
        {
          return false;
          
        }
        else
        {
          return true;
        }

}
swap_articles();
?> 

[BuddyH]

If someone could tell me if this syntax is wrong, I'd greatly appreciate it.
Been looking around for other solutions, but they usually come back to this method. I can't seem to make this do anything.
Am I understanding this correctly?

$id_active = 1;
$id_swap = 2;

UPDATE article_test
    // the column to update is 'sort_id' and since it is also the column I need to test, then I use the same value for CASE right?
    SET sort_id = CASE sort_id
        WHEN $id_active THEN $id_swap  // sort_id 1 to become sort_id 2
        WHEN $id_swap THEN $id_active // sort_id 2 to become sort_id 1
WHERE sort_id IN ($id_swap,$id_active) // test only rows with sort_id 1 & sort_id 2 

[StarLion]

Well first impulse is "you havent ended your CASE declaration". (END)

second is you havent specified an ELSE. While not strictly necessary, what happens if both your WHEN cases are false?

[BuddyH]

Ah ok thanks, I have done this:

UPDATE article_test
SET sort_id =
  CASE sort_id
  WHEN $id_active THEN $id_swap  
  WHEN $id_swap   THEN $id_active 
  ELSE sort_id
END 
WHERE sort_id IN ($id_swap, $id_active) 

Someone assured me that should work, but it doesn't, so I wonder if my function is flawed somehow, but I can't for the life of me figure out how
This is the script.

<?php
// Run the query & update sql according to the url.
$direction = $_GET['direction'];
$id_active = $_GET['sort_id'];

// See if we are moving up or down
switch ($_GET['direction']){    
    case 'down':
    $id_swap = $_GET['next'];
    break;
    
    case 'up':
    $id_swap = $_GET['prev'];
    break;    
}

// Now let's swap!
function swap_articles(){
db_connect();
if (!db_connect()){
    echo '<P>Connection failed</P>';
}
else { echo '<p>Connection successful!</p>';
}

$query = "UPDATE article_test
SET sort_id =
  CASE sort_id
  WHEN $id_active THEN $id_swap  
  WHEN $id_swap   THEN $id_active 
  ELSE sort_id
END 
WHERE sort_id IN ($id_swap, $id_active)
";
$result = mysql_query($query);
if (!$result)
        {
          return false;
          
        }
        else
        {
          return true;
        }

}
echo $id_active.'<br />';
echo $id_swap;

swap_articles();

?>

[StarLion]

Well the immediate answer is... you havent passed the variables to the function.

But lets sort this out a little bit more.

You have a table, with sort_order field.
sort_order is an INT (of some variation), UNIQUE field.
sort_order has no holes (this is important)
sort_order 'top' is 1 ('up' moves towards 1, 'down' moves towards max)
Your idea is to move an article in the sort order.


2 Variables are needed:
The current article (defined as $_GET['sort_id'])
Which way to move it. (defined as $_GET['direction'])

Here's how i'd plot the logic.

Get variables from the $_GET array into local variables.
If id_active is not set, die.
If direction is not set, assume and set as down.
If direction is down; id_swap is id_active + 1
Else id_swap is id_active - 1
If id_swap = 0; id_swap = 1.
If id_swap Exists in Table:
  Swap(id_active,id_swap)