SQL syntax to update records with diff'rent function generated random PINS

[John Pastrovick]

I use a function, myrandomPIN (), to generate random PIN numbers.

The following sql query updates records with the SAME PIN number but.
I want to generate DIFFERENT pin numbers for every record. The
function is ok but I can't figure out how to run it individually for
each record. In other words I do not know the correct syntax to use
UPDATE in a loop (if necessary) so that a different call to the
function is done every time or ecah record ends up with a different
PIN.

Or may be this can be done with a single mysql_query($sql2)???

----------------------------------------------------------------------------
$qr = @mysql_query($sqlb1) or die("ERROR");
$rs1 = mysql_fetch_array($qr);
$thepin=myrandomPIN();
$sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecode."'";
$qr = @mysql_query($sql2) or die("ERROR"");
--------------------------------------------------------------------------

Thanks

[Garp]

"John Pastrovick" <pa*********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

I use a function, myrandomPIN (), to generate random PIN numbers.

The following sql query updates records with the SAME PIN number but.
I want to generate DIFFERENT pin numbers for every record. The
function is ok but I can't figure out how to run it individually for
each record. In other words I do not know the correct syntax to use
UPDATE in a loop (if necessary) so that a different call to the
function is done every time or ecah record ends up with a different
PIN.

Or may be this can be done with a single mysql_query($sql2)???

-------------------------------------------------------------------------- -- $qr = @mysql_query($sqlb1) or die("ERROR");
$rs1 = mysql_fetch_array($qr);
$thepin=myrandomPIN();
$sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecode."'";
$qr = @mysql_query($sql2) or die("ERROR"");
--------------------------------------------------------------------------

Thanks

The important thing about your UPDATE statement is the WHERE clause - since
you only retrieve one record (I hope!), you can only update one at a time
this way.

For multiple updates of rows like this pseduocode:
update set (a=1 where id=1, a=2 where id=2)
you'd need to create and execute a temporary stored procedure (MySQL 5 and
up - http://dev.mysql.com/doc/mysql/en/St...ocedures.html). If you know
them, it's easy enough, but if you don't, stick to your original loop, it's
no hardship.

Garp

[John Pastrovick]

"Garp" <ga***@no7.blueyonder.co.uk> wrote in message news:<U9*********************@news-text.cableinet.net>...

"John Pastrovick" <pa*********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

I use a function, myrandomPIN (), to generate random PIN numbers.

The following sql query updates records with the SAME PIN number but.
I want to generate DIFFERENT pin numbers for every record. The
function is ok but I can't figure out how to run it individually for
each record. In other words I do not know the correct syntax to use
UPDATE in a loop (if necessary) so that a different call to the
function is done every time or ecah record ends up with a different
PIN.

Or may be this can be done with a single mysql_query($sql2)???

--------------------------------------------------------------------------

--

$qr = @mysql_query($sqlb1) or die("ERROR");
$rs1 = mysql_fetch_array($qr);
$thepin=myrandomPIN();
$sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecode."'";
$qr = @mysql_query($sql2) or die("ERROR"");
--------------------------------------------------------------------------

Thanks

The important thing about your UPDATE statement is the WHERE clause - since
you only retrieve one record (I hope!), you can only update one at a time
this way.

For multiple updates of rows like this pseduocode:
update set (a=1 where id=1, a=2 where id=2)
you'd need to create and execute a temporary stored procedure (MySQL 5 and
up - http://dev.mysql.com/doc/mysql/en/St...ocedures.html). If you know
them, it's easy enough, but if you don't, stick to your original loop, it's
no hardship.

Garp

In ASP I looped through the set assigning new values and then OUTSIDE
the loop I updated the set rs1.UPDATE. I do not know how to do this
in PHP.
Do I have to send a query for every record in a loop or is there a way
to set the records value individually in a loop and then send a query
ONCE outside the loop to update the whole table?? Can anybody explain?

I feel that sending a query foer every record is not the efficient
way to do it and there can be a similar way to ASP.

Thanks

[John Pastrovick]

"Garp" <ga***@no7.blueyonder.co.uk> wrote in message news:<U9*********************@news-text.cableinet.net>...

"John Pastrovick" <pa*********@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

I use a function, myrandomPIN (), to generate random PIN numbers.

The following sql query updates records with the SAME PIN number but.
I want to generate DIFFERENT pin numbers for every record. The
function is ok but I can't figure out how to run it individually for
each record. In other words I do not know the correct syntax to use
UPDATE in a loop (if necessary) so that a different call to the
function is done every time or ecah record ends up with a different
PIN.

Or may be this can be done with a single mysql_query($sql2)???

--------------------------------------------------------------------------

--

$qr = @mysql_query($sqlb1) or die("ERROR");
$rs1 = mysql_fetch_array($qr);
$thepin=myrandomPIN();
$sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecode."'";
$qr = @mysql_query($sql2) or die("ERROR"");
--------------------------------------------------------------------------

Thanks

The important thing about your UPDATE statement is the WHERE clause - since
you only retrieve one record (I hope!), you can only update one at a time
this way.

For multiple updates of rows like this pseduocode:
update set (a=1 where id=1, a=2 where id=2)
you'd need to create and execute a temporary stored procedure (MySQL 5 and
up - http://dev.mysql.com/doc/mysql/en/St...ocedures.html). If you know
them, it's easy enough, but if you don't, stick to your original loop, it's
no hardship.

Garp

In ASP I looped through the set assigning new values and then OUTSIDE
the loop I updated the set rs1.UPDATE. I do not know how to do this
in PHP.
Do I have to send a query for every record in a loop or is there a way
to set the records value individually in a loop and then send a query
ONCE outside the loop to update the whole table?? Can anybody explain?

I feel that sending a query foer every record is not the efficient
way to do it and there can be a similar way to ASP.

Thanks