Fixed: Too many results from one category

Hi

I have this form with a category select tag and a subcategory select tag. I then have the following jQuery file which populates the subcategory select depending on which category has been selected. All the information comes from a database:

<script>
        $(document).ready(function() {
    $('#category').change(function() {
    var val = $(this).val();
    if (val == '')  {
    $('#subcategory').html("<option value=''>Any</option>");
    <?php foreach ($categories as $category) :
$id = $category['cat_id'];
    ?>
            } else if (val == <?php htmlout($id); ?>) {
    $('#subcategory').html("<option value=''>Any</option><?php $sql = "SELECT subcat_id, subcat_name FROM subcategory WHERE cat_id = $id ORDER BY cat_id";
    $result = mysqli_query($link, $sql);
    if (!$result)
    {
    $error = 'Error fetching subcategory details.';
    include $_SERVER['DOCUMENT_ROOT'] . '/includes/error.html.php';
    exit();
    }
    while ($row = mysqli_fetch_array($result))
    {
    $subcategories[] = array('subcat_id' => $row['subcat_id'], 'subcat_name' => $row['subcat_name']);
    }
 foreach ($subcategories as $subcat) : ?><option value=<?php htmlout($subcat['subcat_id']); ?>><?php htmlout($subcat['subcat_name']); ?></option><?php endforeach; ?>");<?php $subcategories = array();
        endforeach;
    ?>
        }
        });
        });
</script>

There are nine categories, and all but one of them work perfectly. Just one doesn’t. I am sure that it doesn’t matter, but its row id is 5. What happens is that it puts all of the subcategory items into its option values. But if I change the jQuery to:

<script>
        $(document).ready(function() {
    $('#category').change(function() {
    var val = $(this).val();
    if (val == '' || or val== '5')  {
    $('#subcategory').html("<option value=''>Any</option>");
    <?php foreach ($categories as $category) :
$id = $category['cat_id'];
    ?>
            } else if (val == <?php htmlout($id); ?>) {
    $('#subcategory').html("<option value=''>Any</option><?php $sql = "SELECT subcat_id, subcat_name FROM subcategory WHERE cat_id = $id ORDER BY cat_id";
    $result = mysqli_query($link, $sql);
    if (!$result)
    {
    $error = 'Error fetching subcategory details.';
    include $_SERVER['DOCUMENT_ROOT'] . '/includes/error.html.php';
    exit();
    }
    while ($row = mysqli_fetch_array($result))
    {
    $subcategories[] = array('subcat_id' => $row['subcat_id'], 'subcat_name' => $row['subcat_name']);
    }
 foreach ($subcategories as $subcat) : ?><option value=<?php htmlout($subcat['subcat_id']); ?>><?php htmlout($subcat['subcat_name']); ?></option><?php endforeach; ?>");<?php $subcategories = array();
        endforeach;
    ?>
        }
        });
        });
</script>

The change is in the fifth line of the jQuery, counting <script> as line 1. With that change, it then leaves the subcategory select dropdown as “Any” and when the user submits the form, they get all the items in that category, which is preferable to selecting the wrong subcategory and having it return an error message.

I have checked the database, it is OK. Also, I have used this same piece of jQuery in another website, and the only thing I had to do was change one of the id selectors.

Any help would be appreciated, as this is driving me nuts.

We cannot determine much in the way of issues with the jQuery code since much of it is built in the server.
The only significant issue that I see is that the option values aren’t delimited by quotes (preferably double quotes for HTML attributes).

Can you link us to a test page that exhibits the problem?

Here is the link to the page: http://www.palmeragriparts.co.nz/palmer/

If you select any category except the top one (Combine Crop Lifters) you will see the php working correctly. I think the most subcategories that come up are eight. When you select the top category, all the subcategories appear. To say the least it is very frustrating, and at worst, quite confusing, because if you select the wrong subcategory, the request will return no results.

Thanks for your help.

It seems that all of the options (including Combine Crop Lifters) work for me in both Chrome and IE.
Combine Crop Lifters contains options that contain, Aerator Points, Bale Fork Tine Parts, and so on.

Is it possible that what you’re noticing is just that the width of the dropdown box doesn’t change between Combine Crop Lifters and Cultivation Parts? That would be due to both dropdowns contain the same options at the widest width, Slipdown Point Clamp Assemblies.

I checked again and I still have the same troubles. What I should have mentioned is that when you select Combine Crop Lifters, there should only be four subcategories: Palmer Bolt-on, Floating, Clipon and Spring-clip lifter spool. Are you saying that is what you see. If there is someway that I could show you my Mysql tables you would see that is the case.

Thanks, again

What I’m seeing matches up exactly with what is in the HTML page.


} else if (val == 5) {
	$('#subcategory').html("<option value=''>Any</option><option value=10>Aerator Point</option><option value=22>Bale Fork Tine Parts</option><option value=21>Bale Fork Tines</option><option value=38>Bird Scarer</option><option value=25>Carriage Fasteners</option><option value=29>Clip-on</option><option value=24>Cultivation Fasteners</option><option value=4>Disc</option><option value=30>Electronic Irrigation Clocks</option><option value=26>Engineers Fasteners</option><option value=35>Fastener Nuts</option><option value=39>Feeders</option><option value=28>Floating</option><option value=5>Hub Kit</option><option value=31>Irrigation Gates</option><option value=34>Irrigation Parts</option><option value=16>MaxiTill and S-Tine Clamps</option><option value=18>Miscellaneous Cultivation Parts</option><option value=7>Mouldboard</option><option value=2>NiHard Maxi Share</option><option value=37>NZ-European Conversion</option><option value=27>Palmer Bolt-on</option><option value=23>Plough Fasteners</option><option value=3>Plough Point</option><option value=8>Plough Saddle</option><option value=1>Plough Share</option><option value=9>Plough Toe</option><option value=11>Reversible Grubber Points</option><option value=12>S-Tine Points</option><option value=15>Slipdown Point Clamp Assemblies</option><option value=14>Slipdown Points</option><option value=6>Sole Plate</option><option value=33>Spring-clip lifter spool</option><option value=36>The Dale Share</option><option value=13>Tine Assemblies</option><option value=17>Yeoman Chisel Plough Parts</option><option value=27>Palmer Bolt-on</option><option value=28>Floating</option><option value=29>Clip-on</option><option value=33>Spring-clip lifter spool</option>");

If all of those options are not supposed to appear, then it’s not the JavaScript causing the problem, but something to do with the PHP code that created it, or the contents of the database instead.

I’ll move this thread over to the PHP forum to see if they can help you any further.

Thanks for your help Paul. I hadn’t thought about it being a php problem, but that makes sense.

Here are the two php files:


                <section class="center">
                    <form action="selection.php" method="post" name="form1">
                        <table>
                            <tr>
                                <td><strong>Category:</strong>&nbsp;</td>
                                <td><strong>Subcategory:</strong>&nbsp;</td>
                            </tr>
                            <tr>
                                <td>
                                    <select id="category" name="category" size="1">
                                        <option value=''>Select one</option>
                                        <?php foreach ($categories as $category) :
                                            ?>
                                            <option value="<?php htmlout($category['cat_id']); ?>"><?php htmlout($category['cat_name']); ?></option>
                                        <?php endforeach; ?>
                                    </select></td>

                                <td><select id="subcategory" name="subcategory" size="1">
                                        <option value=''>Any</option>
                                    </select></td>
                            </tr>
                            <tr>
                                <td colspan="2" class="center">
                                    <input name="submit" type="submit" value="submit" />
                                </td>
                            </tr>
                    </form>

And this is the php file called by the form action:


<?php
include_once $_SERVER['DOCUMENT_ROOT'] . "/includes/helpers.inc.php";
include_once $_SERVER['DOCUMENT_ROOT'] . "/../code3/db.inc.php";
if (isset($_POST['category'])) {
    $cat = $link -> real_escape_string($_POST['category']);
}
if (isset($_POST['subcategory'])) {
    $subcat = $link -> real_escape_string($_POST['subcategory']);
}

$select = 'SELECT *';
$from = ' FROM partList';
$where =  ' WHERE TRUE';
$order = ' ORDER BY cat_id, subcat_id, part_num';

if($cat != '') // a category is selected
{
    $where .= " AND cat_id = $cat";
}

if($subcat != '') // a subcategory is selected
{
    $where .= " AND subcat_id = $subcat";
}

$result = mysqli_query($link, $select . $from . $where . $order);
if (!$result)
{
$error = 'Error fetching parts list details.';
include 'error.html.php';
exit();
}
$numRows = mysqli_num_rows($result);

while ($row = mysqli_fetch_array($result))
{
    $parts[] = array( 'part_id' => $row['part_id'], 'cat_id' => $row['cat_id'], 'subcat_id' => $row['subcat_id'], 'part_num' => $row['part_num'], 'thumb_caption' => $row['thumb_caption'], 'man_id' => $row['man_id'], 'width' => $row['width'], 'length' => $row['length'], 'thickness' => $row['thickness'], 'holeSpace' => $row['holeSpace'], 'boltSize' => $row['boltSize'] , 'pix' => $row['photo_filename'], 'application' => $row['application'], 'description' => $row['description']);
}


include 'selection.html.php';
?>

A message from the OP says that the problem is now resolved. Some prior pop code was helping to cause the problem.

Problem RESOLVED! Thanks to Paul, for getting me to consider that the problem may have been a php one (I thought it was my jQuery) I really searched all my code that was involved with this part of the site. I found that just prior to going to the page where the jQuery was called, I had already read all the subcategories from the database. Somehow this obviously conflicted with the first category’s subcategory list. After that first check of which subcategories went with what categories, everything worked as it should. Getting rid of the redundant SELECT getting all the subcategories fixed the problem.

Thanks