Insert multiple checkbox answers into database

Question

I have a checkbox list and I want to insert each item checked into the database. Since it is a checkbox, I only have one value to pass to the database, but I am also passing a variable (primary key) grabbed from a previous database insertion.

My problem is that I can't get it to post to the database. I have tried multiple ways to do this through posts here and nothing seems to work. I am at a loss. I am also fairly new to PHP and mysqli, so I am sure there are better ways to do what I am trying to do, so please help me out.

The table consists of three columns (school_id, graduate_id, schoolName). The school_id is the AI primary key and graduate_id will be populated with the variable $graduateID grabbed from the previous query. Here is what I have:

if (isset($_POST['school'])) {
$school=$_POST['school'];
$schoolQuery="";
foreach($school as $value) {
    if(!$schoolQuery) {
        $schoolQuery="INSERT INTO schoolReunion (graduate_id, schoolName) VALUES ($graduateID, '$value')";
    } else {
        $schoolQuery .= ", ($graduateID, '$value')";
        mysqli_store_result($schoolQuery);
    }
}
$schoolQuery .=";";
if (mysqli_multi_query ($schoolQuery)) {
    echo "Files have been updated successfully.";
} else {
    echo "Error: " . $schoolQuery . "<br/>" . mysqli_error($dbc);
}

}

When the error code prints out, it looks fine, but it just won't post anything and gives me no errors. Can you help me?

Note that you are only building 1 query that inserts multiple data sets, so you don't need `mysqli_multi_query`. And you have an sql injection problem. You should also get rid of the `mysqli_store_result` statement. — jeroen, Apr 06 '15 at 15:00
In your if statement, you set up the opening part of the insertion (INSERT INTO...) but in the else statement, you don't have any a true query to execute. Logic is, you need to execute "this" query, or "that" query — MrTechie, Apr 06 '15 at 15:00
@MrTechie No, in the `else` statement a new set gets added to the existing string. — jeroen, Apr 06 '15 at 15:01

score 0 · Answer 1 · edited May 23 '17 at 12:28

@Maximus & @Eko Junaidi Salam, while you may be right, I don't see any evidence that there are issues with the front-end/form.

@jeroen, you are totally correct.

CarR is dealing with a single concatenated query, so mysqli_query is suitable.
I recommend wrapping the user-supplied variable in mysqli_real_escape_string() to cover the query vulnerability.
mysqli_store_result() is to be used on queries that return a result set; mysqli_affected_rows() is the function that yields a measure of success with INSERT/UPDATE/DELETE type queries.
Lastly, you are right to correct MrTechie. I don't think he realized the query concatenation.

Assuming the form is delivering the necessary values, I'll suggest a new query build section:

$schoolQuery="";
foreach($school as $value){
    if(!$schoolQuery) {
        $schoolQuery="INSERT INTO schoolReunion (graduate_id,schoolName) VALUES ";
    }else{
        $schoolQuery.=",";
    }
    $schoolQuery.="('$graduateID','".mysqli_real_escape_string($dbc,$value)."')";
}
$schoolQuery.=";";

$graduateID IS NOT escaped because I am assuming it comes from a safe place; $value IS escaped because it comes from user input.

Now to deliver the built query. Your code has syntax and logic errors, so try this:

if($schoolResult=mysqli_query($dbc,$schoolQuery)){
    $total_rows=mysqli_affected_rows($dbc);
    echo $total_rows," file",($total_rows!=1?"s have":" has")," been added.";
    // if($total_rows<1){echo "Query Logic Error, @ $schoolQuery";}
}else{
    // echo "Query Syntax Error @ $schoolQuery<br>".mysqli_error($dbc);
}

Uncomment the error lines while you are testing, then re-comment or delete for production. This should sufficiently fix all the issues that are apparent in your snippet of code. Beyond that are the assumed issues...

Since you mention that you are employing a previous query to declare $graduateID, I will assume you have acquired the value via a SELECT query. Be sure to check that you have used

mysqli_free_result($graduateResult);    // I assumed this variable name

to avoid any conflicts with subsequent queries. This is sometimes overlooked.

mysqli_multi_query() is best used when you are dealing with queries that are dependent on a prerequisite query. It seems your $schoolQuery is dependent on the success of what I will call "$graduateQuery". If you wish, you could use mysqli_multi_query() to run $graduateQuery then $schoolQuery. To help you with this implementation, I would need to see more of your code. If you wish to go down that road, it might be best to message me directly or start a new post (after you've had a try at it yourself.) Here is half of your job done: Strict Standards: mysqli_next_result() error with mysqli_multi_query

Insert multiple checkbox answers into database

1 Answers1