Page 1 of 1

updating table

Posted: Sat May 06, 2006 7:07 am
by yuan22m
hi

i hav a table with a field sort, how will my sql statement be if i want to update it with an incrementing number for sort and will copy the sort number if it has the same categoryid.

ex:

index name categoryid sort
1 x1 1003 null
2 x2 1004 null
3 x3 1001 null
4 x5 1003 null
5 x4 1006 null
6 x6 1005 null
7 x7 1004 null

result after update:

index name categoryid sort
1 x1 1003 1
2 x2 1004 2
3 x3 1001 3
4 x5 1003 1
5 x4 1006 4
6 x6 1005 5
7 x7 1004 2

Posted: Sat May 06, 2006 7:08 am
by yuan22m
forgot to thank you all in advance

Posted: Tue May 09, 2006 11:33 am
by BadgerC82
Hey man,

Well the simple way would be to do a:

SELECT sort FROM your_table_name WHERE category_id='$category_id'

Then:

Code: Select all

$query = "SELECT MAX(sort) as max_sort FROM your_table_name";
$result = mysql_query($query, $con);
$row = mysql_fetch_object($result);

$next_sort = $row->max_sort + 1;
Carl