My sort_order column must be a unique number!
Moderator: General Moderators
My sort_order column must be a unique number!
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.
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!
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:
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!
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.
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: My sort_order column must be a unique number!
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;
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;
(#10850)
Re: My sort_order column must be a unique number!
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.
then, if the inputed value is unique it will be save/accepted.
if not, javascript will prompt a duplicate value based on ajax result.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: My sort_order column must be a unique number!
Not really code, but this will give you the idea
You wil now have a hole in the sort order,or a duplicate if revising, until you set the new one.
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.
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"Code: Select all
$Query = "SET/INSERT INTO Val=$New WHERE id=whatever"
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!
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.
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.
Interesting approach although ajax and offset never played well for me..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.
A lot, possibly 500 or so for starters.How many entries will there be?
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'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 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!
Any help with this one above? Thanks!