Jump to content

Multiple Check-box issue - HELP Needed..


dapcigar

Recommended Posts

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

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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....

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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.";
Link to comment
Share on other sites

 

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.