Need Help w/Database Logic: MySQL

[jadedchron]

I'm losing my mind and need some help!

 

I have a MySQL DB with six tables: kit, kitcontent, pool, item, category, brand. It's for a quote generator.

 

kit: kit_id, name, price

pool: pool_id, name, price

item: item_id, name, price

brand: brand_id, name

category: category_id, name

kitcontent: kit_id, pool_id, item_id

 

Right now, everything has been going great, but I'm running into a stump when it comes to displaying the front end.

 

I have to filter based on the user's choice of the pool, and then under each category list the items for that particular category.

 

$kit_query = "SELECT kit_id FROM kitcontent WHERE pool_id = 3";
$kit_result = mysqli_query($dbc, $kit_query);
$kitrow = mysqli_fetch_row($kit_result);
$kitid = $kitrow[0];

$items_query = "SELECT item.*, item.price FROM item, kitcontent WHERE kitcontent.kit_id = $kitid AND kitcontent.item_id = item.item_id ORDER BY item.name";

$items_result = mysqli_query($dbc, $items_query);

while ($items_row = mysqli_fetch_array($items_result, MYSQLI_ASSOC)){

    echo $items_row['name'];

}

 

I'm sure there are plenty of flaws there as far as optimization etc, but right now, I need functionality. That'll display all of the items for the particular kit which matches the pool_id.

 

What I'm having trouble with is filtering out the items based on their category using the above code.

 

I'm sure this isn't the most clear post in the world, but my head is numb at the moment. I can provide more code, DB design schemes, etc. I just don't know which to provide.

 

If I can get a step in the right direction, that'd be great.

 

Thanks!