SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Filter Results after all are displayed

    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.Filter.png
    See image and see mysql code.

    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. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can you append criteria to your where statement?

    Michael

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im sure that is the answer but thats what i dont know how to do. Can you give me an example?

  4. #4
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if ($_POST['state']){
    $where.=" locations.locationstate='".mysql_escape_string($_POST['state'])."' ";

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

    ....
    $where implode(" AND "$where); 
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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/listco...mi&instructor=

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    if (isset($_POST['state']) && trim($_POST['state']) <> '') { 

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    PHP Code:
    if ($_POST['state']){
    $where.=" locations.locationstate='".mysql_escape_string($_POST['state'])."' ";

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

    ....
    $where implode(" AND "$where); 
    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. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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 
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #13
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok this is what i got going on.
    Code is as below.
    Getting an error:

    HTML Code:
    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:
    PHP 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. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #15
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried that...no luck.

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    What is the result of the echo then? Post it here.

  17. #17
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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"
    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' 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

    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' 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. #18
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #19
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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.)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  20. #20
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guys, Thanks so much for helping me out.

    This is whats happening now.
    PHP Code:
    if(count($where) > 0) { array_unshift(""); } 

    Got This:
    Code:
    Fatal error: Only variables can be passed by reference in /home/courses/public_html/admin/managecourses.php on line 115
    Full Code:
    PHP 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'];


  21. #21
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    er dur. gotta tell it WHERE to unshift, SL.

    if(count($where) > 0) { array_unshift($where,""); }
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  22. #22
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guys that worked. THANK YOU ALL SO MUCH!!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •