Multiple Check-box issue - HELP Needed..

[dapcigar]

Hello all,

 

I have a table i created and i want the data to be displayed using check box. Also want users to be able to select multiple checkboxes and save the result back into the DB.. Am so confused right now, i don't know how to accomplish this. Anyone can help me with a syntax?

 

thanks in advance

[Ch0cu3r]

 

Am so confused right now,

What specifically are you stuck with?

 

When you get the data from the database you loop through results and generate a checkbox, assigning each value returned by the query to each checkbox as you go

 

When submitting multiple checkboxes, make sure you append the name of the checkbox with square brackets []. When the form is submitted the values for the selected checkboxes will be submitted as an array. To insert these values into the database you loop over the values in the array.

[grissom]

It's a quick and sweet Google search : http://www.html-form-guide.com/php-form/php-form-checkbox.html

[dapcigar]

What specifically are you stuck with?

 

When you get the data from the database you loop through results and generate a checkbox, assigning each value returned by the query to each checkbox as you go

 

When submitting multiple checkboxes, make sure you append the name of the checkbox with square brackets []. When the form is submitted the values for the selected checkboxes will be submitted as an array. To insert these values into the database you loop over the values in the array.

Thanks a lot.. followed that and i was able to display the saved data with a checkbox. the problem am having now is saving back the selected result back to the DB. here's my code below

// For Display 

<form action="save_comp.php" method="post">
<?php

include ('mysql_connect.php');

$sql = mysql_query("SELECT * FROM competency ");
//$row = mysql_fetch_array($sql);

while($row = mysql_fetch_array($sql))
{

echo"<input type='checkbox' name='comp[]' value= ".$row['id']." /> ".$row['competency']." <br />";


}

?>
<input name="submit" type="submit" value="submit" />
</form>

and for saving into the DB,

<?php




$insStr = ''; 
foreach($_POST['comp'] as $val){ $insStr .=$val.","; }
mysql_query("INSERT INTO competency_result (result) VALUES ( '$insStr' )"); 



?>

Please, result is not saving.

[maxxd]

Turn on error checking. You should be getting an error regarding an extra comma at the end of your $insStr.

 

That having been said, the entire issue could be avoided - and you could have safer and easier database interaction that will still work next year - by moving to PDO or MySQLi. the mysql_* functions have been deprecated for something like a decade now and are slated to be removed from the language with version 7, which I believe drops later this summer.

[dapcigar]

Thanks.. figured it out.. i can now save the result into a database.

now i have another issue. i want to display the result in a table showing the result. the problem is i only saved the id of the checkboxes. so when i try to display the result, it just shows me the id.

 <?php
									$res= mysql_query("SELECT * FROM competency_result WHERE user_id = '$user'")or die(mysql_error());

while($row = mysql_fetch_array($res))
{
	                              

	

echo"<tr>";
    
    echo"<td> $row[result]</td>";                
  
	
	
	
	?>

The output comes out as 1,5,17. Help please

[ginerjm]

For the output simply do a new query that retrieves the user's data and build your table from that. This avoids being concerned about capturing other data from the input and ensures that you get the absolute current correct data from the db.

[dapcigar]

For the output simply do a new query that retrieves the user's data and build your table from that. This avoids being concerned about capturing other data from the input and ensures that you get the absolute current correct data from the db.

Please, could you help me with the syntax?. what is stored in the DB is just the ID. how do i separate the Id's and display the output?

[Barand]

You should be storing each id in a separate record, not in a single record as a comma-separated string, and probably with the id of the user who selected them.

[dapcigar]

I was able to manipulate it by using explode function. the issue now is that when i try to view the reslt, it's only displaying the first one in the array. i figure my loop is not running properly. could you help me take a look at it

$separate = explode(" ", $row['result']);

for($i = 0; $i < count($separate); $i++){
	
	$quest=  mysql_query("SELECT * FROM competency WHERE id = '$separate[$i]'")or die(mysql_error());
	
	
	
	
	while($rows = mysql_fetch_array($quest))
{
	echo"<tr>";
	
	 echo"<td> $rows[competency]  <br /></td>";  
}
}
	

thanks in advance

[Barand]

Here's how you should be doing it, storing the competency ids in a separate table, one per row.

+----------------+                                +-----------------+  
|    user        |                                |  competency     |
+----------------+                                +-----------------+
|   user_id   PK |--+                         +---|  comp_id     PK |
|   username     |  |                         |   |  comp_name      |
+----------------+  |                         |   +-----------------+
                    |                         |
                    |    +-----------------+  |
                    |    | user_competency |  |
                    |    +-----------------+  |
                    +---<|  user_id   PK   |  |
                         |  comp_id   PK   |>-+
                         +-----------------+     
            

Then, to list the competencies for a user ($user)

SELECT c.compname
FROM user_competency uc
  INNER JOIN competency c USING (comp_id)
WHERE uc.user_id = $user

[Muddy_Funster]

first - STOP running queries inside loops - it's bad practice, resource intensive (comparatively speaking) and in some environments it will get your script blocked as a security risk.

 

second - as was previously suggested: STOP writing new code using mysql_<xxx> it's out of date and will be removed very soon.

 

third - be attentive with your operators :  a single = sign is an assignment operator (that which is on the left becomes that which is on the right) and == is a comparison operator (is that which is on the left equal to that which is on the right)  mixing these up will make your loops and other code misbehave....

[dapcigar]

 

Here's how you should be doing it, storing the competency ids in a separate table, one per row.

+----------------+                                +-----------------+  
|    user        |                                |  competency     |
+----------------+                                +-----------------+
|   user_id   PK |--+                         +---|  comp_id     PK |
|   username     |  |                         |   |  comp_name      |
+----------------+  |                         |   +-----------------+
                    |                         |
                    |    +-----------------+  |
                    |    | user_competency |  |
                    |    +-----------------+  |
                    +---<|  user_id   PK   |  |
                         |  comp_id   PK   |>-+
                         +-----------------+     
            

Then, to list the competencies for a user ($user)

SELECT c.compname
FROM user_competency uc
  INNER JOIN competency c USING (comp_id)
WHERE uc.user_id = $user

This  would make it a whole lot easier for me. my challenge now is how to save the result of a multiple check-box as a separate record.

[Muddy_Funster]

It should look something like the following quick sample, although you should apply some validation on each $value and to $_POST['user_id'] before appending it to the query string:

foreach($_POST['multiBox'] as $record=>$value){
  (!isset($values)) ? $values = "({$_POST['user_id']}, {$value})" : $values .= ", ({$_POST['user_id']}, {$value})";
}
$qry = "INSERT INTO user_competency (user_id, comp_id) VALUES ".$values;
 
//example using PDO:
$con = new PDO("mysql:host=<yourServerAddress>;dbname=<nameOfDB>","<DBuserName>","<DBpassword>");
$affectedRows = $con->exec($qry);
($affectedRows === 0) ? echo "Either there was nothing to insert, or something went wrong!" : echo "Success! {$affectedRows} were added to the database.";

[maxxd]

 

It should look something like the following quick sample, although you should apply some validation on each $value and to $_POST['user_id'] before appending it to the query string:

foreach($_POST['multiBox'] as $record=>$value){
  (!isset($values)) ? $values = "({$_POST['user_id']}, {$value})" : $values .= ", ({$_POST['user_id']}, {$value})";
}
$qry = "INSERT INTO user_competency (user_id, comp_id) VALUES ".$values;
 
//example using PDO:
$con = new PDO("mysql:host=<yourServerAddress>;dbname=<nameOfDB>","<DBuserName>","<DBpassword>");
$affectedRows = $con->exec($qry);
($affectedRows === 0) ? echo "Either there was nothing to insert, or something went wrong!" : echo "Success! {$affectedRows} were added to the database.";

 

Personally, I'd go a step further and use PDO's prepared statement for added security, like so:

foreach($_POST['multiBox'] as $val){
	$tmp['user_id'] = $_POST['user_id'];
	$tmp['comp_id'] = $val;
	$vars[] = $tmp;
}
$qry = "INSERT INTO user_compentency
		(user_id, comp_id)
	VALUES
		(:user_id, :comp_id)
	";
try{
    $sql = $conn->prepare($qry);
    $numRows = 0;
    foreach($vars as $insert){
        $numRows += $sql->execute($insert);
    }
    print("<p>There were {$numRows} inserted into the database!</p>");
}catch(PDOException $e){
    print("<p>Oops! There was an issue - this is the message: {$e->getMessage()}</p>");
}

Of course, this assumes you're using PDO and have set the error handling to PDO::ERRMODE_EXCEPTION.

 

Also, note that this is completely untested code and I'm still on my first cup of coffee, so there very well could be a typo or logical glitch in there...

[Muddy_Funster]

@maxxd That's going back to running the db transactions within in a loop... Something I personally disagree with. 

 

Also, it could just be me, but it looks like you're not binding the parameters for the prepared statement?

[maxxd]

You only need to prepare the statement one time, then you can run the execute through the loop - the resource is still allocated, so it doesn't eat any additional space. The parameters are bound on the execute() call using the $insert variable (current iteration of the $vars array). It's one of the main reasons I like PDO over MySQLi.

[Muddy_Funster]

Yeah, just me then :-D  I didn't realise that PDO could use implicit binding on named placeholders.  Example #5 on the manual page is more along the strategy I would take.

[maxxd]

And it's a completely viable strategy; I just typically figure why go through the process of testing and sanitizing the user input to avoid SQL injection when you can use prepared statements and it's not an issue.

 

That having been said, the other caveat for my method is that you only get that security if you set PDO::ATTR_EMULATE_PREPARES to true, as I believe it's false by default.

[Barand]

Umm.. isn't that the other way round? You would need to turn EMULATE_PREPARES off  for truly prepared statements.

[maxxd]

Umm.. isn't that the other way round? You would need to turn EMULATE_PREPARES off  for truly prepared statements.

 

Yup. My bad.