SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to Solve a Problem like Move Down?

    Hello.

    Here is some code which i'm working on along with the HTML output.

    PHP Code:
    <?php
    /*
    Attempt to create Move Up / Move Down list functionality using a FAQ as example
    Data:
    faq_id - int (PK)
    faq_question varchar
    faq_answer text (not used below)
    faq_order int
    */
    $DBHOST "blah";
    $DBUSER "blah";
    $DBPASS "blah";
    $MYSQLDB "blah";

    //i'll be needing these variables methinks.
    $id_before 0;
    $id_after 0;
    $order_before 0;
    $order_after 0;

    $counter 0;
    $num_rows 0;

    // mysql connection blah
    $link mysql_connect($DBHOST$DBUSER$DBPASS);
    mysql_select_db($MYSQLDB);
    $query "SELECT faq_id, faq_question, faq_order FROM faq ORDER BY faq_order";
    $result mysql_query($query);

    // set num rows so that pge up and pge down can be displayed as appropriate
    $num_rows mysql_affected_rows($link);

    echo 
    "<ul>";
    While(
    $row mysql_fetch_assoc($result)){

        if(
    $counter && $counter != ($num_rows-1)){
            echo 
    "<li>".$row['faq_question']." <a href=\"?ud=up&amp;thisid=".$row['faq_id']."&amp;thispos=".$row['faq_order']."&amp;previd=".$id_before."&amp;prevpos=".$order_before."\">Move Up</a> / <a href=\"\">Move Down</a></li>";
        }elseif(
    $counter == ($num_rows-1)){
            echo 
    "<li>".$row['faq_question']." <a href=\"?ud=up&amp;thisid=".$row['faq_id']."&amp;thispos=".$row['faq_order']."&amp;previd=".$id_before."&amp;prevpos=".$order_before."\">Move Up</a></li>";        
        }else{
            echo 
    "<li>".$row['faq_question']." <a href=\"\">Move Down</a></li>";    
        }

        
    // set the counters

        
    $id_before $row['faq_id'];
        
    $order_before $row['faq_order'];
        
    // increment the counter
        
    $counter++;
    }
    echo 
    "</ul>";
    ?>
    HTML Code:
    <ul><li>Question 1: <a href="">Move Down</a></li><li>Question 2: <a href="?ud=up&amp;thisid=2&amp;thispos=2&amp;previd=1&amp;prevpos=1">Move Up</a> / <a href="">Move Down</a></li><li>Question3: <a href="?ud=up&amp;thisid=3&amp;thispos=3&amp;previd=2&amp;prevpos=2">Move Up</a> / <a href="">Move Down</a></li><li>Question 4 <a href="?ud=up&amp;thisid=4&amp;thispos=4&amp;previd=3&amp;prevpos=3">Move Up</a> / <a href="">Move Down</a></li><li>Question 5: <a href="?ud=up&amp;thisid=5&amp;thispos=5&amp;previd=4&amp;prevpos=4">Move Up</a> / <a href="">Move Down</a></li><li>Question 6: <a href="?ud=up&amp;thisid=6&amp;thispos=6&amp;previd=5&amp;prevpos=5">Move Up</a></li></ul>
    Move Up is no problem - simply execute a couple of statements swapping over the value in the order column by refering to parameters in the query string.

    El problemo is that that I can't figure out whats the best way to get the Move Down function working - as the current record set doesn't know what the next id or order number is. Ergo the next id or next order number cannot be passed in the query string.

    I'd prefer not to assume that the next id and order variable are consecutively numbered - after all, Steven Seagal says that assumption is the mother of all foul ups.

    So - without some seemingly over complex server side processing when clicking the Move Down link (at least 1 select and two update queries) can anyone suggest another way of cracking this nut.

    I have a vauge notion that I could do something clever with my initial query involving table aliasing and a subquery - but unfortunately my SQL skills aren't up to it if there is. I'm working with mysql 5.

    Maybe I'm way off track here and there is a much more simple solution???

    Note I have omitted the code that actually does the "moving up" from the script below.

    Thanks

    TC.

  2. #2
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i suppose you could buffer the output 1 result ahead, so you would know the next id.

    psuedo code
    PHP Code:
    // skip ahead once
    $thisrow mysql_fetch_assoc($result)

    While(
    $nextrow mysql_fetch_assoc($result)){ 
        
    printf('?id=%s&nextid=%s'$thisrow['id'], $nextrpw['id']);

        
    // advance our buffer
        
    $thisrow $nextrow;

    btw, you might find this intresting
    http://www.phpriot.com/d/articles/cl...jax/index.html

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tips ClamCrusher. I hadn't thought about creating a new result object...

    ... and that link to phpriot really rocks my boat... Die Hard gets my vote.

    I'll finish off my glass of Pinot Grigio and look at making these work tomorrow.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ta Muchly clam crusher - worked a treat. I just wish I hadn't finished the whole bottle last night.

    PHP Code:
    <?php
    /*
    Data:
    faq_id - int (PK)
    faq_question varchar
    faq_answer text (not used below)
    faq_order int
    */

    $DBHOST "localhost";
    $DBUSER "root";
    $DBPASS "*****";
    $MYSQLDB "*****";

    $link mysql_connect($DBHOST$DBUSER$DBPASS);
    mysql_select_db($MYSQLDB);

    if(isset(
    $_GET['thisid'])){
        
    // get the varialbes and cast them to int.
        
    $first_id = (int) $_GET['thisid'];
        
    $second_id = (int) $_GET['newid'];
        
    $first_pos = (int) $_GET['thispos'];
        
    $second_pos = (int) $_GET['newpos'];
        
    // if they are all numbers that's good enough for me...
        
    if($first_id*$second_id*$first_pos*$second_pos 0){
            
    $query1 "UPDATE faq SET faq_order = ".$second_pos." WHERE faq_id = ".$first_id;
            
    $query2 "UPDATE faq SET faq_order = ".$first_pos." WHERE faq_id = ".$second_id;
            
    $result1 mysql_query($query1);
            if (!
    $result1) {
                die(
    'error: ' mysql_error());
            }
            
    $result2 mysql_query($query2);
            if (!
    $result2) {
               die(
    'error: ' mysql_error());
            }        
        }else{
            echo 
    "<p>A bad value was passed.</p>";
        }
    }

    //i'll be needing these variables methinks.
    $id_before 0;
    $id_after 0;
    $order_before 0;
    $order_after 0;

    $counter 0;
    $num_rows 0;

    // mysql connection blah
    $link mysql_connect($DBHOST$DBUSER$DBPASS);
    mysql_select_db($MYSQLDB);
    $query "SELECT faq_id, faq_question, faq_order FROM faq ORDER BY faq_order";
    $result mysql_query($query);

    // set num rows so that pge up and pge down can be displayed as appropriate
    $num_rows mysql_affected_rows($link);

    echo 
    "<ul>";
    While(
    $row mysql_fetch_assoc($result)){

        if(
    $counter != ($num_rows-1)){
            
    // create a new array with the next row in it.
            
    $nextrow mysql_fetch_assoc($result);
            
    // and populate the variables
            
    $id_after $nextrow['faq_id'];
            
    $order_after $nextrow['faq_order'];        
            
    // then move the pointer back to where it should be.
            
    mysql_data_seek($result,$counter+1);
        }
        
    //Display the list
        
    if($counter && $counter != ($num_rows-1)){
            echo 
    "<li>".$row['faq_question']." <a href=\"?thisid=".$row['faq_id']."&amp;thispos=".$row['faq_order']."&amp;newid=".$id_before."&amp;newpos=".$order_before."\">Move Up</a> / <a href=\"?ud=down&amp;thisid=".$row['faq_id']."&amp;thispos=".$row['faq_order']."&amp;newid=".$id_after."&amp;newpos=".$order_after."\">Move Down</a></li>";
        }elseif(
    $counter == ($num_rows-1)){
            echo 
    "<li>".$row['faq_question']." <a href=\"?thisid=".$row['faq_id']."&amp;thispos=".$row['faq_order']."&amp;newid=".$id_before."&amp;newpos=".$order_before."\">Move Up</a></li>";        
        }else{
            echo 
    "<li>".$row['faq_question']." <a href=\"?thisid=".$row['faq_id']."&amp;thispos=".$row['faq_order']."&amp;newid=".$id_after."&amp;newpos=".$order_after."\">Move Down</a></li>";    
        }

        
    // set some vars
        
    $id_before $row['faq_id'];
        
    $order_before $row['faq_order'];
        
    // increment the counter
        
    $counter++;
    }
    echo 
    "</ul>";
    ?>


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
  •