Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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...
0
Raydot
Asked:
Raydot
  • 13
  • 11
1 Solution
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now