So this is what I have: $query = mysql_query("SELECT * FROM main WHERE info LIKE '%".$search."%' IN (SELECT * FROM main WHERE category='Background & Texture' UNION SELECT * from main WHERE category='Advertising, Graphic Design & Branding' UNION SELECT * from main WHERE category='Web Graphic & Layout' UNION SELECT * from main WHERE category='Human Photo Manipulation, Retouch & Effect' UNION SELECT * from main WHERE category='Realistic Effect' UNION SELECT * from main WHERE category='Photo Manipulation & Illustration' UNION SELECT * from main WHERE category='Text Effect')"); ?> I want to basically search depening on what the user searches for, but only in particular categories of my database. Basically, a subquery. Both queries work fine, but trying to combine them just isn't working. Can anyone lend a hand?
Thats a hell long of a Mysql query.. Why so complicated? Why not simply ask user to pick a category in which he wanna search for info. $search is the search key $category is the category Then, $query = mysql_query("SELECT * FROM main WHERE info LIKE '%".$search."%' AND category = '$category'");
I'm already doing that, or intend to. Those queries currently running are just a selection of some of the categories. It's a big site Also, some things may fall under two or a different category that the one being selected, which isn't very user friendly.
i would not recommend this, this kind of query just complicate things. - execution process. - this will take longer to execute and return the result set. - subquery - notice your subquery, you still trying to select *, doing * on the query will parse all columns and rows and datatypes. this is not a good method, try to specify things - scalability - is this kind of code can accomodate atleast 1000 users executing this query? what about 2000 users? or more? practice simple, robust query. on more i notice, you are searching for a text in category, where as you can separate the text name of the categories and place it in other table, parsing text / varchar datatypes is heavier than comparing integers. this will go really slow. scrap this query and make the program simplier, robust, and scalable, also maintainable. If you are searching for text in a text datatype use Match/Against method no union select.. if you are doing huge queries let mysql / oracle or watever your database is to do that, use procedures, and functions in the database.
I second bartolay's opinion. It's bad to use wildcards. Just the one's you can't run away. Also if it is a big site, try to search for some kind of query cache. There are plenty info on that. Also, consider stop using MySQL. I've dumped it a year ago and now use MongoDB for almost everything. The performance increase is brutal. Anything you need, feel free to drop me a note.
I want fetch data from 2 tables but i want all data from 1st table and i want some data from 2nd table. So how to fire mysql query? Please help me....
Probably an easier way is to run a query first to fetch a data. Then run the 2nd query that uses the data from the first query to fetch it. This will save you lots of trouble and headaches in the future when reading your own long query. I use to go through the trouble of long querys but now just use short simple ones as understanding them as easier when doing refinements.