Allowing Users to Order Items in a List

Hi All,

I'm trying to create functionality like that offerred by the Netflix movie queue.

Users will be looking at a list of items, and should have access to a field in a given row that allows them to reorder the list.

For instance, if the table is:

1   Orange
2   Green
3   Purple

And the user enters a 1 in the 1st box in the 3rd row, the table will now be

1 Purple
2 Orange
3 Green

I am using PHP for the middleware.  The answer should allow for more than a single re-ordering, where users can change multiple values at a time.  Again, above example, but the user enters 2 next to purple and 1 next to green:

1 Green
2 Purple
3 Orange


The answer should account for this, in addition to best practice for setting up the table and error checking (i. e. user enters a "1" for both green and purple).  It will also need to account for new entries being added with a proper order number to the end of the list.

Thanks in advance, and I'm going to cross-post to the PHP section...
LVL 3
RaydotAsked:
Who is Participating?
 
Richard QuadlingSenior Software DeveloperCommented:
With apologies, here is a replacement for the handling of the data from $_POST. Additional comments added.

Tested in PHP4 and PHP5. Same result both times.

The bug was related to the reference and that fact that I was changing the value when I didn't need to.

Now, no reference and is working fine! So much for me knowing what I'm doing!!!!

<?php
if (isset($_POST['a_order']))
      {
      // Validate all the supplied data and default any unsupplied values.
      foreach($_POST['a_order'] as $i_key => $i_value)
            {
            // Convert the supplied data to an integer.
            if (intval($i_value) !== 0)
                  {
                  // Use the data as it is an integer.
                  $i_value = intval($i_value);
                  }
            else
                  {
                  // Construct a value as it is NOT an integer.
                  $i_value = count(array_unique($_POST['a_order']));
                  }
            // See if the value is in the array. If so, use the next available one.
            while(in_array($i_value, $_POST['a_order'], True) === True)
                  {
                  ++$i_value;
                  }
            // Save the value back into the array.
            $_POST['a_order'][$i_key] = $i_value;
            }

      // Sort the supplied order array with the options.
      array_multisort($_POST['a_order'], $a_options);
      }
?>
0
 
Richard QuadlingSenior Software DeveloperCommented:
If you are using PHP as the middleware, then once you have the array in order, you can do what you pretty much like?

<?php
$a_options = array
      (
      'Blue',
      'Brown',
      'Green',
      'Orange',
      'Pink',
      'Red',
      );

if (isset($_POST['a_order']))
      {
      // Validate all the supplied data and default any unsupplied values.
      foreach($_POST['a_order'] as $i_key => &$i_value)
            {
            if (intval($i_value) !== 0)
                  {
                  $i_value = intval($i_value);
                  }
            else
                  {
                  $i_value = count(array_unique($_POST['a_order']));
                  }
            while(in_array($i_value++, $_POST['a_order']));
            }
      // Sort the supplied order array with the options.
      array_multisort($_POST['a_order'], $a_options);
      }

// Build the form in the new sequence
$s_inputs = '';
foreach($a_options as $i_option_ID => $s_option)
      {
      $s_option_ID = 1 + $i_option_ID;
      $s_inputs .= <<< END_OPTION
{$s_option_ID} : {$s_option} <input type="text" name="a_order[{$i_option_ID}]" /><br />
END_OPTION;
      }
echo <<< END_HTML
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$s_inputs}
<input type="submit" />
</form>
END_HTML;
?>


Running this outputs (initially) ...

1 : Blue []
2 : Brown []
3 : Green []
4 : Orange []
5 : Pink []
6 : Red []
Submit

Say you place 1 in Pink, 2 in Orange and 3 in Red, then the output is ...

1 : Pink []
2 : Orange []
3 : Red []
4 : Blue []
5 : Brown []
6 : Green []

If you put 1 in Orange and Blue, then the output is ...

1 : Orange []
2 : Blue []
3 : Brown []
4 : Green []
5 : Pink []
6 : Red  []
0
 
RaydotAuthor Commented:
I always forget to say one thing...I have to read this from and write this to a MySQL db, so the existing values would have to be loaded from there.  How do I create this array from an existing DB (keeping in mind that it's got 11 and not 2 columns).
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
RaydotAuthor Commented:
I just checked this out...I'm not a whiz with PHP although I'm pretty good with programming in general, line 15 is throwing an "unexpected '&'" error in line 15.  I tried removing it which moves the error to the end of the file.
0
 
RaydotAuthor Commented:
Actually, as it turns out upon closer examination, it's a problem with the echo <<< syntax, which this server doesn't seem to support.  I'm reworking all of the URLs now...
0
 
RaydotAuthor Commented:
Ok, so here's what I have.  It sets up ok, but doesn't reorder properly.  If I leave the & in line 14 I still get an error message, does that have anything to do with it?

<?php
$a_options = array
(
 'Blue',
 'Brown',
 'Green',
 'Orange',
 'Pink',
 'Red',
 );

if (isset($_POST['a_order'])) {
  // Validate all the supplied data and default any unsupplied values.
  foreach($_POST['a_order'] as $i_key => $i_value){
            if (intval($i_value) !== 0) {
                    $i_value = intval($i_value);      
              } else {
                    $i_value = count(array_unique($_POST['a_order']));
            }
    while(in_array($i_value++, $_POST['a_order']));
    }
  // Sort the supplied order array with the options.
  array_multisort($_POST['a_order'], $a_options);
}

// Build the form in the new sequence
$s_inputs = '';
foreach($a_options as $i_option_ID => $s_option) {
  $s_option_ID = 1 + $i_option_ID;
     
$s_inputs .= <<< END_OPTION
{$s_option_ID} : {$s_option} <input type="text" name="a_order[{$i_option_ID}]" /><br />
END_OPTION;

}

$output = <<<END_HTML
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$s_inputs}
<input type="submit" />
</form>
END_HTML;

print $output;
?>
0
 
RaydotAuthor Commented:
And I see problem now with the & symbol, I'm running PHP 4.4.1, which doesn't support pass by reference...any workaround?
0
 
RaydotAuthor Commented:
Contacted ISP, asked them to upgrade to PHP 5, which they're doing...so now I just need to focus on the SQL part...
0
 
Richard QuadlingSenior Software DeveloperCommented:
It is important to understand the use of &.

foreach($_POST['a_order'] as $i_key => &$i_value)
 {
 $i_value = 'new value';
 }

This code REPLACES the value in the array.

Non & version ...

foreach($_POST['a_order'] as $i_key => $i_value)
 {
 $_POST['a_order'][$i_key] = 'new value';
 }

0
 
Richard QuadlingSenior Software DeveloperCommented:
Connecting to a DB (assuming mysql)

<?php
$s_server = '.....'; // maybe localhost or an IP address or a server-name - depends upon YOUR ISP.
$s_username = '....'; // Normally provided to you by your ISP.
$s_password = '.....'; // Normally provided to you by your ISP.
$s_database = '....'; // Most ISPs allow more than 1 database.

// Connect to the DB server.
$r_conn = mysql_pconnect($s_server, $s_username, $s_password) or die('Failed to connect to DB server : #' . mysql_errno() . ' : ' . mysql_error());

// Connect to the DB - should not be needed if your SQL statements include the database as part of the statement.
mysql_select_db($s_database, $r_conn) or die('Failed to connect to specific database : #' . mysql_errno() . ' : ' . mysql_error());

// Construct SQL statement.
$s_SQL = <<< END_SQL
SELECT
      tbl.columnOrder,
      tbl.columnColour
FROM
      tableColours tbl
ORDER BY
      tbl.columnOrder
END_SQL;

// Issue the query.
$r_results = mysql_query($s_SQL, $r_conn) or die('Failed to execute query : #' . mysql_errno() . ' : ' . mysql_error());

// Build results array.
$a_options = array();
while (False !== ($a_row = mysql_fetch_assoc($r_results)))
      {
      $a_options[$a_row['columnOrder']] = $a_row['columnColour'];
      }

// Free the results.
mysql_free_result($r_results);
?>

At this stage you have populated the array just like using ...

$a_options = array
(
 'Blue',
 'Brown',
 'Green',
 'Orange',
 'Pink',
 'Red',
 );

The order of the array is based upon that last stored order.
0
 
Richard QuadlingSenior Software DeveloperCommented:
Now the saving part...

We keep the sorting aspect of the existing code and add the save part afterwards.

if (isset($_POST['a_order']))
      {
      // Validate all the supplied data and default any unsupplied values.
      foreach($_POST['a_order'] as $i_key => $i_value)
            {
            if (intval($_POST['a_order'][$i_key]) !== 0)
                  {
                  $_POST['a_order'][$i_key] = intval($_POST['a_order'][$i_key]);
                  }
            else
                  {
                  $_POST['a_order'][$i_key] = count(array_unique($_POST['a_order']));
                  }
            while(in_array($_POST['a_order'][$i_key]++, $_POST['a_order']));
            }
      
      // Sort the supplied order array with the options.
      array_multisort($_POST['a_order'], $a_options);
      
      // Update the database with the new order.
      foreach($a_options as $i_key => $s_value)
            {
            $s_SQL = <<< END_SQL
UPDATE
      tableColours tbl
SET
      tbl.columnOrder = $i_key
WHERE
      tbl.columnColour = '$s_value'
END_SQL;
            $r_result = mysql_query($s_SQL, $r_conn) or die('Failed to update database : #' . mysql_errno() . ' : ' . mysql_error());
            mysql_free_result($r_result);
            }
      }
0
 
Richard QuadlingSenior Software DeveloperCommented:
So, a complete version (You need to supply the credentials!!!) with the fix for PHP4's lack of references in foreach ...

<?php
$s_server = '.....'; // maybe localhost or an IP address or a server-name - depends upon YOUR ISP.
$s_username = '....'; // Normally provided to you by your ISP.
$s_password = '.....'; // Normally provided to you by your ISP.
$s_database = '....'; // Most ISPs allow more than 1 database.

// Connect to the DB server.
$r_conn = mysql_pconnect($s_server, $s_username, $s_password) or die('Failed to connect to DB server : #' . mysql_errno() . ' : ' . mysql_error());

// Connect to the DB - should not be needed if your SQL statements include the database as part of the statement.
mysql_select_db($s_database, $r_conn) or die('Failed to connect to specific database : #' . mysql_errno() . ' : ' . mysql_error());

// Construct SQL statement.
$s_SQL = <<< END_SQL
SELECT
      tbl.columnOrder,
      tbl.columnColour
FROM
      tableColours tbl
ORDER BY
      tbl.columnOrder
END_SQL;

// Issue the query.
$r_results = mysql_query($s_SQL, $r_conn) or die('Failed to execute query : #' . mysql_errno() . ' : ' . mysql_error());

// Build results array.
$a_options = array();
while (False !== ($a_row = mysql_fetch_assoc($r_results)))
      {
      $a_options[$a_row['columnOrder']] = $a_row['columnColour'];
      }

// Free the results.
mysql_free_result($r_results);

// Have we been supplied data?
if (isset($_POST['a_order']))
      {
      // Validate all the supplied data and default any unsupplied values.
      foreach($_POST['a_order'] as $i_key => $i_value)
            {
            if (intval($_POST['a_order'][$i_key]) !== 0)
                  {
                  $_POST['a_order'][$i_key] = intval($_POST['a_order'][$i_key]);
                  }
            else
                  {
                  $_POST['a_order'][$i_key] = count(array_unique($_POST['a_order']));
                  }
            while(in_array($_POST['a_order'][$i_key]++, $_POST['a_order']));
            }
      
      // Sort the supplied order array with the options.
      array_multisort($_POST['a_order'], $a_options);
      
      // Update the database with the new order.
      foreach($a_options as $i_key => $s_value)
            {
            $s_SQL = <<< END_SQL
UPDATE
      tableColours tbl
SET
      tbl.columnOrder = $i_key
WHERE
      tbl.columnColour = '$s_value'
END_SQL;
            $r_result = mysql_query($s_SQL, $r_conn) or die('Failed to update database : #' . mysql_errno() . ' : ' . mysql_error());
            mysql_free_result($r_result);
            }
      }

// Build the form in the new sequence
$s_inputs = '';
foreach($a_options as $i_option_ID => $s_option)
      {
      $s_option_ID = 1 + $i_option_ID;
      $s_inputs .= <<< END_OPTION
{$s_option_ID} : {$s_option} <input type="text" name="a_order[{$i_option_ID}]" /><br />
END_OPTION;
      }
echo <<< END_HTML
<form method="POST" action="{$_SERVER['PHP_SELF']}">
{$s_inputs}
<input type="submit" />
</form>
END_HTML;
?>
0
 
RaydotAuthor Commented:
Sorry you went through such a workaround...I upgraded the server to PHP 5, which I mentioned.  The orginal sort code you sent me doesn't work, it doesn't properly sort...I'm going to add in the DB stuff and let you know how it goes.
0
 
Richard QuadlingSenior Software DeveloperCommented:
OOI. You say it doesn't reorder "properly". Can you show a URL? Where I can see what you get? What values do you put in? What do you expect the sequence to be?
0
 
RaydotAuthor Commented:
To be more specific about what I mean by "doesn't work..."

I load page and put 2 into "Blue," hit submit, and nothing happens.  I put 3 into "Blue" and it moves it to 2.  I put 4 in and it moves it to 2.  I put 5 in and it moves it to 6.  I put 6 in and it works.  

I could give more examples.  Again, still working on all of the SQL stuff...I'll keep checking.
0
 
Richard QuadlingSenior Software DeveloperCommented:
Start with
1 : Blue []
2 : Brown []
3 : Green []
4 : Orange []
5 : Pink []
6 : Red []

Place 1 in Pink, 2 in Orange and 3 in Red

What output do you expect?

My code generates (which is where I did the cut and paste from)

1 : Pink []
2 : Orange []
3 : Red []
4 : Blue []
5 : Brown []
6 : Green []

What would you expect to be output?
0
 
Richard QuadlingSenior Software DeveloperCommented:
Nothing happens STRONLY suggests your installation is not quite right.

0
 
RaydotAuthor Commented:
1 in pink, 2 in orange, 3 in red gives:

1 : Pink
2 : Orange  //so far so good
3 : Blue //No good!
4 : Red
5 : Brown
6 : Green

Where would you expect the problem with the install lies?  It's the ISP that's doing it, so I don't have much control.  If you think it would help to see this in HTML, let me know and I'll put it somewhere where you can get to it.
0
 
Richard QuadlingSenior Software DeveloperCommented:
Replicated the issue.
0
 
RaydotAuthor Commented:
You know what you're doing a lot more than I do!

RQuadling, thank you so much for all of your help, I'm going to be going through this stuff all day today and tomorrow and I will surely get back to you.
0
 
RaydotAuthor Commented:
Sorry, had to change directions, still looking this over...
0
 
RaydotAuthor Commented:
Oh, back to it....would I have a problem if more than one column has similar information?  

For instance let's say I have three columns, an order, a color, and a shape.

So:

1 blue triangle
2 yellow square
3 green circle
4 blue square
5 red circle

...and then the user puts a 1 in front of  #4.  I can't index to a unique value becaue other rows contain both "blue" and "square."  (This is not a hypothetical, this is actually the case with my current DB.)
0
 
Richard QuadlingSenior Software DeveloperCommented:
Shouldn't do.

0
 
RaydotAuthor Commented:
It's all good then.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.