Building a better search query php/mysql -



Building a better search query php/mysql -

i'm trying set simple search form - checkboxes - running issues putting query. i'm able homecoming results if 1 location selected, 1 experience or 1 language. if select combination of results spotty @ best. intention homecoming results users with:

experience or b or c , location or b or b or d , languages or b , approved

right now, if select bunch of locations, no other criteria, no results.

what type of query should looking @ when trying search through 20+ languages, 50+ locations, , few other requirements? , how should go building it? on right track?

$adv_search_query = "select users.*, general.*, languages.*, experience.* users left outer bring together languages on users.user_id = languages.user_id left outer bring together general on users.user_id = general.user_id left outer bring together experience on users.user_id = experience.user_id ("; if(!empty($_post['location'])) { foreach($_post['location'] $location) { $location_input = " general.neighborhood '%" . $location . "%' or"; } $adv_search_query .= trim($location_input, 'or'); $adv_search_query .= ") "; } if(!empty($_post['languages']) && !empty($_post['location'])) { $adv_search_query .= "and ("; } if(!empty($_post['languages'])) { foreach($_post['languages'] $language) { $language_input = " languages." . $language . " = 1 or"; } $adv_search_query .= trim($language_input, 'or'); $adv_search_query .= ") "; } if(!empty($_post['experience']) && !empty($_post['location'])) { $adv_search_query .= "and ("; } if(!empty($_post['experience'])) { foreach($_post['experience'] $exp) { $exp_input = " experience." . $exp . " = 1 or"; } $adv_search_query .= trim($exp_input, 'or'); $adv_search_query .= ") "; } if (isset($_post["approved"])) { $approved = " users.approved = 1 or"; } else { $approved = ""; } if (isset($_post["pending"])) { $pending = " users.approved = 2 or"; } else { $pending = ""; } if (isset($_post["incomplete"])) { $incomplete = " users.approved = 0 or"; } else { $incomplete = ""; } if(isset($_post['approved']) || isset($_post['pending']) || isset($_post['incomplete'])) { $status_input = "and (" . $approved . " " . $pending . " " . $incomplete . ""; $adv_search_query .= trim($status_input, 'or'); $adv_search_query .= ") "; } $adv_search_query .= "and users.admin_level = 0";

tables

table.users user_id first_name last_name admin_level user_approved 1 nick jones 0 1 2 johnny rocket 0 1 3 sally fields 0 2 table.general user_id city state zip neighborhood 1 baltimore maryland 00125 hamsterdam 2 lakeland maine 11542 treemont 3 sonic new york 11763 highville table.languages user_id french high german italian spanish 1 0 1 0 1 2 0 0 1 1 3 1 1 1 1 table.experience user_id waldorf kumon homeschooling 1 0 1 0 2 0 0 1 3 1 1 1

first, aware code susceptible sql injection in the:

general.neighborhood

part.

in type of sql query building, "array()" , "implode" friends:

$experience_valid_values = array('exp1', 'exp2'); $experience_conditions = array(); if(!empty($_post['experience'])) { foreach($_post['experience'] $exp) { if (in_array($exp, $experience_valid_values)) { $experience_conditions[] = 'experience.' . $exp . '=1'; } } } $language_valid_values = array('english', 'japanese', 'spanish', 'chinese'); $language_conditions = array(); if(!empty($_post['language'])) { foreach($_post['languages'] $language) { if (in_array($language, $language_valid_values)) { $language_conditions[] = 'language.' . $language . '=1'; } } } $conditions = array(); if (!empty($experience_conditions)) { $conditions[] = '(' . implode(' or ', $experience_conditions) . ')'; } if (!empty($language_conditions)) { $conditions[] = '(' . implode(' or ', $language_conditions) . ')'; } $sql = 'select * users left outer bring together experience on users.user_id = experience.user_id left outer bring together languages on users.user_id = languages.user_id '; $sql .= implode(' , ', $conditions);

using "array()" , "implode" create code shorter , easier read. short illustration hoping give idea.

php mysql search

Comments

Post a Comment

Popular posts from this blog

Delphi change the assembly code of a running process -

Delphi change the assembly code of a running process - i'm trying alter address 00741fa5 has push test.009e721c . alter push test.009e71c8 . procedure callback; asm force $9e71c8 end; procedure tform2.btn1click(sender: tobject); var ppid :dword; pprocess : cardinal; begin getwindowthreadprocessid(findwindow(nil,pchar('test')), @ppid); pprocess := openprocess(process_all_access, false, ppid); injectasm(pprocess, $741fa5, 10, integer(@callback), 0); end; here injectasm function (with error checking) function injectasm(process: longword; injectaddress, injectsize, codeaddress, codesize : integer): pointer; var csize, rsize : integer; replaced : array of byte; jmp : array [0..4] of byte; jmpaddress : integer; nopv : byte; : integer; nbr: ulong_ptr; begin //injectsize must equal or greater 5, because need space our //(far) jmp wwxxyyzz instruction //if there's no space, inject in place of few instructions if injectsize ...
Read more

json - Hibernate and Jackson (java.lang.IllegalStateException: Cannot call sendError() after the response has been committed) -

json - Hibernate and Jackson (java.lang.IllegalStateException: Cannot call sendError() after the response has been committed) - hello have problem hibernate , jackson. have 2 pojos @entity @table(name = "users") public class user implements serializable { static final long serialversionuid = 4235637833262722256l; @id @generatedvalue private int id; @column(name = "creation_date") private date creationdate; @column(name = "first_name") private string firstname; @column(name = "last_name") private string lastname; @version @column(name = "version") private int version; @column(name = "e_mail") private string email; @column(name = "enabled") private boolean enabled; @column(name = "login") private string login; @column(name = "password") private string password; @jsonmanagedreference("role") @onetomany(mappedby = "user", cascade = cascadetype.all,fetch = fetchtyp...
Read more

C++ 11 "class" keyword -

C++ 11 "class" keyword - i've started using c++ 11 little bit more , have few questions on special uses of class keyword. know it's used declare class, there's 2 instances have seen don't understand: method<class t>(); and class class_name *var; why precede typename keyword class in first example, , keyword pointer in sec example? that known elaborated type specifier , necessary when class name "shadowed" or "hidden" , need explicit. class t { }; // love of god don't t t; t t2; if compiler smart, give these warnings: main.cpp:15:5: error: must utilize 'class' tag refer type 't' in scope t t2; ^ class main.cpp:14:7: note: class 't' hidden non-type declaration of 't' here t t; ^ if class not defined, deed forwards declaration. for example: template <typename t> void method() { using type = typename t::value_type; } method...
Read more