How would I go about doing the Before/After Ordering Theory.

[Demonic]

Alright I'm currently working on a project where I want to order table rows using the before and after theory.

 

If you all used SMF there is a create new forum option where you can pick weather you want the forum to be before or after a forum that is already created.

 

How would I go about doing that. So far I have the following:

 

<?php
$c = mysql_query("SELECT * FROM category");
$c2 = mysql_query("SELECT * FROM category");

if(!isset($_POST['update'])){

	echo("
		<form method='post'>
			<select name='mfy'>
		");
		while($ct = mysql_fetch_array($c)){
			echo ("<option value='$ct[order]'>$ct[name]</option>\n");
		}
		echo ("
			</select>
			<select name='order'>
				<option value='before'>Before</option>
				<option value='after'>After</option>
			</select>
		");
		echo ("
			<select name='cats'>
		");
		while($cd = mysql_fetch_array($c2)){
			echo ("<option value='$cd[order]'>$cd[name]</option>\n");
		}
		echo("
			</select><br />
			<input type='submit' value='Update Category' name='update' />
		</form>
	");

}
?>

 

So now I'm a little stumped on after I click the submit button I do 2 checks if its after or before in the middle selection box, but then How would I go about updating all rows in the Database.

 

Cat1 - Order 0

Cat 2 - Order 1

 

If I wanted Cat 2 to have order 0 I would get the current Id since It would be "before"  Since its zero I would do a Check to see if that ID is zero? and I wouldn't make "Cat 2"'s Order -1 but just 0 and then add +1 to all rows greater then 0.  How would I got about doing this.  It's just coming out wierd in my mind atm.

 

Thanks in advance.

[Demonic]

Alright It tried coding it but I get a wierd outcome: http://uni-code.com/codebox/order.php

 

<?php

//Connection include junk stufferz
$c = mysql_query("SELECT * FROM category");
$c2 = mysql_query("SELECT * FROM category");

if(!isset($_POST['update'])){

	echo("
		<form method='post'>
			<select name='mfy'>
		");
		while($ct = mysql_fetch_array($c)){
			echo ("<option value='$ct[order]'>$ct[name]</option>\n");
		}
		echo ("
			</select>
			<select name='order'>
				<option value='before'>Before</option>
				<option value='after'>After</option>
			</select>
		");
		echo ("
			<select name='cats'>
		");
		while($cd = mysql_fetch_array($c2)){
			echo ("<option value='$cd[order]'>$cd[name]</option>\n");
		}
		echo("
			</select><br />
			<input type='submit' value='Update Category' name='update' />
		</form>
	");

}else{

	if($_POST['order'] == "before"){
		$current = $_POST['mfy'];
		$before = $_POST['cats'];
	$idz = mysql_query("SELECT * FROM category ORDER BY `order` DESC");
	$boom = mysql_num_rows($idz);
	//echo $boom."<br />\n";

	$newO = ($before != 0) ? $before-1 : 0; 
	$new2 = $before+1;

	$orderz = mysql_query("UPDATE category SET `order` = '$newO' WHERE `order` = '$current' ") or die("ERROR");
	//$orderz2 = mysql_query("UPDATE category SET `order` = '$new2' WHERE `order` = '$before' ") or die("ERROR");

	while($id2 = mysql_fetch_array($idz)){

		if($id2[order] > $before || $id2[order] == $current){
			continue;
		}
		$order1 = $id2[order]+1;
		$order2 = $id2[id];
		mysql_query("UPDATE category SET `order` = '$order1' WHERE `id` = '$order2' ") or die("ERROR2");
	}

	}

}

echo ("
<BR><BR><BR><BR><BR><BR><BR><BR>
=================================
<BR>
");
$bb = mysql_query("SELECT * FROM category ORDER BY `order` ASC");
while($bbb = mysql_fetch_array($bb)){
	echo ("$bbb[id] <b>:</b> $bbb[order] <b>:</b> $bbb[name]<br />\n");
}
echo ("<BR>
=================================
");



?>

 

Does anyone know how I can get an accurate update for each row.

[Demonic]

bump

[neel_basu]

First $ct[name] is wrong Use $ct['name'] or $ct["name"]

You Can do it in this way

1. Configure your Database -> Table is this Way

    Along with all other fields add an another Field(Attribute/ Column) Named

    order and keep teh order in that field ten while retriving the value from

    database add order(`order_field_name`).

2.And then Update the order in your Table