MySql Loop, How?

[Lamez]

I want a loop that will take ever user, and write a 0 in the paid column.

 

I wanna make sure I got this down, so here is what I have:

<?php
if ($paid == ($_POST['paid'])){
  $results = mysql_query("SELECT `username`, `paid` FROM `users`")
   while($row = mysql_fetch_array($result)){
    $user = $row['username'];
    mysql_query("UPDATE `users` SET `paid` = '0' WHERE `username` = '$user'");
   }
}
?>

 

would this work?

[cooldude832]

you can update multiple rows at a time the WHERE portion dictates what is updated

i.e

Update `users` SET `paid` = '0' 

will update all rows in users table to have paid = 0

[Lamez]

isn't that what I have?

 

mysql_query("UPDATE `users` SET `paid` = '0' WHERE `username` = '$user'");

[cooldude832]

except you are doing a lot of queries I am doing 1

[Lamez]

oh I understand now, so instead of doing this:

 

mysql_query("UPDATE `users` SET `paid` = '0' WHERE `username` = '$user'");

 

I could do this w/o a loop?:

 

mysql_query("UPDATE `users` SET `paid` = '0'");

[craygo]

Since you do not have a where clause in your first query you are essentially updating every row. Which is why cooldude is giving you a simpler solution if that's what you want to do.

 

now if you only want to update certain users then your code would be fine when you add a where clause in there.

 

<?php
if ($paid == ($_POST['paid'])){
  $results = mysql_query("SELECT `username`, `paid` FROM `users` WHERE `users` IN ('John', 'Jack', 'Harry')")
   while($row = mysql_fetch_array($result)){
    $user = $row['username'];
    mysql_query("UPDATE `users` SET `paid` = '0' WHERE `username` = '$user'");
   }
}
?>

 

Now just 3 rows will be updated

 

Ray

[cooldude832]

That makes no sense craygo because you could just say

Update `users` set paid='0' where username IN ('John', 'Jack', 'Harry')

 

You shouldn't need to query a table to figure out the rows to update unless you have some fancy thing to it.

[craygo]

Well it does make sense cause they both will work. I know what you mean by using 2 queries makes no sense, I just wanted to point out the use of the where part of the query.

 

I think I just got stuck on using his existing code to point it out.

 

Ray

[cooldude832]

Well it does make sense cause they both will work. I know what you mean by using 2 queries makes no sense, I just wanted to point out the use of the where part of the query.

 

I think I just got stuck on using his existing code to point it out.

 

Ray

 

I'm all about running a query to the extent of mysql knowledge because it usually simplifies stuff