> -----Original Message----- > From: Peter Brawley [mailto:peter.braw...@earthlink.net] > Sent: February 22, 2012 11:07 AM > To: Steven Staples; mysql@lists.mysql.com > Subject: Re: MySQL Session Variables with PHP > > 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 >
I feel like a TOTAL newb here... If you look at my php example, I forgot the ; at the end of the first db query... so it failed out silently (my php errors are off, changed that too) Now it works... lol As for why I want to do this? I have a table that I constantly add, and remove items from, and I do it by looking up, and then deleting them (it is a long story/process). Anyways, the script stops every 8 hours, and restarts. So what I want to do, is reset all the auto-inc counters back to starting at 1, so that they don't get too large and out of control. I don't need to reference the ID's anywhere else, just when I go through each iteration. I know that this *could* be bad for most databases/tables, but in my case, it is not going to be an issue. Thanks, and sorry for my ID10T error, since it works just fine! Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
