Page 1 of 1
Filtering data
Posted: Fri Jan 16, 2009 6:49 pm
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
Re: Filtering data
Posted: Fri Jan 16, 2009 8:12 pm
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']."'";
Re: Filtering data
Posted: Sat Jan 17, 2009 4:17 am
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

Re: Filtering data
Posted: Sat Jan 17, 2009 8:25 am
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
Re: Filtering data
Posted: Sat Jan 17, 2009 9:32 am
by Burrito
Code: Select all
$result = mysql_query("SELECT * FROM `events` WHERE `month` = '".$_GET['month']."' AND `criteria_field2` = '".$_GET['criteria_value'].'' AND ....");
Re: Filtering data
Posted: Sat Jan 17, 2009 10:34 am
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
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.
Re: Filtering data
Posted: Sat Jan 17, 2009 10:48 am
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.
Re: Filtering data
Posted: Sat Jan 17, 2009 11:09 am
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