Can't update DataBase with checkbox data

Question

I have a list of 4 images of an item. One of them should show up in another page as a link from that page to the item page.

I want to be able to check a box so that this one will be the main pic and will show up in the category page.

this is the code of the form:

$all_pics_of_item = fetch_all_pics_of_item($item_id);
//print_r($all_pics_of_item);
if(is_array($all_pics_of_item))
{
    echo '<ul>';
    foreach($all_pics_of_item as $key=>$val)
    {
        if ($val['pics_main']=='yes')
        {
         $set_checked = "checked";
         $action = true;
        }
        else
        {
        $set_checked = "";
        $action = false;
        }
        echo '<li style="float: left;margin:10px;border: 1px solid #000;padding:10px;">';
        echo '<img style="width:120px;height:120px;" src="../../gallery_images/thumbs/'.$val['pics_source'].'">';
        echo '<br>'.$val['pics_name'];
        echo '<br><a href="delete_pic.php?picsrc='.$val['pics_source'].'&picid='.$val['pics_id'].'&iid='.$item_id.'"><div class="delet"><b>x</b></div></a>';
        echo '<br><form method="post" action="update_main_pic.php" enctype="text/plain" >
              Show in cat. page<input type="checkbox" class="myCheckbox" name="main" value="no"'.$set_checked.'&action='.$action.' data-picid="'.$val['pics_id'].'" data-itemid="'.$item_id.'" />
              </form>';
        echo '</li>';
    }   
    echo '<ul>';
}

Here is the AJAX and script:

 $(document).ready(function(){

$(':checkbox').click(function() {
     $(':checkbox').not(this).removeAttr('checked');

    var picid = $(this).attr('data-picid');
    var itemid = $(this).attr('data-itemid');
    var action = $(this).is(':checked');
    //if((this).attr('checked',true))
    //{
    //  var action = true;
    //}
    //else
    //  {
    //      var action = false;
    //  }


    $.ajax({
        url: "ajax_update_main_pic.php",
        type: "POST",
        data: "itemid=" + itemid + "&picid=" + picid + "&action=" + action,
        timeout:5000,
        dataType: "html",
        beforeSend:function(){


        },
        error: function(){
            alert('Problem !');
        },
        success: function(msg){
            if(msg == 'no')
            {

            }
            else
            {

            }

        },
        complete: function(){

        }
    })

});  

}); //END READY

Here is the update function:

<?php

require_once "../../db.php";
require_once "../../functions.php";

if(isset($_POST['itemid']) && isset($_POST['picid']) && isset($_POST['action']))
{
$item_id = $_POST['itemid'];
$pic_id = $_POST['picid'];
$action = $_POST['action'];
}
else
{
header('location: upload_image.php');
    die();
}


if($action == 'true')
{
$pic_show = 'yes';
}
else
{
$pic_show = 'no';
}
//print_r($pic_show);
function update_main_pic($item_id, $pic_id, $pic_show )

{
    global $db;

    try
    {
        $sql = "
                UPDATE pics SET
                pics_main = :pic_show
                WHERE pics_id = :pic_id AND pics_items_id = :item_id

               ";       

        $stmt = $db->prepare($sql);
        $stmt->bindParam(':pics_id', $pic_id, PDO::PARAM_INT);
        $stmt->bindParam(':pics_items_id', $item_id, PDO::PARAM_INT);
        $stmt->bindParam(':pics_main', $pic_show, PDO::PARAM_STR);
        $stmt->execute();

        return true;    
    }
    catch(Exception $e) 
    {
       return false;        
    }
}



$result = update_main_pic($item_id, $pic_id, $pic_show );

if($result == false)
{
    die('Problem updating pics');
}
else
{
    header('location: upload_image.php?iid='.$item_id);
    die();
}

?>

I always get 'Problem updating pics'

It looks like only the checked checkbox is transmitted, while I want that the column PIC_MAIN will show "yes" if this is the one chosen and "no" foe all other pics

Answer 1

The issue lies with your binding.

You sql has the following name variables :pic_show , :pic_id and :item_id but you are binding :pics_main', :pics_items_id and :pics_id.

Change your binding to:

$sql = "
        UPDATE pics SET
        pics_main = :pic_show
        WHERE pics_id = :pic_id AND pics_items_id = :item_id

       ";       

    $stmt = $db->prepare($sql);
    $stmt->bindParam(':pic_id', $pic_id, PDO::PARAM_INT);
    $stmt->bindParam(':item_id', $item_id, PDO::PARAM_INT);
    $stmt->bindParam(':pic_show', $pic_show, PDO::PARAM_STR);