SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 'Any' isn't working - ideas?

    This is the form I am working on.
    http://projectors.tvspecialists.com/search.php

    I'm trying to make each item an option so that you do not have to select something on every item to submit it. Right now if you do not select something for manufacturer, category, display, and resolution - nothing pulls up (others are all disabled).

    I'm specifically working on 'Manufacturer' - If nothing is selected I'd like it to search through all of the manufacturers. I've had it where if you select nothing, it defaults and plugs in 'Any Manufacturer' because it was given no value, then on the following page it shows 'Any Manufactuer' in the query - but it's not really looking through all the manufacturers.

    I've also tried making 'Any Manufacturer' a value of 'any' then writing something like this on the following page:

    if ($manufacturer == "any")
    {$manufacturer = "(manufacturer = 'Panasonic' OR manufacturer = 'JVC' OR manufacturer = 'Optoma' OR manufacturer = 'Philips' OR manufacturer = 'Sharp' OR manufacturer = 'Sony')";
    }

    The query string is echoed on the results page at the top.

    These selections should pull up projectors (for testing purposes):

    Category: Home Theater
    Type or display: DLP
    ANSI Lumens: Over 3000
    Resolution: SXGA (1280 x 1024 pixels)

    That should pull up a Panasonic PT-D7600 projector
    It works if you select Panasonic as the manufacturer.
    -----------------

    Category: Home Theater
    Type of display: DLP
    ANSI Lumens: Under 1500
    Resolution: SVGA (800 x 600 pixels)

    That should pull up an Optoma H30
    It works if you select Optoma as the manufacturer.
    -------------------

    Category: Business
    Type of display: LCD
    ANSI Lumens: Under 1500
    Resolution: SVGA (800 x 600 pixels)

    That should pull up a Panasonic PT-LM1U
    It works if you select Panasonic as the manufacturer
    -----------------------

    This is the error that comes up currently:

    SELECT * FROM projectors WHERE manufacturer = '(manufacturer = 'Panasonic' OR manufacturer = 'JVC' OR manufacturer = 'Optoma' OR manufacturer = 'Philips' OR manufacturer = 'Sharp' OR manufacturer = 'Sony')' AND (FIND_IN_SET('Home Theater',projectors.category)>0) AND type = 'LCD' AND resolution = 'SVGA' AND (lumens>0) AND (lumens<1500) 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 'Panasonic' OR manufacturer = 'JVC' OR manufacturer = 'Optoma' O

    Can someone help me???
    Last edited by tvspec; Sep 15, 2004 at 12:17.

  2. #2
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just pull selections where "manufacturer != '' " instead of checking if it is one of any of the groups?

  3. #3
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure what you mean (I'm new to php)

    do you mean write this part like this instead?

    if ($manufacturer != "")
    {$manufacturer = "(manufacturer = 'Panasonic' OR manufacturer = 'JVC' OR manufacturer = 'Optoma' OR manufacturer = 'Philips' OR manufacturer = 'Sharp' OR manufacturer = 'Sony')";
    }

    Quote Originally Posted by devil cat
    Why not just pull selections where "manufacturer != '' " instead of checking if it is one of any of the groups?

  4. #4
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry. Let me explain better.

    When you have $manufacturer == 'any', instead of running a database query like this:

    SELECT * FROM projectors WHERE manufacturer = '(manufacturer = 'Panasonic' OR manufacturer = 'JVC' OR manufacturer = 'Optoma' OR manufacturer = 'Philips' OR manufacturer = 'Sharp' OR manufacturer = 'Sony')' AND (FIND_IN_SET('Home Theater',projectors.category)>0) AND type = 'LCD' AND resolution = 'SVGA' AND (lumens>0) AND (lumens<1500)

    why not just use:

    SELECT * FROM projectors WHERE manufacturer != '' AND (FIND_IN_SET('Home Theater',projectors.category)>0) AND type = 'LCD' AND resolution = 'SVGA' AND (lumens>0) AND (lumens<1500)

    It means a little more dynamic query, but it is easier to update if you add another manufacturer.

  5. #5
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So then, if I use that query, can I get rid of the 'if' statement? And it will still work if something IS or IS NOT selected?

    Quote Originally Posted by devil cat
    Sorry. Let me explain better.

    When you have $manufacturer == 'any', instead of running a database query like this:

    SELECT * FROM projectors WHERE manufacturer = '(manufacturer = 'Panasonic' OR manufacturer = 'JVC' OR manufacturer = 'Optoma' OR manufacturer = 'Philips' OR manufacturer = 'Sharp' OR manufacturer = 'Sony')' AND (FIND_IN_SET('Home Theater',projectors.category)>0) AND type = 'LCD' AND resolution = 'SVGA' AND (lumens>0) AND (lumens<1500)

    why not just use:

    SELECT * FROM projectors WHERE manufacturer != '' AND (FIND_IN_SET('Home Theater',projectors.category)>0) AND type = 'LCD' AND resolution = 'SVGA' AND (lumens>0) AND (lumens<1500)

    It means a little more dynamic query, but it is easier to update if you add another manufacturer.

  6. #6
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would still need an if statement, but modify it a little bit:

    Code:
    if ($manufacturer == 'any') {
       $manufacturer_query = "manufacturer != ''";
    }
    else {
       $manufacturer_query = "manufacturer = '".$manufacturer."'"
    }
    
    "SELECT * FROM projectors WHERE ".$manufacturer_query." AND (FIND_IN_SET('Home Theater',projectors.category)>0) AND type = 'LCD' AND resolution = 'SVGA' AND (lumens>0) AND (lumens<1500)
    Give that a shot.

  7. #7
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm getting this error now:

    Parse error: parse error, unexpected '}' in /home/caotcnag/public_html/projectors/results.php on line 15

    This is the code there:

    10 if ($manufacturer == 'any') {
    11 $manufacturer_query = "manufacturer != ''";
    12 }
    13 else {
    14 $manufacturer_query = "manufacturer = '".$manufacturer."'"
    15 }

  8. #8
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    line 14 doesn't end with a ;

    Sorry.

  9. #9
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much!!!!

  10. #10
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're welcome. I take it that worked.

  11. #11
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, one more thing - I came across another problem later:

    if ($display == 'any') {
    $display_query = "display != ''";
    }
    else {
    $display_query = "display = '".$display."'";
    }

    'display' should actually correspond with the a table value called 'type' - I think that's causing problems.

    This is what the query string says now - which do I change? The query string or the little part above?

    $query_Recordset1 = sprintf("SELECT * FROM projectors WHERE ".$manufacturer_query." AND ".$category_query." AND type = '$display' AND resolution = '$resolution' AND (lumens>$lower) AND (lumens<$upper) "

  12. #12
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    type = '$display' isn't going to work that way.

    You would have to have something like this:

    if ($display == 'any') {
    $display_query = "type != ''";
    }
    else {
    $display_query = "type = '".$display."'";
    }


    $query_Recordset1 = sprintf("SELECT * FROM projectors WHERE ".$manufacturer_query." AND ".$category_query." AND ".$display_query." AND resolution = '$resolution' AND (lumens>$lower) AND (lumens<$upper) "

  13. #13
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok that worked, thanks again!

  14. #14
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem

  15. #15
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ugh ok, having a problem with this one too:

    //What to do if no brightness was selected
    if ($ansi == 'any') {
    $lower = "lumens != ''";
    $upper = "lumens != ''";}
    elseif ($ansi == u1500)
    {$lower = 0;
    $upper = 1500;}
    elseif ($ansi == a15a30)
    {$lower = 1500;
    $upper = 3000;}
    elseif ($ansi = o3000)
    {$lower = 3000;
    $upper = 80000;}

    $query_Recordset1 = sprintf("SELECT * FROM projectors WHERE ".$manufacturer_query." AND ".$category_query." AND ".$display_query." AND ".$ansi_query."
    AND (lumens>$lower) AND (lumens<$upper) ORDER BY list_price ASC"

    With this one, you can tell that the value is changed to two variables - then passed on. So this is what I thought it would be...but I'm getting this error:

    SELECT * FROM projectors WHERE manufacturer != '' AND category != '' AND type = 'LCD' AND AND (lumens>lumens != '') AND (lumens<lumens != '') ORDER BY list_price ASCYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND (lumens>lumens != '') AND (lumens<lumens != '') ORDER BY li

  16. #16
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just drop the:

    AND ".$ansi_query."

    completely.

  17. #17
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect. Thanks

  18. #18
    SitePoint Enthusiast tvspec's Avatar
    Join Date
    Mar 2003
    Location
    Utah
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having another problem. The category column in the database is a 'set' - so the ".$category_query." isn't working properly. It is only searching the first item in the set, not the entire set.

    This is what it would be normally in the query:

    (FIND_IN_SET('$category',projectors.category)>0)

    when I change it to just

    ".$category_query."

    It doesn't work properly.

    I'd appreciate the help again.
    Last edited by tvspec; Sep 15, 2004 at 12:18.

  19. #19
    SitePoint Addict devil cat's Avatar
    Join Date
    Apr 2003
    Location
    Reno
    Posts
    344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, that wouldn't work.

    I'm not sure why you are using projectors.category, since you are only looking in one table, you shouldn't have to specify projectors.category, just category.

    I've never tried a FIND_IN_SET, and I cannot find anything on it in the online documentation, so I'm not sure what it's supposed to do...


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
  •