MySQL Updates

[SchweppesAle]

hi, I'm trying to use the Mysql UPDATE function but I'm having trouble "switching" specific column entries for these two rows.  The table has the following columns ->  AuthorName, AuthorID, position.  Both position and AuthorID serve as indexes.  I'd like to leave the position column alone and change the other two however I receive an error since the AuthorID must always be unique and I was trying to switch them with the following statement:

 

mysql_query("UPDATE jos_AuthorList SET AuthorName='$NewName' WHERE AuthorName='$OriginalName'") 
or die(mysql_error()); 

mysql_query("UPDATE jos_AuthorList SET AuthorID='$NewID' WHERE AuthorID='$OriginalID'") 
or die(mysql_error()); 

 

Here's the actual code, you'll see that I was actually trying to cycle all entries in the table by using the two arrays tempArray(old entries) and tempArray2(new entries). 

 

<?php
global $mainframe;
$db =&JFactory::getDBO();
$query = "SELECT * FROM #__AuthorList";
$db->setQuery( $query, 0, $count );
$rows = $db->loadObjectList();
$counter = 0;

foreach($rows as $row)
{
$tempArray[$counter][0] = $row ->AuthorName;
/*echo $tempArray[$counter][0];*/
$tempArray[$counter][1] = $row ->AuthorID;
/*echo $tempArray[$counter][1];*/
$counter++;
}
echo "<hr/>";
for ($x = 0; $x <= $counter; $x++)
{
	$y = $x + 1;
	$tempArray2[$y][0] = $tempArray[$x][0];
	/*echo $tempArray2[$y][0];*/
	$tempArray2[$y][1] = $tempArray[$x][1];
	/*echo $tempArray2[$y][1];*/
}

$tempArray2[0][0] = $tempArray[$counter - 1][0];
$tempArray2[0][1] = $tempArray[$counter - 1][1];
/*echo "<hr/>";*/

for ($x = 0; $x < $counter; $x++)
{
$OriginalName = $tempArray[$x][0];
$OriginalID = $tempArray[$x][1];
echo "<b>";
echo $OriginalName;
echo $OriginalID;
echo "</b>";
echo "<hr/>";

$NewName = $tempArray2[$x][0];
$NewID = $tempArray2[$x][1];
echo $NewName;
echo $NewID;
echo "<hr/>";

/*
mysql_query("UPDATE jos_AuthorList SET AuthorName='temp' WHERE AuthorName='$OriginalName'") 
or die(mysql_error()); 

mysql_query("UPDATE jos_AuthorList SET AuthorName='$NewName' WHERE AuthorName='temp'") 
or die(mysql_error());



mysql_query("UPDATE jos_AuthorList SET AuthorID='99999' WHERE AuthorID='$OriginalID'") 
or die(mysql_error()); 

mysql_query("UPDATE jos_AuthorList SET AuthorName='$NewID' WHERE AuthorName='99999'") 
or die(mysql_error());*/

}


?>

 

thanks in advance, I was also wondering if there's a way to execute this code automatically at a specific time during the day. 

[revraz]

Well are you trying to use the same value in more than 1 row for AuthorID?

 

And yes, you can use a CRON JOB to run it at a certain time.

[SchweppesAle]

Well are you trying to use the same value in more than 1 row for AuthorID?

 

And yes, you can use a CRON JOB to run it at a certain time.

 

I'm not trying to use the same value, I'm really just trying to think of a way to cycle the AuthorName and AuthorID of each entry within the table.  I used used php to load all entries into an array; cycled them then placed the output into another Array. 

 

The only problem I'm having now is how would I go about replacing the column entries within the actual database. 

 

Ex: Last column goes to the top; they all move down.  Sort of like LIFO except Out just means you're placed back at the top of the list.