Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
2 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to build an SQL query for multiple form fields..?
Posted on 2009-05-20
I have a mySQL database that has about 65 or so fields, each related to an input field on an HTML form. Many of the fields are checkboxes (arrays) on the HTML field, which I have "imploded" into comma separated strings for the database. I have been using "strpos" in order to check if a value exists and display the desired results. (Overcoming the checkbox problem was a big obstacle for me....)
I would like to be able to search my mySQL database, on a variety of different criteria. So my search form would have the same fields (almost) as my input form, and I could search on text value(s) and on checkbox value(s).
My problem is how to build an SQL statement that searches multiple fields for multiple values. All the examples I've come across show only simple SQL examples. If anyone can give any suggestions or links to a good tutorial that would be great.
I would like to be able to search my mySQL database, on a variety of different criteria. So my search form would have the same fields (almost) as my input form, and I could search on text value(s) and on checkbox value(s).
My problem is how to build an SQL statement that searches multiple fields for multiple values. All the examples I've come across show only simple SQL examples. If anyone can give any suggestions or links to a good tutorial that would be great.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
-
4
10 Comments
You need to use a full text search. I had this same issue.
Here is the code I finaaly used. Obviously your table names and fields will be different:
"SELECT id, title, summary, content, tags, thumb, subcategory_id, author_id, MATCH (title, summary, content, tags ) AGAINST ('$search_term' IN BOOLEAN MODE) AS score FROM mydatabasename WHERE published=1 AND MATCH (title, summary, content, tags ) AGAINST ('$search_term' IN BOOLEAN MODE) LIMIT $start, 12 ");
Note that you need to do a full text index on your database. The "dual" match increases the relevance scoring. The $start and l2 are used for pagination.
Here is the code I finaaly used. Obviously your table names and fields will be different:
"SELECT id, title, summary, content, tags, thumb, subcategory_id, author_id, MATCH (title, summary, content, tags ) AGAINST ('$search_term' IN BOOLEAN MODE) AS score FROM mydatabasename WHERE published=1 AND MATCH (title, summary, content, tags ) AGAINST ('$search_term' IN BOOLEAN MODE) LIMIT $start, 12 ");
Note that you need to do a full text index on your database. The "dual" match increases the relevance scoring. The $start and l2 are used for pagination.
Full text search only works with the MyISAM storage engine.
Create the FULL TEXT index with:
ALTER TABLE tablename ADD FULLTEXT(title, summary, content, tags);
Also my use of mydatabasename in the MATCH AGAINST statement is misleading. It should read tablename
Create the FULL TEXT index with:
ALTER TABLE tablename ADD FULLTEXT(title, summary, content, tags);
Also my use of mydatabasename in the MATCH AGAINST statement is misleading. It should read tablename
How do I know which storage engine I have? My MySQL version is quite old (4.1).
So if I want to search against let's say 8 fields (and my table were full text indexed), I could say something like:
SELECT (field1, field2, field3, ... field8) MATCH (field1, field2) AGAINST ("inputted text" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (field3) AGAINST ("checkbox value") AND MATCH (field4) AGAINST ("checkbox value) AND MATCH (field5) AGAINST ("checkbox value") AND ... ?
Also what does "score" represent? Is it a field in the table? I assume it's a relevance score?
So if I want to search against let's say 8 fields (and my table were full text indexed), I could say something like:
SELECT (field1, field2, field3, ... field8) MATCH (field1, field2) AGAINST ("inputted text" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (field3) AGAINST ("checkbox value") AND MATCH (field4) AGAINST ("checkbox value) AND MATCH (field5) AGAINST ("checkbox value") AND ... ?
Also what does "score" represent? Is it a field in the table? I assume it's a relevance score?
If you are using the standard mySQL setup, you are using the MyISAM storage engine.
The original select does not have to be identical to the MATCH fields, but the two MATCH fields should be the same.
The "score" is an alias, it is not an actual field. It is used to allow the results to be priortized by relevance.
To correct your statement:
SELECT (field1, field2, field3, ... field8) MATCH (field1, field2) AGAINST ("inputted text" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (field1, field2) AGAINST ("inputted text" IN BOOLEAN MODE") AND MATCH (ffield1, field2) AGAINST ("inputted text" IN BOOLEAN MODE)
Now searching your checkbox fields is another matter. Are these packed fields alpha or numeric? Can you show me a sample of the data from one of these fields? (e.g. is it "blue, green, yellow, red" or "1,3,5,2" and how many options are their in an entry?
The original select does not have to be identical to the MATCH fields, but the two MATCH fields should be the same.
The "score" is an alias, it is not an actual field. It is used to allow the results to be priortized by relevance.
To correct your statement:
SELECT (field1, field2, field3, ... field8) MATCH (field1, field2) AGAINST ("inputted text" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (field1, field2) AGAINST ("inputted text" IN BOOLEAN MODE") AND MATCH (ffield1, field2) AGAINST ("inputted text" IN BOOLEAN MODE)
Now searching your checkbox fields is another matter. Are these packed fields alpha or numeric? Can you show me a sample of the data from one of these fields? (e.g. is it "blue, green, yellow, red" or "1,3,5,2" and how many options are their in an entry?
So, if I understood correctly, you can only search a limited number of fields in one query. :(
So for example, if my table contains a field "colour" with checkbox values "red", "green", "yellow", "blue", "purple" and another field "units" with checkbox values "miles", "feet", "inches", "metres", "cups", "degrees"
Now say I want all records containing the checkboxes "red" and "blue" and also "inches" "cups" and "degrees" (and query - not OR)
SELECT (colour, units) MATCH (colour) AGAINST ("red, blue" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (units) AGAINST ("inches cups degrees" IN BOOLEAN MODE);
From what I undestand above this cannot be done? Unless I search all fields repeatedly against each string? Like
SELECT (colour, units) MATCH (colour, units) AGAINST ("red blue" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (colour, units) AGAINST ("inches cups degrees" IN BOOLEAN MODE);
?? Which would work?
So for example, if my table contains a field "colour" with checkbox values "red", "green", "yellow", "blue", "purple" and another field "units" with checkbox values "miles", "feet", "inches", "metres", "cups", "degrees"
Now say I want all records containing the checkboxes "red" and "blue" and also "inches" "cups" and "degrees" (and query - not OR)
SELECT (colour, units) MATCH (colour) AGAINST ("red, blue" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (units) AGAINST ("inches cups degrees" IN BOOLEAN MODE);
From what I undestand above this cannot be done? Unless I search all fields repeatedly against each string? Like
SELECT (colour, units) MATCH (colour, units) AGAINST ("red blue" IN BOOLEAN MODE) AS score FROM tablename AND MATCH (colour, units) AGAINST ("inches cups degrees" IN BOOLEAN MODE);
?? Which would work?
Oh I forgot to say that the checkbox values are entered into an array in php, then imploded into a comma separated string. So in the MySQL database, a checkbox field ends up formatted as a single string that looks like "red, green, blue, purple". I use php function explode() to make it back into an array for display purposes.
Hi x13:
Searching your packed fields is difficult if you are also trying to search within unpacked fields without breaking them out using a temporary table.
If you are searching only within packed fields, and you want an exact search, you can pack your search term with the separator, however a comma is not the best separator to use, since it can be included in regular text. A bar | (the character over \ on most keyboards) is a better separator. You can then take your search term, say "blue" and pack it with the bars. $search_term="|".$serach_t
Now if your field is |blue|green|yellow|orange and purple|blue and green|blue, yellow and fuchsia| you are only searching for |blue| so only the first instance will be found.
Searching your packed fields is difficult if you are also trying to search within unpacked fields without breaking them out using a temporary table.
If you are searching only within packed fields, and you want an exact search, you can pack your search term with the separator, however a comma is not the best separator to use, since it can be included in regular text. A bar | (the character over \ on most keyboards) is a better separator. You can then take your search term, say "blue" and pack it with the bars. $search_term="|".$serach_t
Now if your field is |blue|green|yellow|orange and purple|blue and green|blue, yellow and fuchsia| you are only searching for |blue| so only the first instance will be found.
Featured Post
WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:
Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
- Superb Internet
- IT Administration
- Ransomware
- Storage Software
- Databases
- Cloud Computing, Cloud Services, *Backups, *Online Backup Services, *Backup Software
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month2 days, 22 hours left to enroll
696 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.