Solved

Allowing Users to Order Items in a List

Posted on 2006-06-19
24
400 Views
Last Modified: 2008-02-01
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
Comment
Question by:Raydot
  • 13
  • 11
24 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 16940724
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
 
LVL 3

Author Comment

by:Raydot
ID: 16943286
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
 
LVL 3

Author Comment

by:Raydot
ID: 16943352
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
 
LVL 3

Author Comment

by:Raydot
ID: 16946055
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
 
LVL 3

Author Comment

by:Raydot
ID: 16946199
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
 
LVL 3

Author Comment

by:Raydot
ID: 16946274
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
 
LVL 3

Author Comment

by:Raydot
ID: 16947212
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16949107
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16949135
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16949183
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16949217
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
 
LVL 3

Author Comment

by:Raydot
ID: 16952319
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:RQuadling
ID: 16952397
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
 
LVL 3

Author Comment

by:Raydot
ID: 16952405
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16952415
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16952540
Nothing happens STRONLY suggests your installation is not quite right.

0
 
LVL 3

Author Comment

by:Raydot
ID: 16956422
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 16957714
Replicated the issue.
0
 
LVL 40

Accepted Solution

by:
RQuadling earned 500 total points
ID: 16957734
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
 
LVL 3

Author Comment

by:Raydot
ID: 16959598
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
 
LVL 3

Author Comment

by:Raydot
ID: 17020210
Sorry, had to change directions, still looking this over...
0
 
LVL 3

Author Comment

by:Raydot
ID: 17060491
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
 
LVL 40

Expert Comment

by:RQuadling
ID: 17071234
Shouldn't do.

0
 
LVL 3

Author Comment

by:Raydot
ID: 17092321
It's all good then.  Thanks!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now