updating table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
yuan22m
Forum Newbie
Posts: 2
Joined: Sat May 06, 2006 6:46 am

updating table

Post 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
yuan22m
Forum Newbie
Posts: 2
Joined: Sat May 06, 2006 6:46 am

Post by yuan22m »

forgot to thank you all in advance
BadgerC82
Forum Commoner
Posts: 25
Joined: Tue Feb 07, 2006 6:53 am

Post 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
Post Reply