[Resolved] Checkboxes

Sharif

I am trying to create a list of users that hasn't been activated and have checkboxes next to them to that will let me select users and then activated them.

In the mysql table, teh field 'status' (type int) holds that data. If it is '1', then the user isn't active, if it is '2', then the user has been activated.

I want to be able to select multiple checkboxes (aka multiple users) and activate them all at once (the checkboxes, using a while loop, hold their userid. How can I do this? The code I have currently (uncomplete) is below.

	echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">
		<table width="100%" border="0" cellspacing="1" cellpadding="1">
			<tr>
				<td>&nbsp;</td>
				<td align="center" valign="top">Name</td>
				<td align="center" valign="top">Type</td>
				<td align="center" valign="top">Age</td>
				<td align="center" valign="top">Registered On</td>
				<td align="center" valign="top">Country</td>
				<td align="center" valign="top">Email</td>
			</tr>';
	$sql = "SELECT userid, first_name, last_name, type, ";
$sql .= "DATE_FORMAT(dob, '%Y') AS dob, DATE_FORMAT(date_registered, '%m/%d/%Y') AS date_registered, ";
$sql .= "country, email FROM users WHERE status = 1 ORDER BY date_registered DESC";
	$query = mysql_query($sql) or die(sql_error());
	if(mysql_num_rows($query) > 0)
	{			
		$currentdate = getdate();
		$year = $currentdate["year"]; 

	while($row = mysql_fetch_array($query))
	{
	$age = $year - $row['dob'];
	  echo '<tr>
			<td><input name="select_user" type="checkbox" id="select_user" value="' . $row['userid'] . '"></td>
			<td align="center" valign="top">' . $row['first_name'] . ' ' . $row['last_name'] .'</td>
			<td align="center" valign="top">' .  $row['type'] . '</td>
			<td align="center" valign="top">' . $age . '</td>
			<td align="center" valign="top">' . $row['date_registered'] . '</td>
			<td align="center" valign="top">' . $row['country'] . '</td>
			<td align="center" valign="top">' . $row['email'] . '</td>
		</tr>';
	}
	echo '<tr><td><input name="submit" type="submit" value="Activate"></td></tr></table></form>';
}
else
{
	echo 'There are no users that need to be registered.';
}

As you can see, it gets data from the table, loops it to get multiple records but I don't know how to activate multiple accounts at once.

I would try this but don't know how to complete it:

UPDATE users SET status = '2' WHERE userid = '????"

What would I put in the ??? because there would be more than one userid? Thanks for any help.

ahundiak

Just build a comma delimited list of ids and:

UPDATE users SET status = '2' WHERE userid IN (11,56,99)

You will want to get back an array of id's so use:
name="select_users[]"
for your input element. Only checked boxes will be returned.

tekky

you should be able to make a "activate all" button

then do

UPDATE table SET status='2' WHERE status='1'

or something similiar to that (that will affect all rows that need it rather)

LordShryku

I'd make the checkboxes an array, like this....

<?
echo "<form action=\"". $_SERVER['PHP_SELF']."\" method=\"post\"> 
      <table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"1\"> 
        <tr> 
          <td>&nbsp;</td> 
          <td align=\"center\" valign=\"top\">Name</td> 
          <td align=\"center\" valign=\"top\">Type</td> 
          <td align=\"center\" valign=\"top\">Age</td> 
          <td align=\"center\" valign=\"top\">Registered On</td> 
          <td align=\"center\" valign=\"top\">Country</td> 
          <td align=\"center\" valign=\"top\">Email</td> 
        </tr>"; 

$sql = "SELECT   userid, 
                 first_name, 
                 last_name, 
                 type,  

                 DATE_FORMAT(dob, '%Y') AS dob, 
                 DATE_FORMAT(date_registered, '%m/%d/%Y') AS date_registered
                 country, 
                 email 
        FROM     users 
        WHERE    status = 1 
        ORDER BY date_registered DESC"; 

$query = mysql_query($sql) or die(mysql_error()); 
if(mysql_num_rows($query) > 0) {
    $currentdate = getdate(); 
    $year = $currentdate['year'];  

while($row = mysql_fetch_array($query)) { 
    $age = $year - $row['dob']; 
    echo "<tr> 
            <td><input name=\"select_user[]\" type=\"checkbox\" id=\"select_user\" value=\"".$row['userid']."\"></td> 
            <td align=\"center\" valign=\"top\">".$row['first_name']." ".$row['last_name']."</td> 
            <td align=\"center\" valign=\"top\">".$row['type']."</td> 
            <td align=\"center\" valign=\"top\">".$age."</td> 
            <td align=\"center\" valign=\"top\">".$row['date_registered']."</td> 
            <td align=\"center\" valign=\"top\">".$row['country']."</td> 
            <td align=\"center\" valign=\"top\">".$row['email']."</td> 
          </tr>"; 
} 
echo "  <tr>
          <td><input name=\"submitButton\" type=\"submit\" value=\"Activate\"></td>
        </tr>
      </table>
      </form>"; 
} 
else { 
    echo "There are no users that need to be registered.";
}

if(isset($_POST['submitButton'])) {
    if(count($_POST['select_user'])==0) { 
        echo "No users to active";
    }
    else {
        $sql = "UPDATE users 
                SET status = '2'
                WHERE userid IN(";
        for($i=0;$i<count($_POST['select_user']);$i++) { 
        //implode could be used here also
            if($i==0) {
                $sql .= "'".$_POST['select_user']."'";
            }
            else {
                $sql .= ", '".$_POST['select_user']."'";
            }
        }
        $sql .= ")";
        if(mysql_query($sql)) {
           printf ("Updated records: %d\n", mysql_affected_rows());
        }
    }
}
?>

Sharif

ahundiak,

Can you please clarify?

if I do this:

select_user becomes an array???

And then I can do:

UPDATE users SET status = '2' WHERE userid ($select_user) ??

Sharif

LordShryku,

Can you please explain the LAST part of the code? Where you start with:

if(isset($_POST['submitButton'])) {...

LordShryku

//if the submit button is pressed
if(isset($_POST['submitButton'])) { 
    // if no checkboxes are checked, error out
    if(count($_POST['select_user'])==0) {  

        echo "No users to active"; 
    } 
    else {
        //otherwise, build the update query 
        $sql = "UPDATE users 
                SET status = '2' 
                WHERE userid IN("; 
        //loop through and collect the users in the array
        for($i=0;$i<count($_POST['select_user']);$i++) {  

        //implode could be used here also 
            if($i==0) { 
                $sql .= "'".$_POST['select_user']."'"; 
            } 
            else { 
                $sql .= ", '".$_POST['select_user']."'"; 
            } 
        } 
        $sql .= ")"; 
        //execute and say how many were updated
        if(mysql_query($sql)) { 
           printf ("Updated records: %d\n", mysql_affected_rows()); 
        } 
    } 
}

Sharif

OK I did an echo of $sql and this is what I saw:

UPDATE users SET status = '2' WHERE userid IN('Array').

Why is it being IN('Array') and not the userid's?

LordShryku

My bad...change that loop to this

if($i==0) {  

    $sql .= "'".$_POST['select_user'][$i]."'";  

}  

else {  

    $sql .= ", '".$_POST['select_user'][$i]."'";  

}

Sharif

OK that worked,

Now if I was going to delete users, it would be:

				$sql = "DELETE users WHERE userid IN("; 
				//loop through and collect the users in the array 
				for($i=0;$i<count($_POST['delete_user']);$i++) 
				{   

					if($i==0) 
					{   

						$sql .= "'" . $_POST['delete_user'][$i] . "'";   

					}   

					else 
					{   

						$sql .= ", '" . $_POST['delete_user'][$i] . "'";   

					}
				}  

				$sql .= ")";

correct? WRONG it wont work!

LordShryku

Originally posted by Sharif
correct? WRONG it wont work!

Well good luck figuring that out Mr Rude

Sharif

Hey I'm sorry, just a little frustrated. Why won't it work... it is the same method just differnet sql query...

n00854180t

Why don't you try figuring out why it won't work instead of demanding that other people do it for you.

Sharif

I am trying... just don't understand what is going on because the SQL query seems fine and I don't see any errors that you probably might find. Sorry if I offended you.

ahundiak

As LordShryku mentioned, You can use implode to simplify the code.

And you need to handle the case where no check boxes are checked in which case nothing gets posted.

$userIDs = null;
if (isset($_POST['delete_user']))
{
  /* Make a comma delimited list */
  $userIDs = implode(',',$_POST['delete_user']);
}
if ($userIDs !== null)
{
  $sql = "DELETE FROM users WHERE userid IN($userIDs)";
  mysql_query($sql);
}

Sharif

In LordShryku's code, it already checks to see if no boxes were checked and it works perfectly. The original purpose of the code works, but I want to be able to delete records too so I just replaced the original query with a delete query:

$sql = "DELETE users WHERE userid IN(";  

                //loop through and collect the users in the array 
                for($i=0;$i<count($_POST['delete_user']);$i++)  

                {    

                    if($i==0)  

                    {    

                        $sql .= "'" . $_POST['delete_user'][$i] . "'";    

                    }    

                    else  

                    {    

                        $sql .= ", '" . $_POST['delete_user'][$i] . "'";    

                    } 
                }   

                $sql .= ")";

Which does not work...

ahundiak

I guess I'm feeling generous today. It's "DELETE FROM ...". Just like my previous post stated.
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DELETE

Sharif

Yea that did the trick. Now I was trying to use implode() so I replaced this:

$sql = "DELETE users WHERE userid IN(";   

                //loop through and collect the users in the array 
                for($i=0;$i<count($_POST['delete_user']);$i++)   

                {     

                    if($i==0)   

                    {     

                        $sql .= "'" . $_POST['delete_user'][$i] . "'";     

                    }     

                    else   

                    {     

                        $sql .= ", '" . $_POST['delete_user'][$i] . "'";     

                    }  

                }    

                $sql .= ")";

with this:

$ids = implode(",", $_POST['delete_user']);
$sql = "DELETE FROM users WHERE userid = '$ids'";

What happens is that it does delete, but only the first one picked and not all of them at once...

ahundiak

$sql = "DELETE FROM users WHERE userid IN ($ids)";

BuzzLY

:rolleyes:

Anyone know where I can find a good sledgehammer?