SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Error in count query!

    Can anyone tell me why the following query is returning an error?
    I can't see anything wrong with it. I've echoed the query and it doesn't seem to be taking any notice of the if statements.

    PHP Code:
    $search_type $_GET['search_type'];
    $top_make $_GET['top_make'];
    $top_model $_GET['top_model'];
    $thisYear date("Y")-1;
    $today date("Y-m-d");

    if(
    $search_type "used"){ $MyTable 'Used_Stock';}
    if(
    $search_type "vans"){ $MyTable 'Used_Stock';}
    if(
    $search_type "bikes"){ $MyTable 'Used_Stock';}
    if(
    $search_type "nearlynew"){ $MyTable 'Used_Stock';}
    if(
    $search_type "new"){ $MyTable 'New_Offers';}
    if(
    $search_type "motability"){ $MyTable 'Motability_Offers';}

        
    //build query
    $howmanyquery "SELECT COUNT(*) as NUM FROM $MyTable";
    if(
    $search_type "used")
        
    $howmanyquery .= " WHERE Type='2'";
    if(
    $search_type "vans")
        
    $howmanyquery .= " WHERE Type='4'";

    if(
    $search_type "bikes")
        
    $howmanyquery .= " WHERE Type='6'";

    if(
    $search_type "nearlynew")
        
    $howmanyquery .= " WHERE Type='2' AND Year = '$thisYear'";

    if(
    $search_type "new")
        
    $howmanyquery .= " WHERE Make !='Harley-Davidson' AND Valid >='$today'";

    if(
    $search_type "motability")
        
    $howmanyquery .= " WHERE Expiry >='$today'";

    if(
    $top_make != "")
        
    $howmanyquery .= " AND Make = '$top_make'";

    if(
    $top_model != "")
        
    $howmanyquery .= " AND Model LIKE '%$model%'";

        
    //Execute query
    $total_results mysql_result(mysql_query("$howmanyquery"),0);

    echo 
    "" .$total_results"";//Shows found number of results in db
    echo "Query:" .$howmanyquery"";//Shows found number of results in db 

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    There should be a song about this somewhere.
    You've given every one of those IF statements a definition, not a comparison. (= vs ==) !

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, daft mistake to make though after another straight 14hrs coding I am starting to miss things!! Appreciate the help once agian.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,187
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    A switch statement is ideal for what your doing, not stacking multiple if conditions. To that end I took the liberty of running clean-up including adding proper escaping and input checking to make the script more robust. Never assume user input exists, that is a bad assumption to make. Always check make sure the input exists before assigning it to a variable. Also always use mysql_real_escape_string() when using the standard library to protect against SQL injection.

    (untested)
    PHP Code:
    $search_type = isset($_GET['search_type'])?$_GET['search_type']:null;
    $top_make = isset($_GET['top_make'])?$_GET['top_make']:null;
    $top_model = isset($_GET['top_make'])?$_GET['top_model']:null;

    $thisYear date("Y")-1;
    $today date("Y-m-d");

    $querytpl 'SELECT COUNT(*) as NUM FROM %s %s';

    $MyTable 'Used_Stock';
    $myFilter '';

    switch((string) 
    $search_type) {

        case 
    'used':
            
    $myFilter "WHERE Type='2'";
            break;
        
        case 
    'vans':
            
    $myFilter "WHERE Type='4'";
            break;
        
        case 
    'bikes':
            
    $myFilter "WHERE Type='6'";
            break;
        
        case 
    'nearlynew':
            
    $myFilter "WHERE Type='2' AND Year = '$thisYear'";
            break;
        
        case 
    'new':
            
    $MyTable 'New_Offers';
            
    $myFilter "WHERE Make !='Harley-Davidson' AND Valid >='$today'";
            break;
        
        case 
    'motability':
            
    $MyTable 'Motability_Offers';
            
    $myFilter "WHERE Expiry >='$today'";
            break;
        
        default:

    }

    if(
    $top_make !== null) {
        
    $myFilter.= ($myFilter == ''?'WHERE ':' AND ')."Make = '".mysql_real_escape_string($top_make)."'";
    }

    if(
    $top_model !== null)
        
    $myFilter.= ($myFilter == ''?'WHERE ':' AND '),"Model LIKE '%".mysql_real_escape_string($top_model)."%'";
    }


    $howmanyquery sprintf($querytpl,$MyTable,$myFilter);

    //Execute query
    $total_results mysql_result(mysql_query($howmanyquery),0);

    echo 
    "" .$total_results"";//Shows found number of results in db
    echo "Query:" .$howmanyquery"";//Shows found number of results in db 
    The only code I hate more than my own is everyone else's.


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
  •