Page 1 of 1

My sort_order column must be a unique number!

Posted: Tue May 05, 2009 6:53 pm
by Sindarin
I have created a product list in php/mysql which displays the products as rows. At the end of each row there is a textfield (sort_order) in which the user can input a number (integer) and by that number the products will be sorted next time he visits the page,

PRODUCT NAME | SORT ORDER
Laptop [ 1 ]
PC [ 2 ]
Macbook [ 3 ]
Pocket PC [ 4 ]
Smartphone [ 5 ]

The problem is that the user can input numbers that already exist and those will be duplicate entries from now on.
I need a way to make the sort_order number unique, a way to "auto-update" the sort_order column if the number entered exists so the following doesn't happen:

PRODUCT NAME | SORT ORDER
Laptop [ 1 ]*
PC [ 1 ]

Macbook [ 2 ]*
Pocket PC [ 4 ]

Smartphone [ 5 ]

*must not have duplicate entries
*must not have hollows between entries, numbers should begin from 1 and go on like 2,3,4,5,6,7... (not 2,3,5,8,9,10,13,14,16,17...)

I've tried searching for this but cannot find anything, and it seems such a common thing but I just can't do it.

Re: My sort_order column must be a unique number!

Posted: Tue May 05, 2009 7:04 pm
by Defiline
It is called "auto increment" fields.
In MySQL you should set primary key for your field then when you will add a new row, the value will be automatically incremented.
It is unique value.

Example:

Code: Select all

CREATE TABLE `test` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`record` TEXT NOT NULL 
) ENGINE = InnoDB;

Re: My sort_order column must be a unique number!

Posted: Tue May 05, 2009 10:09 pm
by califdon
No, he doesn't want an auto-increment field, he wants to let users specify a sort order--an entirely different requirement.

You could write a (probably complicated) routine in Javascript to do what you describe, but I think your strategy is wrong to begin with. How many entries will there be? If it's not too awfully many, I would suggest writing a Javascript routine to display two list boxes, one with all the items, and let the user select an item to transfer to the other list box in the order they prefer. This is fairly straightforward, using 2 arrays. Then when all the items have been transferred to the second list box, have another button that reads the array and assigns sequential numbers to be stored in the table. The user should not be concerned with numbers like 1, 2, 3... they should just specify the order in which they want the items displayed.

I'm not volunteering to write the routine for you, but it shouldn't be too difficult. I've written substantially that kind of thing in Access VBA. It's sometimes called multi-select or availability.

Re: My sort_order column must be a unique number!

Posted: Tue May 05, 2009 10:49 pm
by Christopher
It is not a integer field (which it should be ... actually SMALLINT) but you can try:

UPDATE mytable SET sort_order = sort_order + 1 WHERE category='foo' AND sort_order>=$value_to_be_added;

Then:

UPDATE mytable SET sort_order = $value_to_be_added WHERE id=$record_id;

Re: My sort_order column must be a unique number!

Posted: Wed May 06, 2009 3:54 am
by newbe
when your products are too many you could do ajax.
then, if the inputed value is unique it will be save/accepted.
if not, javascript will prompt a duplicate value based on ajax result.

Re: My sort_order column must be a unique number!

Posted: Wed May 06, 2009 8:25 am
by Bill H
Not really code, but this will give you the idea

Code: Select all

if $Insert
     $Query = "SET Val = Val+1 WHERE Val>=$New"
else if $New > $Old
     $Query = "SET Val = Val-1 WHERE Val>$Old AND Val<=$New"
else $Query = "SET Val = Val+1 WHERE Val<$Old AND Val>=$New"
You wil now have a hole in the sort order,or a duplicate if revising, until you set the new one.

Code: Select all

$Query = "SET/INSERT INTO Val=$New WHERE id=whatever" 
 
And give them a dropdown select with numbers to set priority. I would put an "onchange" trigger on it so that each time the user selects a new priority the list redisplays in the new sequence.

Although I do it in a popup which is modifying the item generally. There is a "priority" dropdown select which says "make this priority number..."

But you will definitely have to change that field to a numeric field for this approach to work.

Re: My sort_order column must be a unique number!

Posted: Thu May 14, 2009 4:49 am
by Sindarin
If you have played around with Joomla, you'd probably know what I mean. There is an up/down arrow which increments or decrements the order value and also a textfield to put an integer. Those values will always be unique.
when your products are too many you could do ajax.
then, if the inputed value is unique it will be save/accepted.
if not, javascript will prompt a duplicate value based on ajax result.
Interesting approach although ajax and offset never played well for me..
How many entries will there be?
A lot, possibly 500 or so for starters.
I'm not volunteering to write the routine for you, but it shouldn't be too difficult. I've written substantially that kind of thing in Access VBA. It's sometimes called multi-select or availability.
I'll try messing with it a bit more, I'll just need to find a way to iterate all the numbers in the 'order' column after I update the row. It feels kinda weird mySQL doesn't have a function for this.

I tried making an up arrow where it increments the product's value it kinda works but I can't get the unique number thing right...
The idea is> user press up arrow> that entry get sorted by its current sort number -1 then all fields get defragmented so there are no duplicates.

Code: Select all

if ($_GET['action']=='sortup')
{
 
$id=$_GET['id'];
 
$sort_query = "SELECT * FROM products WHERE id='$id' LIMIT 1";
$sort_result = mysql_query($sort_query) or die('Error in Query: ' . mysql_error());
while($row = mysql_fetch_array($sort_result))
 {
        $get_sort=$row['sort'];
        $get_sort=$get_sort-1;
}
 
 
$db_update="UPDATE products SET 
sort='$get_sort'
WHERE id='$id' LIMIT 1
";
mysql_query($db_update,$db_connection) or die('Error in Query: ' . mysql_error());
 
 
$iquery = "SELECT * FROM products ORDER BY sort ASC";
$iresult=mysql_query($iquery,$db_connection) or die('Error in Query: ' . mysql_error());
$sort=0;
while($row = mysql_fetch_array($iresult))
 {
 $sort= $sort+1;
 $db_update="UPDATE products SET 
sort='$sort' LIMIT 1";
mysql_query($db_update,$db_connection) or die('Error in Query: ' . mysql_error());
 
 
 }

Re: My sort_order column must be a unique number!

Posted: Wed May 20, 2009 5:59 am
by Sindarin
Any help with this one above? Thanks!