How to build a search query?

Hello, trying to write a query for the normal reading of the database fields. Here is the problem. Is the main table. Connected to it by id still have a lot of tables, and each of them, id from the main table can appear more than once. For each REP id in the external table returns a single record. For a small number of filters I managed to write a class in php, which results in the return result in order, but had to expand, and for the greater number - this is not the case.

Here is the query:
'SELECT * FROM `companys`
INNER JOIN `countrys` ON `company`.`company_id` = `countrys`.`company_id`
INNER JOIN `country_value` ON `country_value`.`value_id` = `countrys`.`value_id`
INNER JOIN `prices` ON `company`.`company_id` = `prices`.`company_id` 
INNER JOIN `service_description` ON `service_description`.`description_id` = `prices`.`description_id` '


Get the results:

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "http://pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "124" ["value_id"]=> string(1) "1" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" } 

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "http://pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "232" ["value_id"]=> string(1) "2" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" } 

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "http://pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "463" ["value_id"]=> string(1) "6" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" } 

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "http://pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "490" ["value_id"]=> string(1) "7" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" } 

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "http://pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "550" ["value_id"]=> string(2) "10" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" } 

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "http://pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "570" ["value_id"]=> string(2) "11" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" } 

array(14) { ["company_id"]=> string(2) "67" ["name"]=> string(16) "Inmotion Hosting" ["href"]=> string(0) "" ["image"]=> string(65) "http://pickuphost.ru/wp-content/uploads/inmotion-hosting-logo.png" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "125" ["value_id"]=> string(1) "1" ["price_id"]=> string(4) "1363" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1952" ["service_name"]=> string(7) "VDS_min" }
July 8th 19 at 11:39

Related questions

More answers about "How to build a search query?"
2 answers
July 8th 19 at 11:41
Select t1.id t1.data, ta2.data, t3.data from table1 t1, table2 t2, table3 t3 where t1.id=t2.id and t1.id=t3.id expand as much as you want
'SELECT * FROM company c1, c2 countrys, prices p1, service_description s1 WHERE c1.company_id=c2.company_id AND c1.company_id=p1.company_id AND p1.description_id = s1.description_id'
nothing has changed, here goes the results:

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "124" ["value_id"]=> string(1) "1" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" }

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "232" ["value_id"]=> string(1) "2" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" }

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "463" ["value_id"]=> string(1) "6" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" }

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "490" ["value_id"]=> string(1) "7" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" }

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "550" ["value_id"]=> string(2) "10" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" }

array(14) { ["company_id"]=> string(2) "66" ["name"]=> string(5) "iPage" ["href"]=> string(0) "" ["image"]=> string(54) "pickuphost.ru/wp-content/uploads/ipage-logo.jpg" ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "570" ["value_id"]=> string(2) "11" ["price_id"]=> string(4) "1362" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1627" ["service_name"]=> string(7) "VDS_min" }

array(14) { ["company_id"]=> string(2) "67" ["name"]=> string(16) "Inmotion Hosting" ["href"]=> string(0) "" ["image"]=> string(65) "pickuphost.ru/wp-content/uploads/inmotion-hosting-..." ["VIP"]=> string(1) "0" ["free_period"]=> string(0) "" ["security"]=> string(0) "" ["VIP_value"]=> string(1) "0" ["country_id"]=> string(3) "125" ["value_id"]=> string(1) "1" ["price_id"]=> string(4) "1363" ["description_id"]=> string(1) "1" ["price"]=> string(4) "1952" ["service_name"]=> string(7) "VDS_min" }

etc. - sister commented on July 8th 19 at 11:44
: I even quite blunt or something, it comes to answer a two-dimensional array with the information you a little something? - sally_Carro commented on July 8th 19 at 11:47
each company makes a shitload of times, and you need that in all that concerns one company returned a single row. - sister commented on July 8th 19 at 11:50
: in fact, the essence of join to combine the tables and record type 1 to many can be issued as many times as there are many. - sally_Carro commented on July 8th 19 at 11:53
OK, but how to make many-to-one? - sister commented on July 8th 19 at 11:56
: read the documentation, you probably the theory behind. let's say you have the communication tables 1 and 2, the first one entry in the second match of 3, you will 01 - 1, 01 -2, 01 -3 and no matter what you salinesi, display order only change. if you want a one-line display 01, 1, 2, 3 the function (either built-in skul', or realize them on page). Let's go the easy route, you take a sample of the table where all your records unikalniy and should not be repeated and their ID in return array in PHP then cycle through this array and do a bunch of selectors for him to form his desired place, then somewhere her return. If you go in whining, forward in the study of the documentation, without it actually very sad to do anything. - sally_Carro commented on July 8th 19 at 11:59
OK, thanks, still need docks poshurshat. maybe I missed or something else can be done. - sister commented on July 8th 19 at 12:02
July 8th 19 at 11:43
write the class in php
Use the tools MS SQL (SSMS or SSDT) and put the query in the stored procedure to save the query on the server.
he's just in mescole.... - sister commented on July 8th 19 at 11:46

Find more questions by tags MySQLPHPSQL