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

From: John Pastrovick (pastrovic2k_at_hotmail.com)
Date: 04/13/04 

Date: 13 Apr 2004 01:01:21 -0700

"Garp" <garp7@no7.blueyonder.co.uk> wrote in message news:<U9Dec.2101$iI5.20629621@news-text.cableinet.net>...
> "John Pastrovick" <pastrovic2k@hotmail.com> wrote in message
> news:a21852f1.0404121212.21899286@posting.google.com...
> > 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/Stored_Procedures.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

Relevant Pages

  • Re: SQL syntax to update records with diffrent function generated random PINS
    ... >> The following sql query updates records with the SAME PIN number but. ... In ASP I looped through the set assigning new values and then OUTSIDE ... the loop I updated the set rs1.UPDATE. ...
    (comp.lang.php)
  • Re: Queries and Now()
    ... ASP settings themselves may possibly cache ... some data it perceives as 'constant', avoiding a round trip to the server. ... Sure, that sounds like I try to send the problem to ASP, but if the query ... So probably in the loop where you think you've changed ...
    (microsoft.public.access.queries)
  • Re: faye,, pappy/now about Jerrys drama queen problems. ;-)
    ... loop, and was putting tightening up off. ... Pin checks, and their usefulness, especially plane side, have ... pointing at the rig in front of me. ... Just as a check in an airplane might, ...
    (rec.skydiving)
  • Re: LOOP WITHIN A LOOP
    ... Assuming you can define a set of forbidden values, a Query is probably the easiest way to go. ... But your criteria could be trickier than just an easily listed set. ... Public Function DataScore(_ ... The reason I asked the loop within a loop question was so that I can loop through all of the fields for each record anh have a running score. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: faye,, pappy/now about Jerrys drama queen problems. ;-)
    ... loop, and was putting tightening up off. ... Pin checks, and their usefulness, especially plane side, have ... Just as a check in an airplane might, ... for lack of a pincheck but then, ...
    (rec.skydiving)