SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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..
    PHP Code:
    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?

  2. #2
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    One way is to use a for loop instead of a foreach loop, assuming $articles is stored with a sequential integer index.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    // 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']). 

  4. #4
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You can use this:

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

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that. It looks like it makes sense to me, but alas I tried it and it outputs nothing.
    PHP Code:
    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.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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'] ....
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    //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 />';


  8. #8
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,792
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)
    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

    Code:
    $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 )
    Code:
    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...

    Code:
    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.

  12. #12
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    PHP Code:
    // 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();
    ?> 

  13. #13
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    PHP Code:
    $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 

  14. #14
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  15. #15
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ok thanks, I have done this:

    PHP Code:
    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 Code:
    <?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();

    ?>

  16. #16
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •