如何使用Ajax，PHP和JQuery检查多个输入复选框？

Question

I'm making a simple Category management for products in my database and I am having a hard time getting a good algorithm to check the checkboxes of items that belong to a specific category from my database tables.

Basically I am using a JQuery Ajax call to the following PHP file:

<?php 
    include_once('config.php');
    $id = $_POST['fetchID'];

    //Create PDO Object
    $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
    //Set Error Handling for PDO
    $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    //Query

    $sql = "SELECT prod_cat.cat_id FROM prod_cat LEFT JOIN products ON products.id = prod_cat.product_id LEFT JOIN category on category.cat_id = prod_cat.cat_id WHERE id = $id";
    //Prepare Statement
    $stmt = $con->prepare($sql);
    $stmt->execute();

    echo "<button id='backBtn-$id' class='backBtn'></button>
    <!-- Left Column -->
    <section class='adminLCol'>
        <h4>Menu</h4>                   
        <form class='updateProductCategory' id='updateProductCat-$id' method='post' action=''>
          <input class='input_class_checkbox' type='checkbox' id='basicCollection_$id' name='cat[]' value='Basic Collection' checked>Basic Collection<br />
          <input class='input_class_checkbox' type='checkbox' id='teesAndTanks_$id' name='cat[]' value='Tees and Tanks' checked>Tees and Tanks<br />
          <input class='input_class_checkbox' type='checkbox' id='shirts_$id' name='cat[]' value='Shirts'>Shirts<br />
          <input class='input_class_checkbox' type='checkbox' id='topsAndBlouses_$id' name='cat[]' value='Tops and Blouses'>Tops and Blouses<br />
        </form>
    </section>  

    <!-- Right Column -->
    <div class='adminRCol'>
        <section class='adminRContent'>
            <h4>Visibility</h4>                 
            <form>
              <input class='radio_selection' type='radio' name='visibility' value='Enable' checked>Enable<br />
              <input class='radio_selection' type='radio' name='visibility' value='Disable'>Disable
            </form>
        </section>
    </div>";
?>

This is how the category table looks like:

+-------------+-------------------+
|   cat_id    | name              |
+-------------+-------------------+
|           1 | Basic Collection  |
|           2 | Tees and Tanks    |
|           3 | Shirts            |
|           4 | Tops and Blouses  |
+-------------+-------------------+

This is how my products table looks like:

+----+-----------------+------------------+
| id | name            | description      |
+----+-----------------+------------------+
|  2 | Product One     | Made in Portugal |
|  3 | Product Two     | Made in Brazil   |
+----+-----------------+------------------+

And this is how my prod_cat table looks like:

+------------+-------------+
| prod_id    | cat_id      |
+------------+-------------+
|          2 |           1 |
|          3 |           1 |
|          2 |           2 |
|          2 |           4 |
+------------+-------------+

The PHP file above, connects to my database and fetches the categories that the product belongs to. So this is how the $sql output looks like for products.id = 2:

+-------------+
|    cat_id   |
+-------------+
|           1 |
|           2 |
|           4 |
+-------------+

Now, as you can see these are the 3 categories that should be checked in my form which has all the checkboxes. This is where I get stuck. What would be a good approach to check those checkboxes given that only these 3 categories are to be checked?

If you need more detail regarding the question, please drop a comment.

Thanks

Edit: I did a print_r on that select statement for $stmt->fetchAll() and the following is the resulting array I get. I'm not sure what to do with it. I'm posting it here, if it could be of any use:

Array ( [
    0] => Array ( 
            [cat_id] => 1 [0] => 1 
        ) 
    [1] => Array ( 
            [cat_id] => 2 [0] => 2 
        ) 
    [2] => Array ( 
            [cat_id] => 4 [0] => 4 
        ) 
) 

Answer 1

If I understand the question correctly, you are using outer joins inappropriately.

I would use:

$sql = "SELECT name FROM category "
. "JOIN prod_cat ON prod_cat.cat_id = category.cat_id "
. "WHERE prod_cat.prod_id = ?";

Get the results:

$stmt = $con->prepare($sql);
$stmt->execute(array($id));
$result = $stmt->fetchAll();

Turn the 2-dimensional array into a 1-dimensional array:

$array = array_map('current', $result);

And then check per checkbox if the product is in the specific category, e.g.:

if (in_array('Basic Collection', $array)) {
    echo 'checked';
}

No Ajax or jQuery needed.

Incidentally, there is little point in using a prepared statement when you are going to put user input in the query like that.