sql updating field for a few members

updating1 field for multiple members

[weddingm]

I need to know how to use the UPDATE feature for a db for multiple members.

Basically, I need to update a field from Yes to No for 100 member names. I gave the member names but not sure a quick way how to change all of their status' from Yes to No.

Thanks,
Matt

[rocknbil]

What is the attribute for updating these members? You will need some sort of trigger, unless it's just "all."

For all,

update members set approved = 1;

(But you probably already knew that . . . )

Note I have a 1, not "yes" or "no." These are textual values with a boolean definition. Set your field to tinyint(1) or boolean for this field, not yes or no. 0 = no, 1 = yes. This will make your DB faster.

If it's a selective update, something like this should work, baby-code for logic only.

select id,fname,lname,approved from members;

Now build checkboxes for unapproved members,
using the record id as a unique handle.

while ($row=mysql_fetch_array($result)) { 
 print $row['fname'] . ' ' . $row['lname']; 
 if ($row['approved']==1) { print "approved"; } 
 else { 
 $chk_name='approved_' . $row['id']; 
 print "<input type=\"checkbox\" name=\"$chk_name\" id=\"$chk_name\" value=\"1\">"; 
 } 
} 

OK? You should have approved_1234, approved_1235, etc. Now when you submit, only the checkboxes you check will be in $_POST/$_GET.

foreach ($_POST as $key=>$value) { 
 if (preg_match('/^approve\_\d+$/',$key)) { 
 list($tag,$id) = explode('_',$key); 
 // VERY IMPORTANT! This must be a number. 
 if (! ($id > 0)) { die("oops! id is not a valid id!"); } 
 $query = "update members set approved=1 where id=$id"; 
 // Do it! 
 } 
} 

Edit: Meh. You'll probably prefer to use array names for checkboxes, inherent in PHP, I like to see what I'm generating and hang a name on it . . . concept is the same though.

[edited by: rocknbil at 9:14 pm (utc) on Dec. 12, 2009]

[weddingm]

Is there a SQL code I can run in the phpMyAdmin?

[rocknbil]

Sure, is there an attribute by which you can select the members?

update members set approved="yes" where last_modified > '2009-12-01';

See first line of previous post if it's "all" . . . .

[weddingm]

What if only a select few?

For example

Update table set value="No" where username="#*$!#*$!, #*$!#*$!1, #*$!#*$!2, #*$!#*$!3, #*$!#*$!4";

This is not working?

[weddingm]

got it, thanks

[rocknbil]

Sorry . . . you can use an OR

update table set field='value' where username='one' or username='two';

but it might be easier (or not) to use "in set"

update table set field='value' where user_id in (1,2,5,7);

Note that using "in" against text values will only work if the first part of the text is in the term:

select * from table where field in ('John','Joe','Mary');

Johnny
Joey
SallyJoe <-- fails

[weddingm]

Yes, I did use the "or" and it worked great thanks