Combine the results of several select boxes by using PHP and MySQL in WordPress

Question

I have 4 dynamic dependent select boxes, now I want to combine the result of the 4 selects into a query. I have all the relative code below.

font-end part of the select boxes

<form class="select-boxes" action="<?php echo site_url("/part-search-result/"); ?>" method="POST" target="_blank">
    <?php include(__DIR__.'/inc/part-search.php'); ?>
</form>

part-search.php

<?php
    include( __DIR__.'/db-config.php' );
    $query = $db->query("SELECT * FROM ps_manufact WHERE status = 1 ORDER BY manufact_name ASC");
    $rowCount = $query->num_rows;
?>

<select name="manufacturer" id="manufact" onchange="manufactText(this)">
    <option value="">Select Manufacturer</option>
    <?php
        if($rowCount > 0){
            while($row = $query->fetch_assoc()){
                echo '<option value="'.$row['manufact_id'].'">'.$row['manufact_name'].'</option>';
            }
        }else{
            echo '<option value="">Manufacturer Not Available</option>';
        }
    ?>
</select>
<input id="manufacturer_text" type="hidden" name="manufacturer_text" value=""/>
<script type="text/javascript">
    function manufactText(ddl) {
        document.getElementById('manufacturer_text').value = ddl.options[ddl.selectedIndex].text;
    }
</script>

<select name="type" id="type" onchange="typeText(this)">
    <option value="">Select Manufacturer First</option>
</select>
<input id="type_text" type="hidden" name="type_text" value=""/>
<script type="text/javascript">
    function typeText(ddl) {
        document.getElementById('type_text').value = ddl.options[ddl.selectedIndex].text;
    }
</script>

<select name="year" id="year" onchange="yearText(this)">
    <option value="">Select Type First</option>
</select>
<input id="year_text" type="hidden" name="year_text" value=""/>
<script type="text/javascript">
    function yearText(ddl) {
        document.getElementById('year_text').value = ddl.options[ddl.selectedIndex].text;
    }
</script>

<select name="model" id="model" onchange="modelText(this)">
    <option value="">Select Year First</option>
</select>
<input id="model_text" type="hidden" name="model_text" value=""/>
<script type="text/javascript">
    function modelText(ddl) {
        document.getElementById('model_text').value = ddl.options[ddl.selectedIndex].text;
    }
</script>

<input type="submit" name="search" id="search" value="Search">


<script type="text/javascript">
    jQuery(function($) {
        $('#manufact').on('change',function(){
            var manufactID = $(this).val();
            if(manufactID){
                $.ajax({
                    type:'POST',
                    url:'<?php echo home_url('wp-content/themes/myTheme/inc/ajax-data.php') ?>',
                    data:'manufact_id='+manufactID,
                    success:function(html){
                        $('#type').html(html);
                        $('#year').html('<option value="">Select Type First</option>');
                    }
                });
            }else{
                $('#type').html('<option value="">Select Manufact First</option>');
                $('#year').html('<option value="">Select Type First</option>');
            }
        });

        $('#type').on('change',function(){
            var typeID = $(this).val();
            if(typeID){
                $.ajax({
                    type:'POST',
                    url:'<?php echo home_url('wp-content/themes/myTheme/inc/ajax-data.php') ?>',
                    data:'type_id='+typeID,
                    success:function(html){
                        $('#year').html(html);
                        $('#model').html('<option value="">Select Year First</option>');
                    }
                });
            }else{
                $('#year').html('<option value="">Select Type First</option>');
                $('#model').html('<option value="">Select Year First</option>');
            }
        });

        $('#year').on('change',function(){
            var yearID = $(this).val();
            if(yearID){
                $.ajax({
                    type:'POST',
                    url:'<?php echo home_url('wp-content/themes/myTheme/inc/ajax-data.php') ?>',
                    data:'year_id='+yearID,
                    success:function(html){
                        $('#model').html(html);
                    }
                });
            }else{
                $('#model').html('<option value="">Select Year First</option>');
            }
        });
    });
</script>

ajax-data.php

<?php

include( __DIR__.'/db-config.php' );

if(isset($_POST["manufact_id"]) && !empty($_POST["manufact_id"])){
    $query = $db->query("SELECT * FROM ps_type WHERE manufact_id = ".$_POST['manufact_id']." AND status = 1 ORDER BY type_name ASC");

    $rowCount = $query->num_rows;

    if($rowCount > 0){
        echo '<option value="">Select Type</option>';
        while($row = $query->fetch_assoc()){
            echo '<option value="'.$row['type_id'].'">'.$row['type_name'].'</option>';
        }
    }else{
        echo '<option value="">Type Not Available</option>';
    }
}

if(isset($_POST["type_id"]) && !empty($_POST["type_id"])){
    $query = $db->query("SELECT * FROM ps_year WHERE type_id = ".$_POST['type_id']." AND status = 1 ORDER BY year_name ASC");

    $rowCount = $query->num_rows;

    if($rowCount > 0){
        echo '<option value="">Select Year</option>';
        while($row = $query->fetch_assoc()){
            echo '<option value="'.$row['year_id'].'">'.$row['year_name'].'</option>';
        }
    }else{
        echo '<option value="">Year Not Available</option>';
    }
}

if(isset($_POST["year_id"]) && !empty($_POST["year_id"])){
    $query = $db->query("SELECT * FROM ps_model WHERE year_id = ".$_POST['year_id']." AND status = 1 ORDER BY model_name ASC");

    $rowCount = $query->num_rows;

    if($rowCount > 0){
        echo '<option value="">Select Model</option>';
        while($row = $query->fetch_assoc()){
            echo '<option value="'.$row['model_id'].'">'.$row['model_name'].'</option>';
        }
    }else{
        echo '<option value="">Model Not Available</option>';
    }
}

?>

part-search-result.php

<?php

if (isset($_POST['search'])) {
    $clauses = array();
    if (isset($_POST['manufacturer_text']) && !empty($_POST['manufacturer_text'])) {
        $clauses[] = "`manufacturer` = '{$_POST['manufacturer_text']}'";
    }
    if (isset($_POST['type_text']) && !empty($_POST['type_text'])) {
        $clauses[] = "`type` = '{$_POST['type_text']}'";
    }
    if (isset($_POST['year_text']) && !empty($_POST['year_text'])) {
        $clauses[] = "`year` = '{$_POST['year_text']}'";
    }
    if (isset($_POST['model_text']) && !empty($_POST['model_text'])) {
        $clauses[] = "`model` = '{$_POST['model_text']}'";
    }
    $where = !empty( $clauses ) ? ' where '.implode(' and ',$clauses ) : '';
    $sql = "SELECT * FROM `wp_products` ". $where;
    $result = filterTable($sql);
} else {
    $sql = "SELECT * FROM `wp_products` WHERE `manufacturer`=''";
    $result = filterTable($sql);
}

function filterTable($sql) {
    $con = mysqli_connect("localhost", "root", "root", "i2235990_wp2");
    if (!$con) {
        die('Could not connect: ' . mysqli_error($con));
    }
    $filter_Result = mysqli_query($con, $sql);
    return $filter_Result;
}

?>

    <?php get_header(); ?>

    <div class="container">
        <div id="products" class="row list-group">
        <?php while ( $rows = mysqli_fetch_array($result) ): ?>
            <div class="item col-xs-12 col-sm-4 col-md-4 col-lg-4">
                <div class="thumbnail">
                    <?php
                        echo '<img name="product-image" class="group list-group-image hvr-bob" src=' . $rows['image_url'] . ' width="400px" height="250px" alt="" />';
                    ?>
                    <div class="caption">
                        <h4 class="group inner list-group-item-heading">
                        <?php
                            echo "Manufacturer:\t".$rows['manufacturer'].'<br>';
                            echo "Type:\t".$rows['type'].'<br>';
                            echo "Year:\t".$rows['year'].'<br>';
                            echo "Model:\t".$rows['model'].'<br>';
                            echo '<br>';
                            echo "Description:\t".$rows['description'].'<br>';
                        ?>
                        </h4>                           
                    </div>
                </div>
            </div>
        <?php endwhile; ?>
        </div>
    </div>

    <?php get_footer(); ?>

Now my problem is:

If only select the first one box, or select the first two boxes, and click the Search button, it successfully jumps to the result page. However, if continuously select the third box, the result page is gone and Chrome Console returns the error:

Failed to load resource: the server responded with a status of 404 (Not Found)

Answer 1

Let me ask you a question. You've tagged this as a WordPress website. Correct? Then why aren't you using the built-in database handler, $wpdb to both prepare and communicate with the database? It's the safest and fastest way for you to work with the database.

Revised Code

Here I've revised your code to do the following:

• Use $wpdb->prepare to sanitize the $_POST values to protect the database from nefarious folks
• Remove redundancies by looping through a list of column names and using the field naming pattern you specified by suffixing _text to the column name
• Using $wpdb->get_results() to go fetch the results.

Here is the revised code:

/**
 * Build the search's WHERE SQL from the form's $_POST fields.
 *
 * @since 1.0.0
 *
 * @return string
 */
function build_search_where_sql() {
    global $wpdb;

    $column_names = array(
        'manufacturer',
        'type',
        'year',
        'model',
    );

    $where_clauses = [];
    foreach( $column_names as $column_name ) {
        $post_key = $column_name . '_text';
        if ( isset( $_POST[ $post_key ] ) && $_POST[ $post_key ] ) {
            $where_clauses[] =  $wpdb->prepare( "{$column_name} = %s", $_POST[ $post_key ] );
        }
    }

    if ( empty( $where_clauses ) ) {
        return '';
    }

    $where_sql = " WHERE " . join( ' AND ', $where_clauses );

    return $where_sql;
}

/**
 * Get the search results from the database.  If the records
 * do not exist or an error occurs, false is returned.  Else,
 * an array with stdClass objects for each record is returned.
 *
 * @since 1.0.0
 *
 * @return bool|array
 */
function get_search_database_results() {
    $where_sql = isset( $_POST['search'] )
        ? build_search_where_sql()
        : "WHERE manufacturer = ''";

    if ( ! $where_sql ) {
        return false;
    }

    global $wpdb;

    $sql_query = "SELECT * FROM wp_products {$where_sql};";

    $records = $wpdb->get_results( $sql_query );

    if ( ! $records ) {
        return false;
    }

    return $records;
}

UPDATE: Strategy for You

Now that I've seen the HTML code you are proposing as well as knowing you are learning about building websites, let's talk about a different architectural strategy for your project.

1. Don't use a custom database table.
2. Use a custom post type called products instead.
3. Use post metadata to set the attributes for each product, i.e. the manufacturer, model, year, type, etc.
4. Use a form plugin, such as Ninja Forms.
5. You can build the meta boxes yourself for the metadata, if you have the technical chops to do it. Else, you can use a third-party plugin such as CMB2 or ACF.

Custom Post Type

WordPress provides you the ability to add custom content. They provide built-in post types. We developers can add custom ones that are specific content context. Products is a good candidate for a custom post type.

You can generate the code on GenerateWP. It's literally a few lines of code to create it.

Where can you learn about Custom Post Types?

Well, there are plenty of tutorials. Codex gives you the documentation and examples. I teach it at Know the Code. Tuts+ has lots of tutorials. And many others....

Why Custom Post Type Instead of Custom Db Table?

Yes, you can build a custom database table. But it requires you to add the schema, seed the table, write the interface for admins to interact with the content, and then write and secure the interaction. To populate the options in the select, you'd have to query the database using $wpdb and then write a model to translate it into a view. Then you'd have to write the form processing to interact and save.

In other words, it's going to cost you time and money. Why? Because it's more code and not native to WordPress. You have to write, secure, test, and maintain it yourself.

If You Want to Stick With Your Current Strategy

If you prefer to stick with the custom database table strategy, then here are some suggestions to help you out:

1. You don't need the hidden input for each of the select elements. Why? When the form is posted, the option set for each select will post back to the server.
2. I'd change the select names to be an array, like this: name="part-select[manufacturer]" and then repeat for type, model, year, etc. Then you can grab the $_POST['part-select'] to get all of the values.
3. You are going to want to add nonce to protect the content. Make sure to pass it with the data packet when doing AJAX too.
4. Using AJAX, you can request the records from the database. You'll need to modify the code I gave to you in order to build the SQL query. Then loop through the results to build the HTML you want to send back to the front-end.
5. I like building the HTML markup on the server side and then sending that back to the front-end when doing AJAX.

Cheers.