Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts

    help and advice on building a query string based on $_Get Variables

    Hi

    Could someone please help me, i have a form which filters results from a mysql database but im not sure what is the best approach?

    the form submits and passes the values using the $_GET variable so i have a url something like this

    Code:
    website.com/results.php?l=993&d=18&t=1&s=&filter-button=submit
    but whats the best way to build the query string so even if the $_GET variables are empty i get a valid query string?

    i have tried this
    PHP Code:
    <?php 
    require("config.php");
    $sQuery "SELECT *
        FROM tbl_jobs AS job
        LEFT JOIN tbl_job_department AS jdept ON job.jobID = jdept.jobID
        LEFT JOIN tbl_job_location AS loc ON job.jobID = loc.jobID
        LEFT JOIN tbl_places AS place ON loc.placeID = place.placeID
        LEFT JOIN tbl_departments as dept ON jdept.deptID = dept.deptID"
    ;
    $eQuery " ORDER BY createdDate DESC";
    if(isset(
    $_GET['l']) && $_GET['l'] <> '')
    {
        
    $placeID $_GET['l'];
        
    $fQuery "place.placeID = $placeID";
        
    $mQuery " WHERE ";
        
        if(isset(
    $_GET['d']) && $_GET['d'] <> '')
        {
            
    $deptID $_GET['d'];
            
    $q $mQuery.$fQuery." AND dept.deptID = $deptID";
        }
        else
        {
            
    $q $mQuery.$fQuery;
        }
    }
    if(isset(
    $_GET['d']) && $_GET['d'] <> '')
    {
        
    $deptID $_GET['d'];
        
    $fQuery "dept.deptID = $deptID";
        
    $mQuery " WHERE ";
        
        if(isset(
    $_GET['l']) && $_GET['l'] <> '')
        {
            
    $placeID $_GET['l'];
            
    $q $mQuery.$fQuery." AND place.placeID = $placeID";
        }
        else
        {
            
    $q $mQuery.$fQuery;
        }
    }

        
    $sql $sQuery.$q.$eQuery;//put the query string together

    ?>
    but it seems very long winded especally as im only checking 2 of the 5 $_GET variables.

    there must be a better way to do this?

    any help would be much appreciated.
    thanks

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Well, you'd *never* nest them like that. You'd simply keep assembling on just the outer branch.
    If you want to make it easier than use an associative array to reference the input name to the property name. Then simply loop it and check if its not empty and if its not append it to the string. Keep a counter to determine when to add the AND, or reassemble / walk the array and generate a new array with the string you need and implode it with an AND.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,024
    Thanks
    2
    Thanked 314 Times in 306 Posts
    Code:
    $where_terms = array();
    if(isset($_GET['l']) && $_GET['l'] <> '')
    {
        $placeID = $_GET['l'];
        $where_terms[] = "place.placeID = $placeID";
    }
    if(isset($_GET['d']) && $_GET['d'] <> '')
    {
    	$deptID = $_GET['d'];
    	$where_terms[] = "dept.deptID = $deptID";
    }
    
    $q = ''; // default to empty string (no where clause)
    if(!empty($where_terms)){
    	$q = "WHERE " . implode(' AND ',$where_terms);
    }
    
    $sql = $sQuery.$q.$eQuery;//put the query string together
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    LJackson (09-11-2013)

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Hi Fou-Lu

    Well, you'd *never* nest them like that.
    Oh Bugger lol

    You'd simply keep assembling on just the outer branch.
    so just check each of the $GET variables once and dont re-check each of them if if one of the others is set?

    If you want to make it easier than use an associative array to reference the input name to the property name. Then simply loop it and check if its not empty and if its not append it to the string. Keep a counter to determine when to add the AND, or reassemble / walk the array and generate a new array with the string you need and implode it with an AND.
    sounds like fun could you elaborate on this please, cant get my head around setting it up.

    Thanks Mate

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    @Fou-Lu

    is CFMaBiSmAd's solution what you were saying to do?
    thanks

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    @CFMaBiSmAd - thanks for the code mate definately a lot cleaner and easier to follow than mine

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    No, my suggestion is to use an array of what's possible. The imploded array is the same.
    PHP Code:
    $aFilterOptions = array(
        
    'l' => 'place.placeid',
        
    'd' => 'dept.deptid'
    );

    $aFilters = array();

    foreach (
    $aFilterOptions AS $identifier => $property)
    {
        if (!empty(
    $_GET[$identifier]))
        {
            if (
    is_numeric($_GET[$identifier]))
            {
                
    $sFormat "%s = %f";
            }
            else
            {
                
    $sFormat "%s = '%s'";
            }
            
    $aFilters[] = sprintf($sFormat$property$_GET[$identifier]);
        }
    }

    if (!empty(
    $aFilters))
    {
        
    $sWhere implode(' AND '$aFilters);

    Edit:
    Oh yeah you'll want to make sure you do whatever sanitizing you need as well.
    Last edited by Fou-Lu; 09-11-2013 at 03:00 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ah i see

    thanks for showing me!!

    Oh yeah you'll want to make sure you do whatever sanitizing you need as well.
    good point! thanks


  •  

    Posting Permissions

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