php mysql building a search query at ten tables + different columns

Question

I am trying to develop a search page for a website, but cannot come up with a single query.

Here is a list of those ten tables and their fields

1. tmp_auction_auto

   id order category manufacturer model price price_type location year run run_type doors airbags gear engine horsepower cylinders drivetype fuel color abs electronicwindows climatcontrol disks hatch boardcomputer alarm rightsteer turbo parkingcontrol conditioner leathersalon navigation centrallock chairwarm hydraulics noprice rent exchange customclearance status other contact

2. tmp_auction_estate id order type transaction price price_type price_sqm noprice city address area height repair condition project destination land veranda mansard conference stairs_total stair rooms bedrooms balcony sanitary_arr loggia fireplace conditioner garage parking land_destination buildings distance_central_street distance_tbilisi storeroom jacuzzi bathroom shower sauna furniture technique telephone internet generator pool businesscenter ate network inventory wardobe elevator gas hotwater heating intercom cabletv alarmsystem entrancesecurity windowguards security duplex triplex satelite kitchen showcase land_railway land_electricity land_gas land_water land_drainage status other contact

3. tmp_auction_other id order title price price_type noprice info contact

4. tmp_branch id lang title content x y
5. tmp_comments id reply_id path username email title content likes dislikes time admin

6. tmp_news id lang title content date

7. tmp_pages id lang title content date

8. tmp_polls id name question answers ip

9. tmp_presentation id lang title order

10. tmp_sitemap id parent lang title link order

I know I can write multiple queries for each table with any order (bad practice) and then combine it to a PHP array for output, but I rather need a professional approach to this subject.

P.S. I don't want to use memcache, solr, sphinxs and such libs (server won't support those)

• I will also appreciate other seach suggestions like content search,etc (website is written in php in mvc pattern with url rewrites and relies on mysql database though)

Answer 1

I guess you can join these tables and create a view into which the data obtained fom the joined tables can be saved. Now the search must be conducted on this view which will speed up the search.
For eg.
mysql> SELECT CONCAT(UPPER(supplier_name), ' ', supplier_address) FROM suppliers;
+-----------------------------------------------------+
| CONCAT(UPPER(supplier_name), ' ', supplier_address) |
+-----------------------------------------------------+
| MICROSOFT 1 Microsoft Way                           |
| APPLE, INC. 1 Infinate Loop                         |
| EASYTECH 100 Beltway Drive                          |
| WILDTECH 100 Hard Drive                             |
| HEWLETT PACKARD 100 Printer Expressway              |
+-----------------------------------------------------+
CREATE VIEW suppformat AS 
SELECT CONCAT(UPPER(supplier_name), ' ', supplier_address) FROM suppliers;

mysql> SELECT * FROM suppformat;
+-----------------------------------------------------+
| CONCAT(UPPER(supplier_name), ' ', supplier_address) |
+-----------------------------------------------------+
| MICROSOFT 1 Microsoft Way                           |
| APPLE, INC. 1 Infinate Loop                         |
| EASYTECH 100 Beltway Drive                          |
| WILDTECH 100 Hard Drive                             |
| HEWLETT PACKARD 100 Printer Expressway              |
+-----------------------------------------------------+


Please check this link which will give you some idea of views
[http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views][1]


  [1]: http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views