AceKicker Posted December 7, 2006 Share Posted December 7, 2006 Hi all, I'm still learning when it comes to php and sql so please bear with me.Right now I have a simple MySql database for a list of items, each item is basically a color combination of 3 predefined colors in a set order. So each row would have a 'name' columb, and 3 color columb which for the sake of simplification here we'll call 'left_color', 'middle_color' and 'right_color'. I have it now so you can select what order to display them all in by 'name' or 'left_color'.I've got a working php page which displays them all in 10 item pages, ordered by either 'name' or 'left_color'.What I'm looking to do is to add a function where you can select what colors you're looking for, filter the results, and display all color combinations where those colors are present. My idea was to make a form with 3 drop down menus, with the second two being optional. Each menu would have all the colors available. The user would select up to 3 colors from those menus, hit submit, and it would run a select where it would display all color combinations that had all the selected colors present, regardless of order.It's what that code should be that runs that I'm having trouble working on. I tried sending the results to a seperate php file, where it would take the values from the drop-downs and comple a string with the proper WHERE statement, assign that string to a variable, and return it to the first page adding that WHERE string to the previous SELECT command. Obviously so far I haven't gotten it to work. So my 2 questions are 1) Does this even sound like a good way of doing this, and if not what would you suggest, and 2) what SQL command would you suggest for searching for multiple values in multiple fields in the same table?If you need anything from me, like code specifics, let me know. Link to comment https://forums.phpfreaks.com/topic/29848-multiple-value-filter/ Share on other sites More sharing options...
sanfly Posted December 7, 2006 Share Posted December 7, 2006 Maybe using [url=http://dev.mysql.com/doc/refman/5.0/en/union.html]mysql Union[/url] would be the best way.You would also need to use a different query depending on how many colours selectedTry something along these lines[code=php:0]if($col_1 && $col_2 && $col_3){$q = "(SELECT * FROM your_table_name WHERE left_color = '$col_1' OR mid_color = '$col_1' OR right_color = '$col_1') UNION (SELECT * FROM your_table_name WHERE left_color = '$col_2' OR mid_color = '$col_2' OR right_color = '$col_2') UNION (SELECT * FROM your_table_name WHERE left_color = '$col_3' OR mid_color = '$col_3' OR right_color = '$col_3') ORDER BY name";$r = mysql_query($r) or die(mysql_error());}elseif($col_1 && $col_2){if($col_1 && $col_2 && $col_3){$q = "(SELECT * FROM your_table_name WHERE left_color = '$col_1' OR mid_color = '$col_1' OR right_color = '$col_1') UNION (SELECT * FROM your_table_name WHERE left_color = '$col_2' OR mid_color = '$col_2' OR right_color = '$col_2') ORDER BY name";$r = mysql_query($r) or die(mysql_error());}else{$q = "SELECT * FROM your_table_name WHERE left_color = '$col_1' OR mid_color = '$col_1' OR right_color = '$col_1' ORDER BY name";$r = mysql_query($r) or die(mysql_error());}[/code]There may be a syntax error or something in there, but im sure you get the drift. I [i]think[/i] that will do what you want Link to comment https://forums.phpfreaks.com/topic/29848-multiple-value-filter/#findComment-137138 Share on other sites More sharing options...
AceKicker Posted December 7, 2006 Author Share Posted December 7, 2006 It looks like for the SQL code we were thinking along the same lines, except I tried to put it into one query where an unspecified option would default to '*'. Wasn't completely sure if that would work, but at least as far as basic structure goes I've got someone out there that thinks alike. Now on to figuring the best way of passing that into the search and refreshing the page I guess. Link to comment https://forums.phpfreaks.com/topic/29848-multiple-value-filter/#findComment-137141 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.