Filter Results

[lukekelly]

Hi all,

 

New to these forums, so treat me well 

 

Ok so i think the question i have is php specific...

 

I have a very very simple query/ piece of code im using to pull info from the db:

 

<style>
.ay_client
{
border:4px solid #ccc;
background:#ccc;
width:500px;
height:500px;
padding:10px;
text-align:justify;
overflow:auto;
}
</style>
<div class="ay_client">
<?php
$query =  mysql_query("SELECT * FROM jos_ay_client_activity");

{

echo $row['month'];

}

echo "<table><tr><td>UID</td> <td>Month</td> <td>Client</td> <td>Surveys</td> <td>Air Tests</td> <td>Project Managements</td> <td>Bulk Samples</td></tr>";

while($row = mysql_fetch_array($query))
{
    echo "<tr><td>";
    echo $row['id'];
    echo "</td><td>";
    echo $row['month'];
    echo "</td><td>";
    echo $row['client'];
    echo "</td><td>";
    echo $row['surveys'];
    echo "</td><td>";
    echo $row['airtests'];
    echo "</td><td>";
    echo $row['project'];
    echo "</td><td>";
    echo $row['bulks'];
    echo "</td></tr>";
}

echo "</table>";
?></div>

 

What i would like to do is create some filters for the results, so at the top of the table will be a few buttons to filter by month, client etc.

 

Firstly is this possible? (im taking a longshot here and thinking ofc its possible ) and Secondly how would i go about implementing it? I would laso like a sort function for each column.

 

Thanks in advance,

 

oh and some info if needed:

 

- MySQL client version: 5.1.33

- Apache/2.2.11 (Unix) DAV/2 mod_ssl/2.2.11 OpenSSL/0.9.8k

- PHP/5.2.9 mod_apreq2-20051231/2.6.0 mod_perl/2.0.4 Perl/v5.10.0

- phpMyAdmin Version: 3.1.3.1

 

 

Luke

[GingerRobot]

Firstly, do you know about ordering the results with an SQL query? You can just use ORDER BY field DIRECTION e.g:

 

SELECT * FROM yourtable ORDER BY somefield ASC
SELECT * FROM yourtable ORDER BY somefield DESC

 

Of course, you may wish to sort by one field, then another:

 

SELECT * FROM yourtable ORDER BY somefield ASC, otherfield DEC

 

Now, all you need to do is make each of your headings a link to the same page and pass some parameters in the URL to contain which field you're ordering by and the direction.

 

 

[lukekelly]

Thanks for the reply,

 

No i was not aware on how to order.. which is why i asked Im a php rookie so there are quite a few things i have never done nor come across, im learning what i need and taking it from there!

 

Only thing im not clear about is how you would get it to work. The only i can gather from what your saying is if for example i have two sort buttons next to headers, each will point to a page with exact same code but with this

 

$query =  mysql_query("SELECT * FROM jos_ay_client_activity ORDER BY month ASC");

 

and obv different variables for different rows.

 

That seems a bit long winded - And im pretty sure i didnt catch the drift of your answer

 

Do you mean headers should have a link that then uses the desired query and refresh's the page with that query? If so excuse my ignorance but i never knew you could do a link in such a way.

 

Also any tips on having a drop down box filter for say my "clients" field (and even for month for that matter)? It seems so simple in my head but i've got a block when it comes to implementing it!

 

Thanks in advance

 

Luke

[GingerRobot]

Well, you would construct links such as www.yoursite.com/yourpage.php?orderby=somefield&dir=ASC. If you want to allow for orderings on multiple fields, it'll get more complicated but a simple setup would be something like:

 

<?php
$fields = array('field1','field2','field3','field4','field5');
if(!isset($_GET['orderby'] || !in_array($_GET['orderby'],$fields) ){
    //if the user's not clicked a link or if the field isn't one we're sorting by(also prevents SQL injection)
    //set a default field
    $orderby = 'defaultField';
}else{
    //otherwise, we set the order by to the field in the URL
    $orderby = $_GET['orderby'];
}

if(!isset($_GET['dir']) || ($_GET['dir'] != 'ASC' && $_GET['dir'] != 'DESC')){
    //same thing for direction
    $dir = ASC
}else{
    $dir = $_GET['dir'];
}

//show headings. Assuming a table:
echo '<table><tr>';
foreach($fields as $field){
    if(isset($_GET['orderby'] && $_GET['orderby'] == $field){
        //if we're already ordering by this field, we swap the the direction of the order
        if($dir == 'ASC'){
            $newdir = 'DESC';
        }else{
            $newdir = 'ASC';
        }
    }else{
        //otherwise, we set the default ordering
        $newdir = 'ASC';
    }
    echo '<th><a href="' . $_SERVER['PHP_SELF'] . '?orderby= ' . $field . '&dir='. $newdir .' ">'. $field .'</a></th>';
}
$sql = "SELECT * FROM yourtable ORDER BY $orderby $dir";
//execute the query
//display results

 

Note that we save ourselves a lot of work by placing the fields in an array and looping through. This also allows us to check the user is passing us a valid field by using the array as a whitelist too.

[lukekelly]

Well, you would construct links such as www.yoursite.com/yourpage.php?orderby=somefield&dir=ASC.

 

Thanks, that has actually opened my eyes to other possible querys i can use in that manor.

 

To clarify (and thanks for posting it) can i just change that code to my settings and place it at the bottom of my existing code? Or would i need to place this elsewhere and get it to call on the file?

 

Also i really appreciate the fact that you explained why the code was written like that and what checks it does - Helpful for dissecting code in the future for other pages.

[GingerRobot]

Well, i suspect it's going to be more in place of your existing code rather than added to it. You already have code that selects information and displays it, so you're going to have to put the two pieces together. I've commented roughly what should happen where, so it shouldn't be too difficult.

[lukekelly]

Thanks for the reply, unfortunately i could not get that to work; im working with joomla, with the jumi component (that allows you to put in your custom code). Im going to have a play around in the mean time.

 

Also could someone answer whether it is possible to have some drop down boxes to sort?