MySQL select with "selections"

[Joveice]

Okey so I have a search function (basic one) and I would like to be able to check of diffrent stuff etc their role, if they are premium, and search by name, all at the same time.

so if I search for "test" it will select test, if I select role test it will find test and test2 since they are both in that role.

All of the how to "select" and those functions I already know how, I just don't know how the mysql select query string should look like to be able to do this.

the way the search function works is that it just creates a url parameter which the "search page" uses etc ?q=jove will find the user jove, and I would like a simple way of doing it this way (if there isent a better way ofc).

Please let me know if I wasent clear enough, my english and explanation isent the best

search is by name, the drop down has values it will search for and checkbox will see if it's true or false

search looks in column uid, Dropdown in role, and checkbox in premium for a o or a 1.

and I want this to be sent in a url parameter and then piced up and used where I have the other end.

[mariushm]

You should use POST for submitting forms to server, so that you don't get a lot of text in the URL. 

Make sure you escape strings before sending them to mysql server, because whatever is submitted to server through POST or GET can be modified by users.

 

If you want to search for rows that have a record with some text, you can use something like 

 

select * from table_name where name LIKE '%searchword%';

 

See the documentation http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

 

If user enters multiple words, you can first separate the words using built in functions like explode () for php and then you could build the query string before submitting the query to the mysql server..

 

ex  (in no specific language)

 

text = 'select * from table_name where '

for (i = 0; i < total_keywords ; i++ ) {

  if (i!=0) text = text + ' OR ' ;

  text = text + ' name LIKE \'%' + keyword + '%\' '  ;   // escape ' by writing it \' inside string variables

}

text = text + ';';

 

and this should form the query string  select * from table_name where name LIKE '%keyword1%' OR name LIKE '%keyword2%';

 

If you want for example to select several categories using checkboxes from a drop down list or through radio buttons , you should basically create another table and populate the drop down list or radio buttons or checkboxes from that table.

 

ex let's say we have 

table_names  : 

id  , name , jobid

1 , john , 1

2, alex , 2

3, maria, 1

 

table_jobs:

id, job

1, Engineer

2, Medic

3, Teacher

If user selects Engineer and Teacher from check boxes, drop down list, radio buttons, whatever and searches for 'john' you'd build a query that looks like this :

 

select  A.*, B.job FROM table_names AS A LEFT JOIN table_jobs AS B ON A.jobid = B.id WHERE A.name LIKE '%john%' AND A.jobid IN (1,3);

 

AS gives a 'nickname' to a table to make it easier to build the query.

Just for kicks, i used left join to bring with me the job titles from a second table..   LEFT JOIN and ON creates a link between two tables and allows you to import records from other tables to your result.

You could have said AND ((A.jobid = 1) OR (A.jobid=3))

and you should get one result:

 

id, name, jobid , job 

1 , john , 1, Engineer

 

and don't trust the user ever. If the form has checkboxes or radio buttons or a drop down list with checkboxes when you parse the fields returned by the form and expect to have 1 or 0 for some fields, don't trust that you'd get 0 or 1, make sure they're actually only digits and not some hacker wannable trying to hack your database. Validate all input. If you get something you didn't expect, ignore that or show an error.message and ask to repeat the search.