From: Peter Brawley
Date: February 22 2012 4:07pm
Subject: Re: MySQL Session Variables with PHP
List-Archive: http://lists.mysql.com/mysql/226838
Message-Id: <4F4512B1.1080904@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2/22/2012 9:47 AM, Steven Staples wrote:
> Good [insert time of day here] all!
>
> I am trying to reorder my auto-inc field in my database, and I have
> successfully done it with my "front end" that I use (SQLYog) with the
> following code:
>
> SET @var_name = 0;
> UPDATE `my_database`.`my_table` SET `id` = (@var_name := @var_name +1);
>
>
> Now, when I try this within PHP... I can't get it to work at all.  I assume
> that the SET is the issue, but I am not 100% sure.
>
> I use the PEAR MDB2 class, and I have tried it in 1 statement, but it
> failed, and I tried it with 2 statements, it didn't puke on it, but it
> didn't work either.
>
> <?php
> 	# db connection is already set up #
> 	echo $db->exec('SET @var_name = 0;')
> 	echo '<br />';
> 	echo $db->exec('UPDATE `my_database`.`my_table` SET `id` =
> (@var_name:= @ var_name +1);');
> 	exit;
> ?>
>
> Does anyone know how I can do this within PHP?  Worst case scenario, is that
> I just write a php shell() command instead, but I would rather avoid that if
> at all possible.

The manual warns us not to rely on repeat user var assignments, but your 
approach works for me:

$conn=mysql_connect( ... );
mysql_select_db("test");
mysql_query( "drop table if exists t" ) or exit(mysql_error());
mysql_query( "create table t (id int)" ) or exit(mysql_error());
for( $i=0; $i<10; $i++ ) mysql_query( "insert into t values(0)" ) or 
exit(mysql_error());
mysql_query( "set @var=0" ) or exit(mysql_error());
mysql_query( "update t set id=(@var:=@var+1)" ) or exit(mysql_error());
$res = mysql_query( "select id from t" ) or exit(mysql_error());
while( $row = mysql_fetch_row( $res )) echo $row[0], " ";

Output: 1 2 3 4 5 6 7 8 9 10

PB

-----

>
> Thanks in advance!
>
> Steven Staples
>
>
>