dropdown Option to clear php query

Question

I have the following dropdown box that uses PHP query to get it's data. It creates a dropdown with 3 entries: CARS, TRUCKS, TRAINS. After I select an option, it performs a GET and the PHP updates with that category that is selected. I added a static OPTION with text VIEW BY CATEGORY. If I choose a category and then go back to VIEW BY CATEGORY, I would like all results (CARS, TRUCKS, and TRAINS) to show again.

<form name="form1" action="" method="GET">
<select name="category" onchange="this.form.submit();">
<option value="">View By Category...</option>
<?php while ($rows = mysql_fetch_array($query_category)) { ?>

<?php if(isset($_GET['category'])) { ?>

<option value="<?php echo $rows['category']; ?>" <?php echo $rows['category'] == $category ? 'selected' : '' ?> ><?php echo $rows['category'] ?></option>

<?php } else {?>

<option value="<?php echo $rows['category']; ?>"><?php echo $rows['category'] ?></option>

<?php } ?>

<?php } ?>  
</select>
</form>

*EXAMPLE- When I select option CARS, my URL will be http://localhost/edit.php?category=Cars. When I choose VIEW BY CATEGORY, it becomes http://localhost/edit.php?category=. Then all of my results disappear. Rather than disappear, I would like all results to show.

    <?php
                        //Establishing Connection with Server
                        $connection = mysql_connect("localhost", "em", "em");

                        //Selecting Database
                        $db = mysql_select_db("em", $connection);   

                        //This checks if variable defined
                        if(isset($_GET['category'])) {          

                        //If it is, will run query with variable
                        $category = $_GET['category'];  


                        $query = mysql_query("SELECT * FROM tblClients  WHERE tblclients.package =  'standard' AND tblclients.category = '$category' ", $connection);   
                        } else {

                        //If NOT, will run query without variable   
                        $query = mysql_query("SELECT * FROM tblClients  WHERE tblclients.package =  'standard' ", $connection);                         
                        }

                        //Other Queries                         
                        $query_featured = mysql_query("SELECT * FROM tblClients WHERE tblclients.package =  'featured'", $connection);  
                        $query_category = mysql_query("SELECT * FROM tblCategory", $connection);                            
?>

I need the code that creates the list from the category to help you. — frankle, Dec 23 '15 at 09:34
I think I can see it, see my answer below and see if it helps :) — frankle, Dec 23 '15 at 09:53
ALSO - this is a recipe for disaster. Never trust user input, always sanitize the super globals. See this post [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — frankle, Dec 23 '15 at 09:54

Professor Abronsius · Accepted Answer · 2015-12-23T10:10:19.963

One think I spotted was the undeclared $category variable which I assume to be $_GET['category'] You might wish to perform some better sanitation of user input here.

To get the records displayed when the user selects VIEW BY CATEGORY and thus causing the url to become http://localhost/edit.php?category= you should set a test that selects records according to whether $_GET['category is empty.

ie:

<?php
    if( $_SERVER['REQUEST_METHOD']=='GET' ){
        if( isset( $_GET['category'] ) )    {
            if( !empty( $_GET['category'] ) ){
                /* sql: query using known category from url */
            } else {
                /* sql: query to get all records as no category defined */
            }
        }
    }
?>

<form name='form1' action='' method='GET'>
    <select name='category' onchange='this.form.submit();'>
        <option value=''>View By Category...</option>

        <?php   
            while ( $rows = mysql_fetch_array( $query_category ) ) { 
                if( isset( $_GET['category'] ) ) {
                    /* There appeared to be an undeclared variable $category here */
                    $category=trim( $_GET['category'] );
                    $selected=$rows['category'] === $category ? 'selected' : '';
                    echo "<option value='{$rows['category']}' {$selected}>{$rows['category']}</option>";
                } else {
                    echo "<option value='{$rows['category']}'>{$rows['category']}</option>";
                }
            }
        ?>  
    </select>
</form>

update: Since the posting of the db logic and sql, some of the above could be simplified to

<?php
    //Establish Connection with Server
    $connection = mysql_connect("localhost", "em", "em");
    $db = mysql_select_db( "em", $connection );

    /* If category is defined in url, filter and assign as a variable - otherwise it is false */ 
    $category = isset( $_GET['category'] ) && !empty( $_GET['category'] ) ? strip_tags( filter_input( INPUT_GET, 'category', FILTER_SANITIZE_STRING ) ) : false;


    $query_featured = mysql_query("SELECT * FROM `tblClients` c WHERE c.`package` = 'featured'", $connection);  
    $query_category = mysql_query("SELECT * FROM `tblCategory`", $connection); 


    if( $_SERVER['REQUEST_METHOD']=='GET' ){
        if( $category ){
            /* sql: query using known category from url */
            $sql="SELECT * FROM `tblClients` c  WHERE c.`package`='standard' AND c.`category` = '$category'";
        } else {
            /* sql: query to get all records as no category defined */
            $sql="SELECT * FROM `tblClients` c  WHERE c.`package`='standard'";
        }
        $query=mysql_query( $sql );
    }
?>

It should be noted however @Objective_d - you ought to use either mysqli or PDO as they offer far greater protection from the dreaded sql injection by utilising prepared statements. Good luck - happy xmas

score 1 · Answer 2 · answered Dec 23 '15 at 09:52

1

Try this:

//This checks if variable defined
if(isset($_GET['category'])&&$_GET['category']!='')

The problem is that the GET variable is set, so the condition evaluates to true. You need to account for the variable to be set but equal to nothing (which it is).

Doing it as advised will mean that instead of running the first query you will revert to running the second and receive the entire list of items.

answered Dec 23 '15 at 09:52

frankle

141
1
15

Thanks. This really me helped out. I gave you point but not answer because RamRaider gave solution first. My fault for not including the query code. – Maria Nolorbe Dec 23 '15 at 10:06
:)) No problem - but please please please please read up on _SQL injection_, after that read up on _mysqli_ or _pdo_ – frankle Dec 23 '15 at 10:07

score 0 · Answer 3 · answered Dec 23 '15 at 09:33

0

<select name="category" onchange="this.form.submit();">
  <option value="">View By Category...</option>
  <?php while ($rows = mysql_fetch_array($query_category)) { ?>
     <option value="<?php echo $rows['category']; ?>" <?php if($_GET['category'] == $rows['category']) { echo "selected='selected'"; } ?>  ><?php echo $rows['category'] ?></option>

  <?php } ?>  
</select>

Use if condition to match category and if it is matched then add attribute selected='selected' into option tag.

answered Dec 23 '15 at 09:33

Shailesh Katarmal

2,757
1
12
15

I don't think that answers the question. I interpret it as 'how do I get cars, trucks and trains to be listed if the view by category option is selected?' – frankle Dec 23 '15 at 09:36

dropdown Option to clear php query

3 Answers3