0

I have a form in admin page that store data in custom table, with multiple checkboxs. On form submit I need to save the checked box id with the store id it's related in a key pair way (store_id => checkbox_id, or 588 => 552, 588 => 325, 588 => 61, so each data_id can be a new line with the sotre_id as key, so later I can do select whatever id and get all of checked values with that id)

and also check if some unchecked data are saved and if so remove them; and maybe if data is already there do nothing.

What's the best way to do this ? Select every record of store_id, delete all and then just save new checked id, or check if already there but uncheck then delete and save new entries that are not already there? checkboxes won't have more than 15 entries, so not huge. In any case all of my attempts ended with

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1] INSERT INTO wp_mytable( store_id,data_id) VALUES;

     Form.php

add_action('wp_ajax_filterBrandRestrict', 'filter_brand_retailer');
add_action('wp_ajax_nopriv_filterBrandRestrict', 'filter_brand_retailer');

function filter_brand_retailer()
{

    $store_id = $_POST['retailerfilter'];
    global $wpdb;
    //var_dump($_POST);

    $query = $wpdb->prepare("SELECT data_id FROM `mytable` WHERE `store_id` = '%d'", $store_id);

    $results = $wpdb->get_results($query, OBJECT_K);

    $Brand = new WP_Query(array('post_type' => 'watch_brand'));
?>
    <?php
    if ($Brand->have_posts()) : ?>
        <div class="control-group legend_brand">
            <form method="post" action="<?php echo site_url() ?>/wp-admin/admin-post.php" contentType: false,>
                <!-- Store id -->
                <input type="hidden" name="store_ids" value="<?php echo $store_id ?>">
                <input type="hidden" name="action" value="saveBrand">
                <!--  loop on brand to create checkboxes -->
                <?php while ($watchBrand->have_posts()) : $watchBrand->the_post(); ?>
                    <label class="control control-checkbox">
                        <?php echo get_the_title(); ?>

                        <input name="data_ids" value="<?php echo get_the_ID(); ?>" <?= isset($results[get_the_ID()]->data_id)  == get_the_ID() ? 'checked' : '' ?> type="checkbox" />

                        <div class="control_indicator"></div>
                    </label>
                <?php endwhile; ?>
                <div class="submit_default">
                    <button name="Submit" class="submit_button" type="submit">Envoyer</button>
                </div>
            </form>
        </div>
<?php
        wp_reset_postdata();
    else :
        echo 'No posts found';
    endif;

    die();
}


brand.php

<div class="wrap">

    <div class="brand">
        <div class="brand_dropdown">
            <p class="brand_text">Form </p>

            <form method="POST"  action="<?php echo site_url() ?>/wp-admin/admin-ajax.php">
                <div class="select">
                    <input type="hidden" name="action" value="filterBrandRestrict">

                    <select name="retailerfilterwatch">
                        <option value="">Select retailer...</option>
                        <?php

                           $posts = new WP_Query(array('post_type' => 'retailer'));
                           while ($posts->have_posts()) : $posts->the_post();
                               wp_reset_postdata(); ?>
                            <option id="selection" value="<?php echo get_the_ID(); ?>"><?php echo get_the_title(); ?></option>

                        <?php endwhile; ?>
                    </select>


                    <button id="filter_btn">Apply filter</button>

                </div>
            </form>
        </div>
        <div class="brand__checkbox" id="responseBrand">




        </div>
function save,

    global $wpdb;


    // Get the values
    $store_id[]  = (null !== $_POST['store_ids']) ? sanitize_text_field($_POST['store_ids']) : '';
    $watch_brand_id = (null !== $_POST['data_ids']) ? sanitize_text_field($_POST['data_ids']) : ''; // Empty value if data not set
    // die(var_dump(['store_ids']));
    // die(gettype($watch_brand_id));
    $attribut_table = $wpdb->prefix . 'jr_restriction';

    $format = array('%d', '%d', '%d');
    // print_r($store_id);

    // get the saved values
    $itemsInDB = $wpdb->prepare("SELECT data_id FROM `wp_of_jr_restriction` WHERE `store_id` = %d", $store_id);

    $results = $wpdb->get_results($itemsInDB, ARRAY_A);
    $match = array_intersect($results, $_POST);
    $nomatch = array_values(array_diff($results, $_POST['data_ids']));

    
    $insertArray = array($store_id[0] => $nomatch);

    $insertQuery = "INSERT INTO `$attribut_table`( store_id,data_id) VALUES";

    foreach ($insertArray as $an_item) {
        $insertQuery .= $wpdb->prepare(
            "(%s, %s, %d),",
            $an_item['store_id'],
            $an_item['nomatch'],
        );
    }

    $q = rtrim($insertQuery, ',') . ';';
    //die(print_r($q));

    $wpdb->query($q);


thanks

3
  • Hi @Francis. The WordPress documentation for wp_reset_postdata() says that it should be called, After looping through a separate query. In brand.php it is called in the middle of the while loop. Your first suggestion (wiping out database entries by store_id then repopulating using checkbox values) sounds the simplest. The call to $wpdb->prepare within the foreach loop has 3 conversion specifiers, but only two arguments. This should produce an error.
    – kofeigen
    May 1, 2023 at 0:58
  • Hi @kofeigen, thanks for your reply, i've corrected my code according to your suggestions.But deleting everything and saving values again won't do performance issues ? thanks
    – Francis
    May 2, 2023 at 9:16
  • By "deleting everything" I assume you mean deleting only those rows corresponding to a specific store_id. The alternative sounds like it requires more transactions. It doesn't sound like it would be too difficult for you to test both scenarios if there is a concern about performance.
    – kofeigen
    May 2, 2023 at 17:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.