PHP Checkbox Filtered SQL List Without A Table Structure

JavaScript
, ,
1

Hi All

First post on this forum so please be gentle!

I’d like to be able to filter a list of SQL results using php checkboxes but without having the data displayed in a table.

I’ve found (and tried) tutorials on this kind of thing but can figure out how to manipulate them to fit my needs (I’m by no means a coder, I’ve learnt as I’ve gone along but cant seem to figure this one out!)

Closest I’ve seen was by @James_Hibbard on this forum but again, it was output into a table.

I’ve got my page HERE which is a dynamically built list of food items from my database. My filter lists are also built dynamically from the options in the relevant columns in my food items table…

Filter Code Example…

<?php
    $sql = "
    SELECT * 
    FROM food_items 
    INNER JOIN directory
        ON food_items.author = directory.author    
    ORDER BY directory.area
    ";

$result = $con->query($sql);        
    if ($result->num_rows > 0) 
        {        
            $areaFilter = '';
            while($row = $result->fetch_assoc())
            {
                if($areaFilter != $row["area"])
                {
                    $areaFilter = $row["area"];
                    echo '<div><input type="checkbox" id="id_' . $areaFilter . '" name="' . $areaFilter . '"><label for="AreaStalybridge">' . $areaFilter . '</label></div>';
                }
            }
        };

?>

And here’s the code for the dynamic list of food items, grouped by course…

    <?php
        $sql = "
        SELECT * 
        FROM food_items 

        INNER JOIN directory
            ON food_items.author = directory.author

        INNER JOIN courses
            ON food_items.course_id = courses.course_id

        ORDER BY food_items.course_id
        ";

    $result = $con->query($sql);        
        if ($result->num_rows > 0) 
            {        
                $curCategory = '';
                while($row = $result->fetch_assoc())
                {
                    if($curCategory != $row["course"])
                    {
                        $curCategory = $row["course"];
                        echo '<p class="menuCourseHeader"><strong>' . $curCategory . '</strong></p>';
                    }
                        echo 
                            '<div class="menuItemContainer">
                                <div class="menuItemContainerLeft"> <p class="menuVenue">' . $row["pub_name"] . '</p><p class="menuVenue">' . $row["area"] . '</p> </div>
                                <div class="menuItemContainerMain">
                                    <p class="menuItem">' . $row["item"] . ' - £' . $row["price"] . '</p>
                                    <p class="menuDescription">' . $row["description"] . '</p>
                                </div>
                                <div class="menuItemContainerRight"><p class="menuVenue">' . $row["course"] . '</p> </div>
                            </div>';
                }
            };
    echo '<hr/>';

$con->close();
    ?>

If anybody can point me in the right direction, your help would be very much appreciated.

Cheers, Mark.

2

I have not fully made sense of the problem, but I’m thinking that any filtering should be done in the query via WHERE clauses, not selecting everything, and then filtering.

3

Thanks for the reply
I want the list to show everything to start with then as checkboxes are ticked the list will shorten. As far as I can make out, I need to make checking a box add a WHERE clause to the existing SQL, filtering the list?
Eg. Ticking Denton will add a WHERE area = ‘Denton’ clause showing all food in Denton. Ticking Denton and Pizza will change the clause to WHERE area = ‘Denton’ AND course = ‘Pizza’ showing all Pizza in Denton and so on.
Maybe i’m thinking about it all wrong but if you could give me a better way I’d appreciate it

4

On the page I see the list and the filters checkboxes, but nowhere to submit the request.
Should there be a submit button, or are you expecting it to happen dynamically without a page refresh?
That would require a javascript solution.

5

It would be nice if it’d refresh as it’s ticked (using some kind of jQuery post solution) but a Submit button would work just as well. Sorry if I’m not being too clear with what i’m saying, I’m no coder. I know what’s possible and usually have a good idea of what is needed to make it happen, it’s just getting the code right that I have trouble with! :flushed:

6

For a php only solution, you would have to put the checkboxes in a form with a submit button. The parse the data and adjust the query with WHERE clauses.
Or you can take the more interactive javascript route. Then I guess you fetch the whole lot and let js show/hide according to what is checked.

7

This is as far as I’ve got up to now…

$sql = ’
SELECT *
FROM food_items

  INNER JOIN directory
  	ON food_items.author = directory.author
  
  INNER JOIN courses
  	ON food_items.course_id = courses.course_id
  	
  WHERE directory.area = "' . $area . '" AND food_items.cat_id = ' . $cat_id . '
  
  ORDER BY food_items.course_id
  ';

Just need to figure out how to change the $area and $cat_id variables in the WHERE clause now… Think I’ll try the Javascript / Ajax approach… I’ll ge there eventually!

Thanks for the suggestions so far! :+1:

8

That would be done by processing the form data.
WHEREs can be appended to the query from if conditions depending on what is checked.
BTW, those variables should not be directly in the query, but bound to a prepared statement.

That’s a different approach, maybe one for the js forum.

9

Thanks again, I’ll have a search around the web for some code I can ‘recycle’! As I said, I’m not much of a coder but at least now I know what i’m looking for!
Just changed the topic of this thread too from PHP to Javascript, see if anyone else can help

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.