By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,746 Members | 1,865 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,746 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a

"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

P: n/a
"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

P: n/a
"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.