Insert dynamic number of checked box values to database

[wright0768]

What I have so far is a page that produces a list of usernames with checkboxes next to them.  From the usernames that I check, I want to update a field for each of those users.  My problem is that the number of checkboxes I check is not going to be static so I am pretty sure I have to run a loop based on the number of checked checkboxes ("People chosen").  Here is what I have so far:

 

<?php
if (isset($_POST['submit1'])) {

  $pick = $_POST["pick"];
        $how_many = count($pick);
        echo 'People chosen: '.$how_many.'<br><br>';
       
   
      
            echo "<br><br>";
		$count = 0;
		while ($count < $how_many) {
			//mysql code probably should go here but I am stuck


			$count++;

		}

    } else {

$gender = mysql_real_escape_string($_POST['Gender']);
$limit = mysql_real_escape_string($_POST['limit']);




echo $gender;
echo "<br />";
$count = 0;
if ($gender == 'Male') {
	$result = mysql_query("SELECT * FROM users WHERE gender =\"1\" LIMIT 0, $limit", $connection);
	if (!$result) {
		die("Database query failed: " . mysql_error());
	} 
	?>	<form method="post"> <?php
while ($row = mysql_fetch_array($result)) {
 ?> <input name="pick[]" type="checkbox" value="<?php echo $row['username'] ?>"><?php
echo $row['username'];?> <br />
<?php
}
?>
<input type="submit" name="submit1" value="Make Offer" />
</form>
<?php
}
?>

 

 

I hope this makes sense.  Any help would be greatly appreciated.

[Psycho]

You NEVER want to run queries in loops. It is extremely inefficient and puts an unnecessary load on the server. The solution to your problem is very simple and only requires two total queries. Give me a few minutes and I'll post some sample code.

 

 

EDIT: Can you show the field that you want updated and how it should be updated? Is it a tiny int that will be a 0 or 1?

[wright0768]

It is an int and the field name is offer.  Do you need any information other than that?

[Psycho]

OK, I have made many changes. I'll try to give you some details about what I did. Also, I didn't test since I don't have your database, so there may be some syntax errors.

 

1. Well, first off I was wrong. It doesn't require TWO queries - it only requires ONE that uses an IF() statement to set the value appropriately.

 

2. It looks like there is more code that you left off to handle when the gender is female. Assuming you are doing the exact same thing for females as males, having two sets of code to do the same thing is unnecessary. The code below will work for males and females based upon the selection.

 

3. I made an assumption as to the field name for the user ID. I used "user_id" -  change it to the correct value in the queries and the php code that uses it.

 

4. I updated the logic for creating the checkboxes to automatically check the box for each record if the "offer" field is already true.

 

if (isset($_POST['submit1']))
{
    $pickAry = $_POST["pick"];
    echo "People chosen: {$pickAry}<br><br>\n";
    echo "<br><br>\n";

    //Run query to update PICKED/UNPICKED records
    $pickList = implode(', ', $pickAry);
    $query = "UPDATE offer SET offer = IF(user_id IN ({$pickList}), 1, 0)";
    $result = mysql_query($query, $connection);
    if (!$result)
    {
        die("Database query failed: " . mysql_error());
    } 
}
else
{
    $gender = ($_POST['Gender'] != 'Male') ? 0 : 1;
    $limit = mysql_real_escape_string($_POST['limit']);
    echo "{$_POST['Gender']}<br />\n";

    $query = "SELECT user_id, username, offer FROM users WHERE gender =\"{$gender}\" LIMIT 0, {$limit}";
    $result = mysql_query($query, $connection);
    if (!$result)
    {
        die("Database query failed: " . mysql_error());
    } 
    echo "<form method=\"post\">\n";
    while ($row = mysql_fetch_array($result))
    {
        $checked = ($row['offer']==1) ? ' checked="checked"' : '';
        echo "<input name=\"pick[]\" type=\"checkbox\" value=\"{$row['user_id']}\"{$checked}>\n";
        echo "{$row['username']}<br />\n";
    }
    echo "<input type=\"submit\" name=\"submit1\" value=\"Make Offer\" />\n";
    echo "</form>\n";
}

 

Here is an explanation of the query:

UPDATE offer
SET offer = IF(user_id IN ({$pickList}), 1, 0)

 

It is an UPDATE query, obviously. It has NO WHERE clause so it will attempt to update all records (if the update value is different than the current value). The real trick in this query is where we set the value for "offer". Basically it says set "offer" equal to and then does a comparison: if the record's "user_id" is in the list of checked records sent in the POST data it sets the value to 1, else it sets it to 0.

[wright0768]

Thank you so much. I'll test it out now and let you know if I run into any problems.  Thanks again!

[wright0768]

It looks good however, it is giving me this output:

 

People chosen: Array

 

Database query failed: Unknown column '(whatever the first username checked is' in 'field list'.

 

 

I substituted in my own values for my database and this is what it is giving me.  My table name is users, the user_id is the column that holds usernames, and offer is also a column.  I think something might just be reversed because it is looking for a specific username as a column instead of as a row.

 

[wright0768]

EDIT:

People chosen: Array

 

Database query failed: Unknown column '(whatever the first username checked is)' in 'field list'.

 

[Psycho]

Well, I told you I didn't test it.

 

Database query failed: Unknown column '(whatever the first username checked is)' in 'field list'.

 

Hmm, I see an error in that query, but I wouldn't think it would display an error on the username. Well, It shouldn't display an error on the username anyway since I told you that you should be using the user ID. As I stated you need to replace the filed name 'user_id' with whatever you are using for the user id field. There are 2 instances in the queries and one instance in the PHP code.

 

I have made a couple of corrections in the section below as well as added some additional debugging code in case the query fails again.

if (isset($_POST['submit1']))
{
    $pickList = (isset($_POST["pick"])) ? implode(', ', $_POST["pick"]) : '';
    echo "People chosen: {$pickList}<br><br>\n";
    echo "<br><br>\n";

    //Run query to update PICKED/UNPICKED records

    $query = "UPDATE users SET offer = IF(user_id IN ({$pickList}), 1, 0)";
    $result = mysql_query($query, $connection);
    if (!$result)
    {
        die("Query: {$query}<br>Database query failed: " . mysql_error());
    } 
}

[wright0768]

I know you said you didn't test it so don't worry I wasn't expecting it to necessarily work without some tinkering.  I appreciate all the help.  If 2 users are selected this gives me:

People chosen: user1, user2

 

Query: UPDATE users SET offer = IF(user_id IN (user1, user2), 1, 0)

Database query failed: Unknown column 'user1' in 'field list'

 

I'll try tweaking it to see if i can get it to work, let me know what you think

[wright0768]

$query = "UPDATE users SET offer = IF(user_id IN ('{$pickList}'), 1, 0)";

 

This does not give an error but only actually updates the database if 1 user total is selected.  The only difference is single quotes around the {$pickList}.  I think it might be something with implode in order to get the right sql syntax

[wright0768]

$pickList = (isset($_POST["pick"])) ? implode('\', \'', $_POST["pick"]) : '';

 

This change seems to make everything work.  I still have to do more testing but so far so good.  mjdamato thank you again.

[Psycho]

$query = "UPDATE users SET offer = IF(user_id IN ('{$pickList}'), 1, 0)";

 

This does not give an error but only actually updates the database if 1 user total is selected.  The only difference is single quotes around the {$pickList}.  I think it might be something with implode in order to get the right sql syntax

 

Why are you putting single quotes around $picklist? The user_id field should be a numeric field and the values from the POST data should be numeric as well. So, you don't need quotes around the values if you set up the database field correctly (should be an int type). Besides, if you did need quotes, you need them around each individual value not around the list of values.

 

So, what type of field is "user_id"?

[Psycho]

$pickList = (isset($_POST["pick"])) ? implode('\', \'', $_POST["pick"]) : '';

 

This change seems to make everything work.  I still have to do more testing but so far so good.  mjdamato thank you again.

 

It works because you are implementing a workaround for a problem that shouldn't be there to begin with. You need to be using an int type field for user_id and I suspect you are using varchar or some other text variant. You did not solve the illness you only put a band-aid on a symptom.

[wright0768]

I was originally using a varchar field named username but seeing that the script you wrote was using user_id I changed mine to user_id but kept it as varchar.  I changed the field name in my database instead of the field name in the script so everyone was dealing with the same script.  I guess we had a bit of a miscommunication but user_id (int) in your script was username (varchar) in mine. 

[Psycho]

I guess we had a bit of a miscommunication but user_id (int) in your script was username (varchar) in mine.

 

I guess I wasn't clear when I stated

3. I made an assumption as to the field name for the user ID. I used "user_id" -  change it to the correct value in the queries and the php code that uses it.

 

Username and user_id (or whatever the primary key is) are not interchangeable. You should not be using the username field as the primary key. Your table should have an auto-incrementing int field (e.g. user_id) to use as the primary key as well as a username field for, well, the username. Look at the modification I made for the checkboxes. The script will still show the username as the label next to the checkbox, but the value will be the user_id.