Query fun

[anthonydamasco]

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?

 

 

 

 

[jonsjava]

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"

[discomatt]

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.

[anthonydamasco]

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'

[anthonydamasco]

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.

[discomatt]

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

[anthonydamasco]

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']) {

[craygo]

need second parenthesis

 

if ( isset($_POST['colors']) && is_array($_POST['colors'])) {

 

ray

[anthonydamasco]

now im getting this error