List:General Discussion« Previous MessageNext Message »
From:Steven Staples Date:February 22 2012 4:44pm
Subject:RE: MySQL Session Variables with PHP
View as plain text  
> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: February 22, 2012 11:07 AM
> To: Steven Staples; mysql@stripped
> 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.

Thread
MySQL Session Variables with PHPSteven Staples22 Feb
  • Re: MySQL Session Variables with PHPPeter Brawley22 Feb
    • RE: MySQL Session Variables with PHPSteven Staples22 Feb
      • Re: MySQL Session Variables with PHPJohan De Meersman22 Feb