Inserting multiple checkbox values to MySQL

Question

I know there are multiple questions here on SO regarding this same issue already and I've looked into them but didn't quite get a satisfying answer. So here goes my question,

I have a form which consists of a few textboxes and checkboxes. It looks like this,

enter image description here

The user can select multiple checkboxes. I'm trying to insert the values(not the displaying text string) of those checkboxes into a MySQL table. It should look like this,

enter image description here

One Service ID(SID) can have multiple Locations(Loc_Code). Those location codes (CO, GQ) are the values of the checkboxes.

I've written this following code so far.

<html>
<head>
</head>
<body>
<?php
require_once("db_handler.php");

$conn = iniCon();
$db = selectDB($conn);

/* Generating the new ServiceID */
$query = "SELECT SID FROM taxi_services ORDER BY SID DESC LIMIT 1";
$result = mysql_query($query, $conn);
$row = mysql_fetch_array($result);
$last_id = $row["SID"];

$id_letter = substr($last_id, 0, 1);
$id_num = substr($last_id, 1) + 1;
$id_num = str_pad($id_num, 3, "0", STR_PAD_LEFT);
$new_id = $id_letter . $id_num;

//Selecting locations
$query = "SELECT Loc_Code, Name FROM districts";
$result = mysql_query($query, $conn);

$count = mysql_num_rows($result);
?>

<?php
if(isset($_POST["savebtn"]))
{
    //inserting the new service information
    $id = $_POST["sid"];
    $name = $_POST["name"];
    $cost = $_POST["cost"];
    if($_POST["active"] == "on") $active = 1; else $active = 0;

    $query = "INSERT INTO taxi_services(SID, Name, Cost, Active) VALUES('$id', '$name', '$cost', '$active')";
    $result = mysql_query($query, $conn);

    //inserting the location details
    for($j = 0; $j < $count; $j++)
    {
        $loc_id = $_POST["checkbox2"][$j];
        $query = "INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')";
        $result5 = mysql_query($query, $conn);
    }

    if (!$result || !$result5)
    {
        die("Error " . mysql_error());
    }
    else
    {
?>
        <script type="text/javascript">
            alert("Record added successfully!");
        </script>
<?php
    }   
    mysql_close($conn); 
}
?>

<div id="serv">
<b>Enter a new taxi service</b>
<br/><br/>
    <form name="servForm" action="<?php $PHP_SELF; ?>" method="post" >
        <table width="300" border="0">
          <tr>
            <td>Service ID</td>
            <td><input type="text" name="sid" readonly="readonly" value="<?php echo $new_id; ?>" style="text-align:right" /></td>
          </tr>
          <tr>
            <td>Name</td>
            <td><input type="text" name="name" style="text-align:right" /></td>
          </tr>
          <tr>
            <td>Cost</td>
            <td><input type="text" name="cost" style="text-align:right" onkeypress="return isNumberKey(event)" /></td>
          </tr>
          <tr>
            <td>Active</td>
            <td><input type="checkbox" name="active" /></td>
          </tr>
        </table>
</div>

<div id="choseLoc">
Locations <br/><br/>
    <table border="0">
        <?php
        $a = 0;
        while($row = mysql_fetch_array($result))
            {
            if($a++ %5 == 0) echo "<tr>";
            ?>
            <td align="center"><input type="checkbox" name="checkbox2[]" value="<?php echo $row['Loc_Code']; ?>" /></td>
            <td style="text-align:left"><?php echo $row["Name"]; ?></td>
        <?php
        if($a %5 == 0) echo "</tr>";
            }
        ?>
    </table>
</div>
<br/>
<div id="buttons">
    <input type="reset" value="Clear" /> <input type="submit" value="Save" name="savebtn" />
    </form>
</div>

</body>
</html>

It inserts the Service details correctly. But when it inserts location data, a problem like this occurs,

enter image description here

I selected 4 checkboxes and saved. The 4 location codes gets saved along with the service ID. But as you can see from the screenshot above, a bunch of empty rows gets inserted too.

My question is how can I stop this from happening? How can I insert the data from the checkboxes only I select?

Thank you.

Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables. — eggyal
– eggyal, Commented Aug 25, 2012 at 12:59
Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions: This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. — eggyal
– eggyal, Commented Aug 25, 2012 at 13:00
Have you tried checking the $_POST variable to check what gets submitted? — Madara's Ghost
– Madara's Ghost, Commented Aug 25, 2012 at 13:02
@eggyal: Ah yes, I'm aware of SQL injection. But this is just a small front end for a database which's only user is me. Its not online. But thanks for the heads up :) — Isuru
– Isuru, Commented Aug 25, 2012 at 13:03
@Truth: you mean the $_POST at this line? $loc_id = $_POST["checkbox2"][$j]; — Isuru
– Isuru, Commented Aug 25, 2012 at 13:04

eggyal · Accepted Answer · 2012-08-25 13:03:56Z

10

One way would be to only loop over the checkboxes that were submitted:

//inserting the location details
foreach($_POST["checkbox2"] as $loc_id)
{
  $query = "INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')";
  $result5 = mysql_query($query, $conn);
}

I reiterate here the SQL injection warning given above: you would be much better off preparing an INSERT statement and then executing it with parameters. Using PDO, it would look something like:

//inserting the location details
$stmt = $dbh->prepare('
  INSERT INTO service_locations(SID, Loc_Code) VALUES(:id, :loc)
');
$stmt->bindValue(':id', $id);
$stmt->bindParam(':loc', $loc_id);
foreach($_POST["checkbox2"] as $loc_id) $stmt->execute();

answered Aug 25, 2012 at 13:03

eggyal

127k18 gold badges221 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Isuru Over a year ago

Works beautifully. :) Can you please explain this one line? foreach($_POST["checkbox2"] as $loc_id) How $loc_id gets the value of the checkbox? | I'm currently studying PDO :) When I get the hang of it, I will be sure to use it when I'm working with PHP and MySQL. Thank you.

eggyal Over a year ago

@nK0de: Read about foreach.

leoyfm · Accepted Answer · 2012-08-25 13:14:48Z

0

from these sentence:

for($j = 0; $j < $count; $j++)
    {
        $loc_id = $_POST["checkbox2"][$j];
        $query = "INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id')";
        $result5 = mysql_query($query, $conn);
    }

i find the problem is that the value of loc_code must be the last loction you selected. because in this loop, the value of loc_code will replaced everytime. if you want to insert all the location, you should put it on the one sentence, like INSERT INTO service_locations(SID, Loc_Code) VALUES('$id', '$loc_id'), the value of $loc_id should be CO,GQ,GL.

answered Aug 25, 2012 at 13:14

leoyfm

2514 silver badges16 bronze badges

Comments

Tieran · Accepted Answer · 2012-08-25 13:04:17Z

-5

This is happening because the checkboxes that weren't ticked still get posted, they just have empty values. Before you do your insert to service_locations just check if $loc_id is empty or not, only do the insert if it isn't.

answered Aug 25, 2012 at 13:04

Tieran

1,0767 silver badges9 bronze badges

2 Comments

eggyal Over a year ago

Not correct. It is happening because the loop is limited by the number of locations in the database.

Madara's Ghost Over a year ago

No, checkboxes which aren't getting ticked do not get submitted by the browser.

Collectives™ on Stack Overflow

Inserting multiple checkbox values to MySQL

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related