We upgraded mysql to 5.7 and we are getting a few SQL call errors in our PHP script.
Here is an example of one of the errors which we need fixed. we expect there to be 5 or so that need fixing, We have traced it back to this "In mysql 5.7 it is required to have the order by fields to be present in the select statement when using DISTINCT"
Expression #1 of ORDER BY clause is not in SELECT list, references column '[login to view URL]' which is not in SELECT list; this is incompatible with DISTINCT
SELECT p.*, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*, (SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='2' AND disc.catorprodid=[login to view URL]) AS prodgroupdiscount, (SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='2' AND disc.catorprodid=[login to view URL]) AS discountmethod FROM ( SELECT DISTINCT [login to view URL], FLOOR(prodratingtotal / prodnumratings) AS prodavgrating FROM isc_categoryassociations ca INNER JOIN isc_products p ON [login to view URL] = [login to view URL] WHERE p.prodvisible=1 AND [login to view URL] IN (159) ORDER BY [login to view URL] asc, [login to view URL] ASC LIMIT 0, 15 ) AS ca INNER JOIN isc_products p ON [login to view URL] = [login to view URL] LEFT JOIN isc_product_images pi ON ([login to view URL] = 1 AND [login to view URL] = [login to view URL])
Location:
/public_html/includes/classes/[login to view URL] (Line 728)
Hello,
I have experience in writing optimized mysql query. I have worked on both 5.5. and 5.7 and fully understands differences. Please provide details.
Thankyou
Hello, i am ready to fix this issue, this happens after up-gradation of PHP ver and Mysql. I have done similar update for an earlier project and confident to update the site issue to your satisfaction. Please open message board for further discussion and provide the cpanel/ftp/Phpmyadmin details.
Thanks
Ramesh
Hello,
I'm interested in your project very well I'm good in SOLVING DATABASE QUERY’s.
I’m quite well experienced in these kinds of jobs.
Let's go ahead with me I want to service for you continuously.
Now I am free, so I will start the work in your project immediately.
Thanks
Hello Sir, I have read your job description regarding Make 5 php / mysql Queries sql 5.7 Compatible.
I have 6+ year of experience in HTML, MySQL, PHP, Software Architecture, SQL.
I have strong expertise to accomplish this project in decided time frame.
Lets discuss in detail so that we never skip basic thing during project implementation.
Thanks & Regards