insert row into middle of table

[jamie85]

Is there a lovely easy way of swapping 2 mysql rows?

Come to think of it, swapping the unique IDs instead of the entire row data may have the desired effect.

Much appreciated

[rincewind456]

What?

[tomhath]

You should never consider changing the id of a row if it's autonumbered; that id uniquely identifies that row. Change the data in the other columns if you want. Or if other rows in other tables have references to the ids you can change the references. But trying to manipulate the ids is asking for problems later on.

[laserlight]

In the first place, why do you want to swap rows?

[Sxooter]

Assuming you have a legit need to do this, you could use something like:

update table set rowid=10000000 where rowid=123;
update table set rowid=123 where rowid=124;
update table set rowid=124 where rowid=10000000;

assuming all rowids are < 10000000.

[jamie85]

Hey Sxooter. Ive been trying that, and when i manually run the queries through the sql console, it works fine.

However when executed in my php code, it gets a little jumbled.

Im not using auto_increment for my ID column.

$sql = "UPDATE hunt SET mapid = '$qry[mapid]',
	locationid = 0 
	WHERE locationid =$first_location_id AND mapid =$qry[mapid]";


if ( ($result = mysql_query($sql)) == 0 )
{
	echo " ERROR 1";
}

// update second row with first id
$sql = "UPDATE hunt SET mapid = '$qry[mapid]',
	locationid = $first_location_id 
	WHERE locationid =$second_location_id AND mapid =$qry[mapid]";


if ( ($result = mysql_query($sql)) == 0 )
{
	echo " ERROR 2";
}

// update first row with second id
$sql = "UPDATE hunt SET mapid = '$qry[mapid]',
	locationid = $second_location_id
           WHERE locationid =0 AND mapid =$qry[mapid]";


if ( ($result = mysql_query($sql)) == 0 )
{
	echo " ERROR 3";
}

The first 2 queries work fine, but when it comes to changing the row with ID '0', the locatonid changes fine, but instead of the other columns staying as they were, they all seem to inherit the values that were in the other row?? eh?

[Sxooter]

Ummmm. Why are you updating TWO fields instead of one? It should be a straight change of the ID feld only, right?

[jamie85]

Sxooter wrote:

Ummmm. Why are you updating TWO fields instead of one? It should be a straight change of the ID feld only, right?

Correct.

I changed it like so:

// update first row with 0 id
$sql = "UPDATE hunt SET locationid = 1000 
		WHERE locationid =$first_location_id AND mapid =$qry[mapid]";


if ( ($result = mysql_query($sql)) == 0 )
{
	echo " ERROR 1";
}

// update second row with first id
$sql = "UPDATE hunt SET locationid = $first_location_id 
		WHERE locationid =$second_location_id AND mapid =$qry[mapid]";


if ( ($result = mysql_query($sql)) == 0 )
{
	echo " ERROR 2";
}

// update first row with second id
$sql = "UPDATE hunt SET locationid = $second_location_id
		WHERE locationid ='1000' AND mapid =$qry[mapid]";


if ( ($result = mysql_query($sql)) == 0 )
{
	echo " ERROR 3";
}

But still no luck :S

[Sxooter]

since location id should be a unique or primary key, just put it into the where clause