anthonydamasco Posted April 17, 2008 Share Posted April 17, 2008 Hey, Here is my query "SELECT * FROM products WHERE collection LIKE '%$collection%' AND construction LIKE '%$construction%' AND match1 LIKE '%$match1%' AND wheremade_e LIKE '%$wm_en%' AND pattern LIKE '%$keyword%' AND sku LIKE '%$keyword%' AND color_e LIKE '%$Beige%' OR '%$colorall%' OR '%$Black%' OR '%$Blue%' OR '%$Brown%' OR '%$Gold%' OR '%$Green%' OR '%$Grey%' OR '%$Neutral%' OR '%$Red%' OR '%$Rust%' OR '%$Seafoam%' OR '%$White%' AND fiber LIKE '%$fiberall%' OR '$Wool' OR '$Silk_and_Wool' OR '$Sisal' OR '$Cotton' OR '$Mixed' LIMIT $eu, $limit" the posting page has check boxes for each color, the only color that is actually being called is "beige" if i check any other color check box i get no results. Obviously there is something wrong with my query. any suggestions? Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/ Share on other sites More sharing options...
jonsjava Posted April 17, 2008 Share Posted April 17, 2008 been a while since I needed an "AND/OR" statement, so I hope I'm rignt........... "SELECT * FROM products WHERE collection LIKE '%$collection%' AND construction LIKE '%$construction%' AND match1 LIKE '%$match1%' AND wheremade_e LIKE '%$wm_en%' AND pattern LIKE '%$keyword%' AND sku LIKE '%$keyword%' AND color_e LIKE '%$Beige%' OR color_e LIKE'%$colorall%' OR color_e LIKE '%$Black%' OR color_e LIKE '%$Blue%' OR color_e LIKE '%$Brown%' OR color_e LIKE '%$Gold%' OR color_e LIKE '%$Green%' OR color_e LIKE '%$Grey%' OR color_e LIKE '%$Neutral%' OR color_e LIKE '%$Red%' OR color_e LIKE '%$Rust%' OR color_e LIKE '%$Seafoam%' OR color_e LIKE '%$White%' AND fiber LIKE '%$fiberall%' OR '$Wool' OR fiber LIKE '%$Silk_and_Wool%' OR fiber LIKE '%$Sisal%' OR fiber LIKE '%$Cotton%' OR fiber LIKE '%$Mixed%' LIMIT $eu, $limit" Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519694 Share on other sites More sharing options...
discomatt Posted April 17, 2008 Share Posted April 17, 2008 A good way to solve this on your own is trying to echo mysql_error(); You should be building your query dynamically... Why query for things that don't matter? <?php // Assuming you have an array of colours returned by checkbox; foreach($_POST['colors'] as $color) // Basic sanitization and add wildcards $_POST['colors'] = '\'%'. mysql_real_escape_string( $color ) .'%\''; $query_color_e = implode(' OR ', $_POST['colors']); $query = 'SELECT * FROM `products` WHERE `color_e` LIKE ' . $query_color_e; ?> Just as an example. Leaves for a nice clean query and less of a chance for mistakes. Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519709 Share on other sites More sharing options...
anthonydamasco Posted April 17, 2008 Author Share Posted April 17, 2008 thats, but that doesnt seem to work either, http://pattersonflynnandmartin.com/advanced_search.php when you search with any color checked, it seems to ignore the "AND collection like '$collection' Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519712 Share on other sites More sharing options...
anthonydamasco Posted April 17, 2008 Author Share Posted April 17, 2008 how would i set up the array to use this? sorry, im still a little junior A good way to solve this on your own is trying to echo mysql_error(); You should be building your query dynamically... Why query for things that don't matter? <?php // Assuming you have an array of colours returned by checkbox; foreach($_POST['colors'] as $color) // Basic sanitization and add wildcards $_POST['colors'] = '\'%'. mysql_real_escape_string( $color ) .'%\''; $query_color_e = implode(' OR ', $_POST['colors']); $query = 'SELECT * FROM `products` WHERE `color_e` LIKE ' . $query_color_e; ?> Just as an example. Leaves for a nice clean query and less of a chance for mistakes. Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519717 Share on other sites More sharing options...
discomatt Posted April 17, 2008 Share Posted April 17, 2008 Make your checkboxes like this: <div class="checkbox_pair"><input type="checkbox" name="colors[]" value="beige"> Beige</div> <div class="checkbox_pair"><input type="checkbox" name="colors[]" value="black"> Black</div> <div class="checkbox_pair_last"><input type="checkbox" name="colors[]" value="blue"> Blue</div> ect... This will return all checked values in an array, $_POST['colors'] It will only populate checked values And because you're new, I'll show you the proper way to do my code above... with error checking <?php // Make sure its set and an array (if no colors were checked it won't be) if ( isset($_POST['colors']) && is_array($_POST['colors']) { foreach($_POST['colors'] as $color) // Basic sanitization and add wildcards $_POST['colors'] = '\'%'. mysql_real_escape_string( $color ) .'%\''; $query_color_e = implode(' OR ', $_POST['colors']); } $query = 'SELECT * FROM `products` WHERE `color_e` LIKE ' // If isset($query_color_e) return (?) $query_color_e, else ( return nothing . ( isset($query_color_e) ? $query_color_e : '' ); ?> I personally think all variables should be checked for existence before being used, and that's what all the issets are for... even with error_reporting(E_ALL), no errors should be displayed Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519730 Share on other sites More sharing options...
anthonydamasco Posted April 17, 2008 Author Share Posted April 17, 2008 im getting an error when trying to use that code Parse error: syntax error, unexpected '{' on this line: if ( isset($_POST['colors']) && is_array($_POST['colors']) { Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519796 Share on other sites More sharing options...
craygo Posted April 17, 2008 Share Posted April 17, 2008 need second parenthesis if ( isset($_POST['colors']) && is_array($_POST['colors'])) { ray Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519797 Share on other sites More sharing options...
anthonydamasco Posted April 17, 2008 Author Share Posted April 17, 2008 now im getting this error Link to comment https://forums.phpfreaks.com/topic/101585-query-fun/#findComment-519804 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.