Filter Results after all are displayed

PHP
1

Hello,
I have written a PHP site that is used to schedule courses for CPR.
On the backend i have a table which shows all scheduled courses, however I am having a hard time creating a filter.

Once the admin loads the page I would like the table to display all records. On the top of each column, I have drop downs/text boxes which show disstinct values for each column.

How do I get it to filter if there someone clicks the button. I have tried %like% but then nothing loads unless something is selected.
See image and see mysql code.


SELECT 
  coursetypes.coursetypename,
  scheduledcourses.coursedate,
  scheduledcourses.coursetime,
  scheduledcourses.courseendtime,
  locations.locationname,
  instructors.instructorname,
  instructors.instructorlastname,
  scheduledcourses.coursecost,
  scheduledcourses.scheduledcoursesid,
  scheduledcourses.coursenumberofseats,
  scheduledcourses.notes,
  locations.locationcity,
  locations.locationstate,
  locations.locationzip,
  scheduledcourses.privatecourse
FROM
 scheduledcourses
 INNER JOIN coursetypes ON (scheduledcourses.coursetype=coursetypes.coursetypesid)
 INNER JOIN locations ON (scheduledcourses.courselocationid=locations.locationsid)
 INNER JOIN instructors ON (scheduledcourses.courseinstructor=instructors.instructorsid)
WHERE
  scheduledcourses.coursestatus = 'scheduled'
ORDER BY
  scheduledcourses.coursedate
2

Can you append criteria to your where statement?

Michael

3

Im sure that is the answer but thats what i dont know how to do. Can you give me an example?

4

Lets say you were filtering by state.

if ($_POST[‘state’]){
$where.=" locations.locationstate=‘“.mysql_escape_string($_POST[‘state’]).”’ ";
}

$sql="
SELECT
coursetypes.coursetypename,
scheduledcourses.coursedate,
scheduledcourses.coursetime,
scheduledcourses.courseendtime,
locations.locationname,
instructors.instructorname,
instructors.instructorlastname,
scheduledcourses.coursecost,
scheduledcourses.scheduledcoursesid,
scheduledcourses.coursenumberofseats,
scheduledcourses.notes,
locations.locationcity,
locations.locationstate,
locations.locationzip,
scheduledcourses.privatecourse
FROM
scheduledcourses
INNER JOIN coursetypes ON (scheduledcourses.coursetype=coursetypes.coursetypesid)
INNER JOIN locations ON (scheduledcourses.courselocationid=locations.locationsid)
INNER JOIN instructors ON (scheduledcourses.courseinstructor=instructors.instructorsid)
WHERE
scheduledcourses.coursestatus = ‘scheduled’
$where
ORDER BY
scheduledcourses.coursedate
";

Something like that should work.

5 
if ($_POST['state']){
$where.=" locations.locationstate='".mysql_escape_string($_POST['state'])."' ";
}

=>


$where = array();
if ($_POST['state']){
$where[] ="locations.locationstate='".mysql_escape_string($_POST['state'])."'";
}

....
$where = implode(" AND ", $where);
6

Ok awesome this helps.
Now how do I get it to work with multiple fields, and only if they are selected.
Example.
If the admin selects to only see courses in location “a” (depending on drop down) and in city “b”( listed in drop down).
However if he doesnt select anything for city, all classes in location “a” load.

7

This i may have confused all of us a bit.
What i meant was what if the admin selects to view courses in city “Miami” with instructor “Jason Little”. Obviously only classes in Miami with Jason Little as instructor would be returned, but if he only selelcts Miami, then all classes should show. This is where IM having a problem.
The form is causing the URL to appear with blanks. and how do i structure the query to ignore those blanks…

Example: http://www.sitename.com/admin/listcourses.php?city=Miami&instructor=

8 
if (isset($_POST['state']) && trim($_POST['state']) <> '') {
9

In honestly im more confused than ever. Ill take some time to do some reading as obviously im not getting something here. thank you all anyway

10

Ok starlion,
I think this will work.
so the $where under the … goes in the sql stateemnt correct?
Also can you give me a sample of how it would look with multiple Post fields?
and hoping if one of them is blank, it will not use that field to search.

11 
$where = array();
if (isset($_POST['state']) && trim($_POST['state']) != ''){
$where[] ="locations.locationstate='".mysql_escape_string($_POST['state'])."'";
}
//Repeat previous 3 lines for each value you want to potentially search for.
$where = implode(" AND ", $where); 

//Rest of code as before in post 4
12

Starlions reply is good, but I am a bit bemused to see your example in #7 above uses GET values when the rest of the convo is about POST.

Using GET in this type of situation can be a good idea because you can:

a) go back and forth easily
b) bookmark searches (or more properly “filters”)
c) share links
4) permit power users to manipulate the address bar to filter results
5) have meaningful uris that you can link to from other parts of your app – article mentioning Miami? Link directly to courses in Miami.
6) eventually use something like mod_rewrite to improve uris :

your site com/courses/Miami/Jason+Little

is actually being served from:

you site com/listcourses.php?city=Miami&trainer=Jason+Little

Some of which may not be of too much use because you seem to be making and Admin protected app, but - just sayin’.

13

Ok this is what i got going on.
Code is as below.
Getting an error:

You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near 'scheduledcourses.coursetype='1' AND scheduledcourses.courseinstructor='2' AND sc' at line 1

Code:


where = array();
if (isset($_GET['coursenumber']) && trim($_GET['coursenumber']) != ''){
$where[] ="scheduledcourses.scheduledcoursesid='".mysql_escape_string($_GET['coursenumber'])."'";
}
if (isset($_GET['coursetype']) && trim($_GET['coursetype']) != ''){
$where[] ="scheduledcourses.coursetype='".mysql_escape_string($_GET['coursetype'])."'";
}
if (isset($_GET['instructor']) && trim($_GET['instructor']) != ''){
$where[] ="scheduledcourses.courseinstructor='".mysql_escape_string($_GET['instructor'])."'";
}
if (isset($_GET['location']) && trim($_GET['location']) != ''){
$where[] ="scheduledcourses.courselocationid='".mysql_escape_string($_GET['instructor'])."'";
}
$where = implode(" AND ", $where); 
//end
mysql_select_db($database_db, $db);
$query_rsSchedCourses = "SELECT   coursetypes.coursetypename,   scheduledcourses.coursedate,   scheduledcourses.coursetime,   scheduledcourses.courseendtime,   locations.locationname,   instructors.instructorname,   instructors.instructorlastname,   scheduledcourses.coursecost,   scheduledcourses.scheduledcoursesid,   scheduledcourses.coursenumberofseats,   scheduledcourses.notes,   locations.locationcity,   locations.locationstate,   locations.locationzip,   scheduledcourses.privatecourse FROM   scheduledcourses   INNER JOIN coursetypes ON (scheduledcourses.coursetype = coursetypes.coursetypesid)   INNER JOIN locations ON (scheduledcourses.courselocationid = locations.locationsid)   INNER JOIN instructors ON (scheduledcourses.courseinstructor = instructors.instructorsid) WHERE   scheduledcourses.coursestatus = 'Scheduled' $where ORDER BY   scheduledcourses.coursedate";
$rsSchedCourses = mysql_query($query_rsSchedCourses, $db) or die(mysql_error());
$row_rsSchedCourses = mysql_fetch_assoc($rsSchedCourses);
$totalRows_rsSchedCourses = mysql_num_rows($rsSchedCourses);
14

If you do an echo of $query_rsSchedCourses you can check the actual query that you’re running.
But from a first look I’d say that you’re missing an AND in front of $where.

15

Tried that…no luck.

16

What is the result of the echo then? Post it here.

17

Ok I have been playing. Here is what I have figured out.
I added the “AND” and if no filter results are in the url, (http://www.mydomain.com/managecourses.php) I get the error below:
Here is the “echo”


SELECT coursetypes.coursetypename, scheduledcourses.coursedate, scheduledcourses.coursetime, scheduledcourses.courseendtime, locations.locationname, instructors.instructorname, instructors.instructorlastname, scheduledcourses.coursecost, scheduledcourses.scheduledcoursesid, scheduledcourses.coursenumberofseats, scheduledcourses.notes, locations.locationcity, locations.locationstate, locations.locationzip, scheduledcourses.privatecourse FROM scheduledcourses INNER JOIN coursetypes ON (scheduledcourses.coursetype = coursetypes.coursetypesid) INNER JOIN locations ON (scheduledcourses.courselocationid = locations.locationsid) INNER JOIN instructors ON (scheduledcourses.courseinstructor = instructors.instructorsid) WHERE scheduledcourses.coursestatus = 'scheduled' AND ORDER BY scheduledcourses.coursedateYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY scheduledcourses.coursedate' at line 1

If I manually add something to the URL (http://www.mydomain.com/managecourses.php?instructor=1
it works. Obviously because it has an AND. (scheduledcourses.coursestatus=‘scheduled’ AND instructor=‘1’.

Now if I remove the AND, the plain URL loads fine. (http:/www.mydomain.com/managecourses.php) but if i use the dropdown boxes (which add to the URL) I get the errorbelow. Obviously because there is no AND


SELECT coursetypes.coursetypename, scheduledcourses.coursedate, scheduledcourses.coursetime, scheduledcourses.courseendtime, locations.locationname, instructors.instructorname, instructors.instructorlastname, scheduledcourses.coursecost, scheduledcourses.scheduledcoursesid, scheduledcourses.coursenumberofseats, scheduledcourses.notes, locations.locationcity, locations.locationstate, locations.locationzip, scheduledcourses.privatecourse FROM scheduledcourses INNER JOIN coursetypes ON (scheduledcourses.coursetype = coursetypes.coursetypesid) INNER JOIN locations ON (scheduledcourses.courselocationid = locations.locationsid) INNER JOIN instructors ON (scheduledcourses.courseinstructor = instructors.instructorsid) WHERE scheduledcourses.coursestatus = 'scheduled' scheduledcourses.courselocationid='' ORDER BY scheduledcourses.coursedateYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'scheduledcourses.courselocationid='' ORDER BY scheduledcourses.coursedate' at line 1

How can we make the AND happen only when we need it to?

18

After the implode, check if $where has any content, and if it does add “AND” in front of it. Then, use it in the query.

19

Line before the implode:

if(count($where) > 0) { array_unshift(“”); }

(It will shift an empty value into the front of the array, implode will then tack an AND in there, and it’ll work.)

20

Guys, Thanks so much for helping me out.

This is whats happening now.


if(count($where) > 0) { array_unshift(""); }

Got This:


Fatal error: Only variables can be passed by reference in /home/courses/public_html/admin/managecourses.php on line 115

Full Code:


$where = array();
if (isset($_GET['coursenumber']) && trim($_GET['coursenumber']) != ''){
$where[] ="scheduledcourses.scheduledcoursesid='".mysql_escape_string($_GET['coursenumber'])."'";
}
if (isset($_GET['coursetype']) && trim($_GET['coursetype']) != ''){
$where[] ="scheduledcourses.coursetype='".mysql_escape_string($_GET['coursetype'])."'";
}
if (isset($_GET['instructor']) && trim($_GET['instructor']) != ''){
$where[] ="scheduledcourses.courseinstructor='".mysql_escape_string($_GET['instructor'])."'";
}
if (isset($_GET['location']) && trim($_GET['location']) != ''){
$where[] ="scheduledcourses.courselocationid='".mysql_escape_string($_GET['instructor'])."'";
}
if(count($where) > 0) { array_unshift(""); }
$where = implode(" AND ", $where); 

mysql_select_db($database_db, $db);
echo $query_rsSchedCourses = "SELECT   coursetypes.coursetypename,   scheduledcourses.coursedate,   scheduledcourses.coursetime,   scheduledcourses.courseendtime,   locations.locationname,   instructors.instructorname,   instructors.instructorlastname,   scheduledcourses.coursecost,   scheduledcourses.scheduledcoursesid,   scheduledcourses.coursenumberofseats,   scheduledcourses.notes,   locations.locationcity,   locations.locationstate,   locations.locationzip,   scheduledcourses.privatecourse FROM   scheduledcourses   INNER JOIN coursetypes ON (scheduledcourses.coursetype = coursetypes.coursetypesid)   INNER JOIN locations ON (scheduledcourses.courselocationid = locations.locationsid)   INNER JOIN instructors ON (scheduledcourses.courseinstructor = instructors.instructorsid) WHERE scheduledcourses.coursestatus = 'scheduled' $where ORDER BY   scheduledcourses.coursedate";
$rsSchedCourses = mysql_query($query_rsSchedCourses, $db) or die(mysql_error());
$row_rsSchedCourses = mysql_fetch_assoc($rsSchedCourses);
$totalRows_rsSchedCourses = mysql_num_rows($rsSchedCourses);
$colname_rsCancelCourse = "-1";
if (isset($_GET['cancelcourseid'])) {
  $colname_rsCancelCourse = $_GET['cancelcourseid'];
}