MySQL, PHP filtering results within displayed table?

Question

I've searched through what "I think I should be searching for" - but not really understanding or getting anywhere, please could someone point me in the right direction?

I'd like to be able to filter a result set using the th of one column (region) and although I tried the w3cs tutorial I ended up confusing myself as to "what bit of code goes where" etc and the dropdown form thingie looked very clunky.
All advice and help most welcome.

This is my example website, myTrader clicking the All Trade Regions link on the left displays as far as I've got & that took me days of cobbling tutorials and guddling about in the dark.

query.php code so far:

    <table id="mainTable">
    <tr><td width="300" valign="top"><H1>Welcome to myTrader!</H1>
    <span class="mainText">Glass & Paper Recycled Commodities Trading Site</span></td>
    <td valign="top" align="right"><img src="images/largeLogo.jpg" width="406" height="113" border="0"></td></tr></table>
    <P><span class="paraHeadingOne">All Region Paper Sales</span> </P>
    <P>
    <table class="dbTable">
<tr>
<tr><th>Commodity</th> <th>Region</th> <th>Member</th> <th>Size</th> <th>Price</th> <th>Date Posted</th>
</tr>
    <?php
    $link = mysql_connect('localhost', 'palegall', '******');
    if (!$link) {
    die('Could not connect: ' . mysql_error());
    }
    $db_selected = mysql_select_db('palegall_newTrader', $link);
    if (!$db_selected) {
        die ('cant find newTrader' . mysql_error());
    }
    $query = mysql_query ("SELECT * FROM `sell` WHERE `commodity`='Paper' ORDER BY `price`") or die( mysql_error() );
    $row=mysql_fetch_assoc($query);
    do
    {
    echo'<table class="dbTable">';
    echo '<tr><td>'.$row['commodity'].'</td>
   <td>'.$row['region'].'</td>
   <td>'.$row['member'].'</td>
    <td>'.$row['size'].'</td>
   <td>'.$row['price'].'</td>
   <td>'.$row['posted'].'</td>
   </tr>';
    }while($row=mysql_fetch_assoc($query));
    echo "</table>";
    ?>
  </td></tr></table>
  </td></tr></table>
  </body></html>

We need more information : what tables have you created ? what results do you want ? provide a short example (an entire website is not an example)... — Cyril Gandon
– Cyril Gandon, Commented Jul 4, 2011 at 13:53
Sorry Scorpi0 - only want to use the two tables displayed - (sell & buy) - all the data needs to appear first, then have the user click the header for Region & some method of filtering be available, I dont have any code for the filter part yet but can post my query.php code here - erm, how do I do that? — scotia
– scotia, Commented Jul 4, 2011 at 14:32

Edoardo Pirovano · Accepted Answer · 2011-07-04 13:55:29Z

1

To filter results you need to add WHERE columnName = 'desiredValue' at the end of the query. Optionally, you can specify several conditions using AND or OR, for example:

 WHERE columnName = 'possibleValue' OR columnName = 'otherPossibleValue'

answered Jul 4, 2011 at 13:55

Edoardo Pirovano

8,3543 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

scotia Over a year ago

Hello EdoDodo - thank you, as above I need all the results to show first then for the user to be able to filter them - so Im guessing I need two queries, the first to show me all results, & a second one (which is where I dont know where to put or how to construct) would my second query "live" on the same page as the results? Many thanks :)

topherg · Accepted Answer · 2011-07-04 13:55:45Z

0

I think I know what you mean,
have you tried playing around with the WHERE area of the query. This can change how many results are returned.

I had something similar to this, except it was tagged locations. I had two ideas, first, a table with each column of a tinyint, each one was a certain area. Or the other, where there was just one extra column with space delimited values for specific areas. That was the one I went for and used a query similar to

SELECT * FROM 'items' WHERE `locations` LIKE '% $area %'

as long as there was always a space at the beginning of the string, it was fine at it allowed for multiple options for a single item which didn't take a load of queries to load and lots of memory to sift through the data.

in this case, the data included was

ID    Title        locations
1      Test 1    blank
2      Test 2     london surrey

When a search was done for just london, then "test 2" was displayed

Hope that helps

answered Jul 4, 2011 at 13:55

topherg

4,3314 gold badges40 silver badges74 bronze badges

11 Comments

scotia Over a year ago

Hello Chris thank you, I need all the results to show first, then have the user click to select a region, Ive tried using separate links but its very clunky, just wondered if there is an "in table" way of filtering? Or something to add (as suggested below although not tried it yet!) - :)

topherg Over a year ago

do you mean AJAX? because you could just get it to repopulate the target area with a new table loaded from a PHP file with a GET variable of the target area. Even if its not ajax, you could have a general query that has no WHERE tag and displays them all, with separate links or a text box at the top (under the column headers) where certain areas can be searched for. You would need to have some kind of anti sql injection code active to protect your database.

scotia Over a year ago

Hey Chris, thanks - yes I think thats kinda what Im after? I looked at two tutorials that included an ajax drop down thing & implemented script on the query page, (I couldnt get it to work) I didnt really understand where to "put" stuff in relation to the querys etc on the page - sorry for being a bit slow on the uptake! Basically I just want a simpe user click & allow them to select region - not fussed how, so long as I can get it to work LOL,

scotia Over a year ago

- also tried this stackoverflow.com/questions/6306807/php-filtering-data-in-table :)

topherg Over a year ago

well, either have a dropdown select box that is populated with the list of areas, or a text box for searching. That could be in the next row from the table headers, then have a second table below in a div tag with an ID of new content, then use getElementById('newcontent') to change the inner html with the AJAX response. The call is made when either a button is clicked, or a change has been made with no changes in the last 2 seconds. This sends off a request to '/newcontent.php?searchfield=' then the contents of the text box or select. The PHP then takes that, SQL's it and returns a table

|

Collectives™ on Stack Overflow

MySQL, PHP filtering results within displayed table?

2 Answers 2

1 Comment

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related