471,337 Members | 1,132 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

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

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
Jul 17 '05 #1
3 2341

"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
Jul 17 '05 #2
"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
Jul 17 '05 #3
"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
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by ben | last post: by
3 posts views Thread by Luboą ©lapák | last post: by
1 post views Thread by Chris | last post: by
1 post views Thread by adamjblakey | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.