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($sq l2)???

----------------------------------------------------------------------------
$qr = @mysql_query($s qlb1) or die("ERROR");
$rs1 = mysql_fetch_arr ay($qr);


$thepin=myrando mPIN();
$sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecod e."'";
$qr = @mysql_query($s ql2) or die("ERROR"");
--------------------------------------------------------------------------

Thanks

[Garp]

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


"John Pastrovick" <pastrovic2k@ho tmail.com> wrote in message
news:a21852f1.0 404121212.21899 286@posting.goo gle.com...[color=blue]
> 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($sq l2)???
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> $qr = @mysql_query($s qlb1) or die("ERROR");
> $rs1 = mysql_fetch_arr ay($qr);
>
>
> $thepin=myrando mPIN();
> $sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecod e."'";
> $qr = @mysql_query($s ql2) or die("ERROR"");
> --------------------------------------------------------------------------
>
> Thanks[/color]

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]

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

"Garp" <garp7@no7.blue yonder.co.uk> wrote in message news:<U9Dec.210 1$iI5.20629621@ news-text.cableinet. net>...[color=blue]
> "John Pastrovick" <pastrovic2k@ho tmail.com> wrote in message
> news:a21852f1.0 404121212.21899 286@posting.goo gle.com...[color=green]
> > 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($sq l2)???
> >
> > --------------------------------------------------------------------------[/color]
> --[color=green]
> > $qr = @mysql_query($s qlb1) or die("ERROR");
> > $rs1 = mysql_fetch_arr ay($qr);
> >
> >
> > $thepin=myrando mPIN();
> > $sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecod e."'";
> > $qr = @mysql_query($s ql2) or die("ERROR"");
> > --------------------------------------------------------------------------
> >
> > Thanks[/color]
>
> 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[/color]


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]

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

"Garp" <garp7@no7.blue yonder.co.uk> wrote in message news:<U9Dec.210 1$iI5.20629621@ news-text.cableinet. net>...[color=blue]
> "John Pastrovick" <pastrovic2k@ho tmail.com> wrote in message
> news:a21852f1.0 404121212.21899 286@posting.goo gle.com...[color=green]
> > 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($sq l2)???
> >
> > --------------------------------------------------------------------------[/color]
> --[color=green]
> > $qr = @mysql_query($s qlb1) or die("ERROR");
> > $rs1 = mysql_fetch_arr ay($qr);
> >
> >
> > $thepin=myrando mPIN();
> > $sql2="UPDATE clients SET pin='$thepin' WHERE code='".$thecod e."'";
> > $qr = @mysql_query($s ql2) or die("ERROR"");
> > --------------------------------------------------------------------------
> >
> > Thanks[/color]
>
> 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[/color]


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