Jump to content


Photo

changing the position of records


  • Please log in to reply
3 replies to this topic

#1 KyleBragger

KyleBragger
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 19 March 2003 - 08:09 PM

Okay, how would I change the position of records in a table, e.g.:

id name
1 A
2 B
3 C

...and I want to move \'B\' down in the list...

id name
1 A
2 C
3 B

Please note that ORDER BY is not what I am asking about, as I would like this to be permanant and done within the table, as opposed to an output-only type of thing.
:: Kyle

#2 ystrigathe

ystrigathe
  • Members
  • Pip
  • Newbie
  • 5 posts
  • LocationCologne / Germany

Posted 20 March 2003 - 08:04 AM

because updates & join don\'t really work together, i think i\'d like to

alter table xyz add new_id int default null;
update xyz set new_id = ... // changing positions

update table xyz set id = id * (-1) where new_id is not null; // change values of keys (i think, id is primary key or at least unique)
update table xyz set id = new_id where new_id is not null; // only update for changed records...

#3 dkittell

dkittell
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 14 September 2004 - 02:21 AM

Is there a simple tutorial out there on this?
I knew how to do it in ASP but I work with PHP now and the code I used won't completly work

Any help is appreciated.

#4 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 14 September 2005 - 01:03 PM

To the origional poster, you're going to have to move them with a series of SELECTs and UPDATEs.

If you want to switch two:

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?
$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"SELECT name FROM table1 WHERE id IN (2,3)\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#FF8000\"]// get the results
[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--][span style=\"color:#0000BB\"]<?
$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE table1 SET name = $row[0] WHERE id = 3\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#FF8000\"]// execute

[/span][span style=\"color:#0000BB\"]$query [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"UPDATE table1 SET name = $row[1] WHERE id = 2\"[/span][span style=\"color:#007700\"];
[/span][span style=\"color:#FF8000\"]// execute
[/span][span style=\"color:#0000BB\"]?>
[/span]
[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]





Info: PHP Manual





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users