current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question
4  
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 Aug 25 '12 at 12:59
4  
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 Aug 25 '12 at 13:00
    
Have you tried checking the $_POST variable to check what gets submitted? –  Second Rikudo Aug 25 '12 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 Aug 25 '12 at 13:03
    
@Truth: you mean the $_POST at this line? $loc_id = $_POST["checkbox2"][$j]; –  Isuru Aug 25 '12 at 13:04
show 1 more comment

3 Answers

up vote 7 down vote accepted

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();
share|improve this answer
    
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. –  Isuru Aug 25 '12 at 13:20
    
@nK0de: Read about foreach. –  eggyal Aug 25 '12 at 13:27
add comment
up vote -4 down vote

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.

share|improve this answer
1  
Not correct. It is happening because the loop is limited by the number of locations in the database. –  eggyal Aug 25 '12 at 13:05
1  
No, checkboxes which aren't getting ticked do not get submitted by the browser. –  Second Rikudo Aug 25 '12 at 13:06
    
You're both quite right, my mistake. Now where's the delete button... –  Tieran Aug 25 '12 at 13:09
add comment
up vote 1 down vote

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.