0

I´ve got a list of partners, every partner item has the value partner_main=0. The person who enters the content for the partner items knows if a partner name partner_name is there twice or more and if so, he sets partner_main=1 on one of the rows.

If there is more than one partner with the same partner_name, the partner with the entry partner_main=1 should only show up next to the other partner rows that are not duplicate.

Mysql code:

SELECT *, COUNT(partner_name) FROM partner WHERE partner_name IN ( SELECT partner_name FROM partner partner_main=1 ) GROUP BY partner_name HAVING (COUNT(partner_name) > 1) UNION SELECT *, COUNT(partner_name) FROM partner WHERE partner_name NOT IN ( SELECT partner_name FROM partner partner_main=1 ) GROUP BY partner_name HAVING (COUNT(partner_name) > 1)

The error log says:

[26-Feb-2014 17:28:56 GMT] PHP Fatal error: Uncaught exception 'Exception' with message 'Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1) GROUP BY partner_name HAVING ( COUNT(partner_name) > 1 ) UNION SELECT *, COU' at line 3 (SELECT *, COUNT(partner_name) FROM mm_partner WHERE partner_name IN (SELECT partner_name FROM mm_partner partner_main=1) GROUP BY partner_name HAVING ( COUNT(partner_name) > 1 ) UNION SELECT *, COUNT(partner_name) FROM mm_partner WHERE partner_name NOT IN (SELECT partner_name FROM mm_partner partner_main=1) GROUP BY partner_name HAVING ( COUNT(partner_name) > 1 ))' thrown in /wwwroot.wwwnew/system/modules/core/library/Contao/Database/Statement.php on line 282 #0 /wwwroot.wwwnew/system/modules/core/library/Contao/Database/Statement.php(253): Contao\Database\Statement->query() #1 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Rules/SimpleQuery.php(75): Contao\Database\Statement->execute(Array) #2 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Filter.php(102): MetaModels\Filter\Rules\SimpleQuery->getMatchingIds() #3 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Setting/Collection.php(212): MetaModels\Filter\Filter->getMatchingIds() #4 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/FrontendFilter.php(201): MetaModels\Filter\Setting\Collection->getParameterFilterWidgets(Array, Array, Object(MetaModels\FrontendIntegration\FrontendFilterOptions)) #5 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/FrontendFilter.php(76): MetaModels\FrontendIntegration\FrontendFilter->getFilters() #6 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/Module/Filter.php(74): MetaModels\FrontendIntegration\FrontendFilter->getMetaModelFrontendFilter(Object(MetaModels\FrontendIntegration\Module\Filter)) #7 /wwwroot.wwwnew/system/modules/core/modules/Module.php(148): MetaModels\FrontendIntegration\Module\Filter->compile() #8 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/Module/Filter.php(63): Contao\Module->generate() #9 /wwwroot.wwwnew/system/modules/core/elements/ContentModule.php(63): MetaModels\FrontendIntegration\Module\Filter->generate() #10 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(490): Contao\ContentModule->generate() #11 /wwwroot.wwwnew/system/modules/core/modules/ModuleArticle.php(184): Contao\Controller->getContentElement(Object(Contao\ContentModel), 'main') #12 /wwwroot.wwwnew/system/modules/core/modules/Module.php(148): Contao\ModuleArticle->compile() #13 /wwwroot.wwwnew/system/modules/core/modules/ModuleArticle.php(59): Contao\Module->generate() #14 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(405): Contao\ModuleArticle->generate(false) #15 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(229): Contao\Controller->getArticle(Object(Contao\ArticleModel), false, false, 'main') #16 /wwwroot.wwwnew/system/modules/core/pages/PageRegular.php(138): Contao\Controller->getFrontendModule('0', 'main') #17 /wwwroot.wwwnew/index.php(239): Contao\PageRegular->generate(Object(Contao\PageModel), true) #18 /wwwroot.wwwnew/index.php(407): Index->run() #19 {main}
Improve this question
0

1 Answer 1

Reset to default
1

Does the following works?

SELECT *, COUNT(partner_name) FROM partner WHERE partner_name IN ( SELECT partner_name FROM partner WHERE partner_main=1 ) GROUP BY partner_name HAVING COUNT(partner_name) > 1 UNION ALL SELECT *, COUNT(partner_name) FROM partner WHERE partner_name IN ( SELECT partner_name FROM partner WHERE partner_main=0 ) GROUP BY partner_name HAVING COUNT(partner_name) = 1

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you man but this show only item that have the value "partner_main=1" and not the other items that are not dublicate and have the value "partner_main=0"
Still the same result.
@fr3d wait. refresh the page. I just edited it. added UNION ALL and partner_main=0. try
It was my mistake, every item got the value "partner_main=1", i changed it, now its working. :)
But one last question the value in the database for "partner_main=0" isnt "0", the columne is empty, is that important for your code?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.