INSERT checkbox into database using WHERE clause

Question

I am trying to insert the checkbox into the database using WHERE clause. The code redirects without any error. But it is not updating into the database. When i uncheck the checkbox, the row gets deleted without any issue for the id. It is just the INSERT i am having the issue. I cannot seems to find the problem here.

The code below works without using the WHERE clause.

$bId = $_POST['fm_id']; // this is coming from the hidden input field
if (isset($_POST['fm_day_hid'], $_POST['fm_day'])) {
    $ckb = $_POST['fm_day'];
    for ($i = 0; $i < count($ckb); $i++){
        if(!empty($ckb)){
            $arrayMovies = $ckb[$i];
            $sql = "INSERT INTO checkbox_batchdays (cbx_days) 
            SELECT * FROM (SELECT '$arrayMovies') AS tmp WHERE NOT EXISTS 
            (SELECT cbx_days FROM checkbox_batchdays WHERE cbx_days = '$arrayMovies') 
            WHERE cbx_batchid='$bId'";

            $query = mysqli_query($con, $sql);
        }
    }
    foreach($_POST['fm_day_hid'] as $moviesHidden) {
        if(!in_array($moviesHidden, $_POST['fm_day'])){
            $sql = "DELETE FROM checkbox_batchdays 
            WHERE cbx_days='$moviesHidden' AND cbx_batchid = '$bId'";

            $query = mysqli_query($con, $sql);
        }
    }
}
else {
    $sql = "DELETE FROM checkbox_batchdays WHERE cbx_batchid = '$bId'";
    $query = mysqli_query($con, $sql);
}

if( $query ) {
    header("Location: ../../batches.php");
}
else {
    echo "Not Deleted!";
}

How much rows eou get with the inside select query. You can only insert only one row with this kind of query, else you need a store procedure — juanbits, Aug 07 '18 at 03:49
The above query works with multiple rows. Only the `WHERE` clause i have the issue. — Dexter, Aug 07 '18 at 03:50
Maybe the second where outside the ")" is the problem, you only can have one where statement by query. Try changing the second where to AND — juanbits, Aug 07 '18 at 03:54
Just explaining that I downvoted because there's a severe lack of information here. What does "It is not working" mean exactly - is there an error? Is it just a blank page? Also, where does fm_id come from? What do the variables look like? Have you echoed out the final query before running it? What's the output of mysqli_error($con) afterwards if you echo it? Is fm_id a checkbox on a form? Please update the question with more details and context and I'll change it to an upvote. — jhilgeman, Aug 07 '18 at 03:58
Did you get any error? Try run the select' queries to catch errors for each query. Or you can use: mysqli_query(...) or die (mysqli_error($con)); — juanbits, Aug 07 '18 at 03:58
Also bear in mind that checkbox inputs are not sent at all as part of the POST data if they are not checked. In other words, if a checkbox isn't checked, then it's as if it doesn't even exist on the form when you submit the form. Not sure if it applies here, since there's not enough detail to know, but just throwing it out there. — jhilgeman, Aug 07 '18 at 04:00
Change each mysqli_query() call so that you first echo out $sql, then you mysqli_query(), then you echo mysqli_error($con). Then disable the header redirection and retest the script and see what you get. — jhilgeman, Aug 07 '18 at 04:10
`INSERT INTO checkbox_batchdays (cbx_days) SELECT * FROM (SELECT 'Sun') AS tmp WHERE NOT EXISTS (SELECT cbx_days FROM checkbox_batchdays WHERE cbx_days = 'Sun') WHERE cbx_batchid='3'INSERT INTO checkbox_batchdays (cbx_days) SELECT * FROM (SELECT 'Thu') AS tmp WHERE NOT EXISTS (SELECT cbx_days FROM checkbox_batchdays WHERE cbx_days = 'Thu') WHERE cbx_batchid='3'` — Dexter, Aug 07 '18 at 04:17
Obligatory: https://stackoverflow.com/q/60174/2864740 (also, separating data out from the queries also makes it 'easier' to compartmentalize problems) — user2864740, Aug 07 '18 at 04:46
@user2864740 we do understand the vulnerability of the code. we are just experimenting internally. The code will be on PDO once we go to the final stage. — Dexter, Aug 07 '18 at 04:55

score 0 · Answer 1 · answered Aug 07 '18 at 04:33

Here's the answer.

for ($i = 0; $i < count($ckb); $i++){
    if(!empty($ckb)){
        $arrayMovies = $ckb[$i];
        $sql = "INSERT INTO checkbox_batchdays (cbx_batchid, cbx_days)
                    SELECT * FROM
                        (SELECT '$bId', '$arrayMovies') AS tmp
                    WHERE NOT EXISTS
                        (SELECT cbx_batchid, cbx_days FROM checkbox_batchdays 
                        WHERE cbx_days = '$arrayMovies' AND cbx_batchid='$bId')";
        $query = mysqli_query($con, $sql);
    }
}

INSERT checkbox into database using WHERE clause

1 Answers1