Filtering data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
juxp00
Forum Newbie
Posts: 15
Joined: Fri Jan 16, 2009 6:43 pm

Filtering data

Post by juxp00 »

Hello - I fear I may be using the wrong terminology with "filtering" but here's what I am trying to do:

-I am displaying all records from my database on a page "listings.php"
-I want to add options on the page to do the following:
--Drop down menu to only display records for certain months - where the column entry for "month" is "June" for example
--Have only the records that are "June" displayed on the "listings.php" page
--Do this for each month, via a list with links for each month.

Any pointers to a tutorial / code examples would be great - my Google searches and searches of this forum aren't working due to the fact I don't know how to express what I need to do in a short search phrase.

Many thanks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Filtering data

Post by Burrito »

it depends on how your dates are represented in your database.

basically you just want to select from the database where the month is the month that is selected

Code: Select all

 
$query = "SELECT * FROM `table` WHERE `month` = '".$_POST['month']."'";
 
juxp00
Forum Newbie
Posts: 15
Joined: Fri Jan 16, 2009 6:43 pm

Re: Filtering data

Post by juxp00 »

Thanks for your reply.

OK so when I use that query I get no results returned.

I still want my "listings.php" page to display everything from the table, but also on that same page have a link to change the displayed content, but month.

So, I have this:

Code: Select all

 
listings.php
//DB connect
 
<?php 
//display all records
$sql = "SELECT * FROM table";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>'); 
 
while ($row = mysql_fetch_array($result)) {
?>
 
<?php if ($row ['eventtitle']!= "") { 
echo '<h1>'; 
echo $row ['eventtitle'];
echo '</h1>';
}
?>
 
<?php if ($row ['month']!= "") { 
echo '<h4>'; 
echo $row ['month'];
echo '</h4>'; 
}
?>
<?php
}
 
mysql_close($dbconn);
?>
 
This display everything. Now somewhere on the page I need a menu to just display by month, something like:

Code: Select all

 
<div>
<p>MENU FOR MONTH</p>
<ul>
<li><a href="listings.php?date=jan">Jan</a></li>
<li><a href="listings.php?date=feb">Feb</a></li>
<li><a href="listings.php?date=march">March</a></li>
</ul>
</div>
 
But, with this code in place, I am getting no results displayed when I click on the links - any ideas?

Thanks :D
juxp00
Forum Newbie
Posts: 15
Joined: Fri Jan 16, 2009 6:43 pm

Re: Filtering data

Post by juxp00 »

OK I have got that working fine.

Now I have another task - not only do I need to filter by Month, but also by two other criteria.

In short, I need my listings.php page to function like this:

-When visited display all records
-Show 3 filter options - Month / Type / Time (date and time are just text fields)
-Have a menu to click on the filter options, and change the results presented

Currently, this is working for displaying content by month:

Code: Select all

 
<?php 
//Variables
$month= $_GET['month'];
$eventtitle= $_GET['eventtitle'];
?>
 
<a href="/eventlistings.php?month=jan">LINK</a>
<a href="/eventlistings.php?month=feb">LINK</a>
<a href="/eventlistings.php?month=march">LINK</a>
 
<?php
$con = mysql_connect("details");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("dbname", $con);
 
$result = mysql_query("SELECT * FROM `events` WHERE `month` = '".$_GET['month']."'");
 
while($row = mysql_fetch_array($result))
  {
?>    
      
<?php if ($row ['eventtitle']!= "") {
echo '<h1>';
echo $row ['eventtitle'];
echo '</h1>';
}
?>
 
<?php if ($row ['month']!= "") {
echo '<h4>';
echo $row ['month'];
echo '</h4>'; 
  
<?php  } ?>
 
How do I modify my query to allow the page to display content based on 3 sets of criteria, instead of just one?

Code: Select all

$result = mysql_query("SELECT * FROM `events` WHERE `month` = '".$_GET['month']."'");
I feel I need to have one of these that will also query for "Type" and "Time", my other filter options.

Any ideas?
Thanks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Filtering data

Post by Burrito »

Code: Select all

 
$result = mysql_query("SELECT * FROM `events` WHERE `month` = '".$_GET['month']."' AND `criteria_field2` = '".$_GET['criteria_value'].'' AND ....");
 
juxp00
Forum Newbie
Posts: 15
Joined: Fri Jan 16, 2009 6:43 pm

Re: Filtering data

Post by juxp00 »

Awesome, thanks. I actually meant that the 3 criteria can be independent of each other, so I changed the AND to OR, which did the trick, great :D

The only thing now is, when the page loads, I want to display all records from the DB, as currently I am only getting the records via the links - any ideas?

Thanks for your help, much appreciated.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: Filtering data

Post by Burrito »

I don't fully understand your requirements. You say you want to filter things (the title of this thread) then you say you want to display everything.

Since I don't quite comprehend what you're after I'll provide what I think might work for you.

If you need to show everything but want to filter some things (I presume for display???) you could do it one of three ways.
1) create two queries (one to show everything, the other to limit what you're showing (using the query I sent above))
2) create one query that selects everything then add only the items you need (the filtered ones) to a separate array
3) handle your filter client-side which would allow you to make changes w/o page reloading.
juxp00
Forum Newbie
Posts: 15
Joined: Fri Jan 16, 2009 6:43 pm

Re: Filtering data

Post by juxp00 »

Thank you.

What I am trying to do is:

-When the page listings.php is loaded, all records are displayed
-A sub menu is also displayed for the filter options.

So default display is all records, and then users can filter as they wish.

Thanks for your suggestions, point 3 seems like the best option but I have no idea how to do that, and point 1 looks like the most straightforward, but I'm not quite sure how I would determine which query is being used. Time for more research.

Thanks
Post Reply