[SOLVED] manually change ordering on records

[demon_athens]

I have a mysql database full of pictures, and I need to have the abilty to short the results through an ORDER field in mysql that will give the ASC ordering I want.

 

Problem is that there isn't any field ORDER so I must create it. Can you give me some suggestions hot to alter the ordering results? Its impossible to do it manually and write 1,2,3,4 ....500 with the order that I want in each picture. So there must be a way to see the id of the picture and swap it ( back or forward) through a new column...Should be float?

 

If you know any tutorial that I could read you would help me a lot. If you can help through here with a general example it would be great!

 

<?php

// change order

if($_GET['item']){

$getitem = $_GET['item'];
$getaction = $_GET['action'];

// I suck!...what to do here....hm...

}
//eof order

$stiles = 4; // number of column results
include_once("../mysql_connect.php");
			if(isset($_GET['productid'])){$curid = $_GET['productid'];}else{$curid = $_POST['productid'];}
			$photos = "SELECT `photo`,`id` FROM `layout_pics` WHERE `layout_id`='$curid' ORDER BY `forder` ASC";
			$photos = @mysql_query ($photos) or die(mysql_error());
			$row_photos = mysql_fetch_assoc($photos);
			$totalRows_photos= mysql_num_rows($photos);

do{
	// show pics
         }while ...

?>

[AndyB]

Changing database record ids is a really bad idea.  It's the first step down a slippery slope that ends with a relational database becoming a useless pile of data with all relationships compromised.

 

Isn't there some existing product attribute that you could use in an ORDER by clause before displaying results? If not, how would you manually re-order them anyway?

[demon_athens]

I don't want to change the PRIMARY KEY `ID` of the table. Anyway after a lot of thinking I done it. I run this script once to give the new column the same INT as my primary KEY:

 

<?php
// initiate Ordering by assigning the key ID to the new field
             include_once("../../mysql_connect.php");

			$photos = "SELECT `id` FROM `gallerycontent`";
			$photos = @mysql_query ($photos) or die(mysql_error());
			$row_photos = mysql_fetch_assoc($photos);
			$totalRows_photos= mysql_num_rows($photos);
			$i=1;
			echo "<p>Total Photos: ".$totalRows_photos."</p>";
			do{
			$getphotoid = $row_photos['id'];
			$query = "UPDATE `gallerycontent` SET forder='$getphotoid' WHERE id='$getphotoid'";
			$addy = @mysql_query ($query) or die(mysql_error());
if ($addy){ echo "<p class=\"normal\"><center>".$i.". The order changded :<b> " .stripslashes($titlegr). "</b>succesfully</p>";}
$i++;
			 }while($row_photos = mysql_fetch_assoc($photos));
										 $rows = mysql_num_rows($photos);
											if($rows > 0) {
											mysql_data_seek($photos, 0);
											$row_category = mysql_fetch_assoc($photos);
											}
?>

 

After that I used another script that does the trick. I use left and right image arrows, give $_GET values and the script finds the next and previous item in ordering sequence and swap them on the fly. All this in an iframe with a refresh layout button!

 

Simply beautiful, and the most important of all I figure it out by myself 

 

Thank you very much Andy for your answer, have a nice day.