Copy link to clipboard
Copied
Out of curiosity (I dont know much about sql queries) why would the query below flag an error and not work if 'FROM' was to followed directly on after the first instance of Max(departDate) ???? (difficult to see below but there is a break directly before FROM so it goes onto its own line.
$excursionMonth = $conn->query("SELECT DISTINCT departMonth, excursionYear, MAX(departDate)
FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC") or die($conn->error);
If FROM is not started on its own line the browser error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'excursions GROUP BY departMonth, excursionYear ORDER BY MAX(de' at line 1
OR error in sql console:
2 errors were found during analysis.
If FROM is on its own line there are no errors and the query works.
So from that can I assume that some sql statements need to go on their own line?
OK - I think there must have been some invisible shite character gotten into the string of code somehow/somewhere corrupting the statement. When I retyped the complete query from scratch all in one line it worked perfectly!!!
I know its probably better to format the code into several lines anyway but it looks neater and more compact in one line and I work faster that way.
I understand your point,
so let stuff as they are... at least you should be able to reduce it some
from
SELECT DISTINCT departMonth, excursionYear, MAX(departDate) FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC
going to
SELECT departMonth, excursionYear FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;
Copy link to clipboard
Copied
OK - I think there must have been some invisible shite character gotten into the string of code somehow/somewhere corrupting the statement. When I retyped the complete query from scratch all in one line it worked perfectly!!!
I know its probably better to format the code into several lines anyway but it looks neater and more compact in one line and I work faster that way.
Copy link to clipboard
Copied
I'm curious to understand the specific use case of using DISTINCT and GROUP BY on the same column
Copy link to clipboard
Copied
I'm not at all familar with sql queries, l just make them up as l go along until something l need works.
I was for many years using a different approach to getting distinct values and ordering by a specific column but this failed to work once a website lm currently updating was migrated to a different server environment, maybe its running a more up to date mysql version.
Anyway after some experiments the sql query which is shown in the other post returned the results l needed in the specific order l needed.
Copy link to clipboard
Copied
what do you expect as returning array single object ?
Copy link to clipboard
Copied
An array is returned with distinct values for each month and year ordered by those distinct months and years in the correct order.
For instance:
April 2022
May 2022
June 2022
July 2022
October 2022
November 2022
January 2023
February 2023
April 2023
Copy link to clipboard
Copied
I reformulate... currently you're using
SELECT DISTINCT departMonth, excursionYear, MAX(departDate)
FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC
so, what do you want to get as result...using word and language 😉
Copy link to clipboard
Copied
I don't understand what you're asking?
I already have a functioning sql query that gets me the required results, that's not to say the query is optimal but it works.
Sql querying is not something that l know a lot about as usually for me it's just a simple query to get, update, delete etc
This query was more complex because months appear in different years and had to be ordered correctly not like:
April 2022
April 2023
May 2022
June 2022
June 2023
July 2022
April 2023 and June 2023 etc had to come after all months in 2022 and all need to be in the correct month/ year order
Copy link to clipboard
Copied
is it possible for you to send me an sql dump (30 lines not much needed)...
Copy link to clipboard
Copied
No need, the current query does the job. It might not be perfect, maybe, but it's functional and returns the required array in the correct order.
Copy link to clipboard
Copied
I understand your point,
so let stuff as they are... at least you should be able to reduce it some
from
SELECT DISTINCT departMonth, excursionYear, MAX(departDate) FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC
going to
SELECT departMonth, excursionYear FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;
Copy link to clipboard
Copied
I understand your point,
so let stuff as they are... at least you should be able to reduce it some
from
SELECT DISTINCT departMonth, excursionYear, MAX(departDate) FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC
going to
SELECT departMonth, excursionYear FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;
By @B i r n o u
I'll check the query out later tonight, looks less complex and I understand the logic whereas I dont really have a clue what the MAX() bit is supposed to do, unless I take time to look it up.
EDITED
I can check it out right now! and yes it works. So I'll use your sql query and not the junk that I wrote even though it works, your's makes more sense.
Cheers
Copy link to clipboard
Copied
I understand your point,
so let stuff as they are... at least you should be able to reduce it some
from
SELECT DISTINCT departMonth, excursionYear, MAX(departDate) FROM excursions GROUP BY departMonth, excursionYear ORDER BY MAX(departDate) ASC
going to
SELECT departMonth, excursionYear FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;
By @B i r n o u
Unfortunatley it didnt work in my other more recent server environment where it's running MAMP:
It worked it my other MAMP server environment but that is running some sql version that was installed at least 5 years back...........hummm
I get the following error:
#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Still no worries the orginal sql query runs ok and returns the correct information.
Copy link to clipboard
Copied
cool that it worked, in fact without having the table to simulate the query, and without knowing the point that you was aiming, I just supposed when reading the query itself.
I hope that you wont face any trap, (specific use case) that will broke the scheme.
Copy link to clipboard
Copied
Unfortunatley it didnt work in my other more recent server environment where it's running MAMP:
By @osgood_
- Server version: 5.7.34 - MySQL Community Server (GPL)
youps, I spoke too fast... our messages crossed each others,
well are you running MySQL or MariaDB ?
Copy link to clipboard
Copied
#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
By @osgood_
in that case, you can try adding the filtering column in the previous selection
as
SELECT departMonth, excursionYear, departDate FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;
Copy link to clipboard
Copied
#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
By @osgood_
in that case, you can try adding the filtering column in the previous selection
as
SELECT departMonth, excursionYear, departDate FROM excursions GROUP BY departMonth, excursionYear ORDER BY departDate ASC;
By @B i r n o u
I get the below error, similar to last time:
#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Seriously it doesnt matter. I had a bit of a difficult time getting the original sql query that does work in the right order before it did anything........that's life.....this stuff is fickle.
Copy link to clipboard
Copied
is the table big ?
Copy link to clipboard
Copied
is the table big ?
By @B i r n o u
No, not at all.
I think its just one of those quirky instances which is problematical to get right. As I previously stated, I was using a much simpler DISTINCT sql query, which had worked for years locally and worked on a number of different remote sql servers too. That no longer worked when the website I'm currently managing was migrated to a newer remote server environment plus the query didn't work in my newer local server environment either, so I rewrote the query, which is the one which features in this thread and it works in my newer local environment, havent tested it in the remote environment as yet because I dont know if the client wants it upgraded.
I'm just doing some exploratory work so I'm in a good position to know how to solve it and I assume if other websites I manage get moved into a different environment they will most probably need the sql query to be upgraded as well.
I have no idea why the files were moved to a new remote environment but it happens, the old one could have reached end of life or the host thought it was a good idea, lol. I don't even know if its till the same host......I'm not involved with the hosting of this particular website. I just pick up the pieces and try to glue it back together again.
Copy link to clipboard
Copied
I did some test on a remote environement, and wasn't able to reproduce this error... I know what this type of error comes from, but no way to make it happen, now...
so here is the Dump , could you try and see if the error happen in your room
-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
-- Version du serveur : 10.5.15-MariaDB
-- Version de PHP : 7.4.24
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
-- --------------------------------------------------------
--
-- Structure de la table `excursions`
--
CREATE TABLE `excursions` (
`id` smallint(6) NOT NULL,
`departMonth` varchar(10) NOT NULL,
`excursionYear` varchar(10) NOT NULL,
`departDate` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `excursions`
--
INSERT INTO `excursions` (`id`, `departMonth`, `excursionYear`, `departDate`) VALUES
(1, 'avril', '2022', '2022-04-04'),
(2, 'septembre', '2021', '2021-09-07'),
(3, 'avril', '2022', '2022-04-13'),
(4, 'octobre', '2021', '2021-10-05'),
(5, 'avril', '2022', '2022-04-07'),
(6, 'fevrier', '2022', '2022-02-15'),
(7, 'avril', '2021', '2021-04-08'),
(8, 'fevrier', '2021', '2021-02-10'),
(9, 'avril', '2022', '2022-04-04');
--
-- Index pour la table `excursions`
--
ALTER TABLE `excursions`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT pour la table `excursions`
--
ALTER TABLE `excursions`
MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;
Copy link to clipboard
Copied
Hi Birnou
I'll look into this later today, probably this evening when I'm on my other machine with the newer version of sql.
Thanks
Os
Copy link to clipboard
Copied
Unfortuantely I still get the same error:
#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'excursionDays.excursions.departDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I dont know what 'sql_mode' is and I certainly dont want to be changing any of the current settings. The original query works well with the current settings in the local environment. I've yet to test it remotely though, I'll start tinkering at that point, not before.
Anyway thanks for your input as always............just seems to be dependent on however the sql server is set up.
Copy link to clipboard
Copied
so that mean that my dummy dump is in interrelationship with your own DB
give a last try to check if the query isn't lost by the group
As you can see MariaDB here is on 10.5.15... and the last stable RC is 10.8.2... I think that my VPS should move pretty soon, I probably will see the error... cauz until now, everything works
SELECT departMonth, excursionYear FROM excursions GROUP BY departMonth, excursionYear, departDate ORDER BY departDate ASC;
Copy link to clipboard
Copied
Its has something to do with the sql_mode
If I use this:
SET SQL_MODE = '';
before running the query whilst in phpMyAdmin the query works.
I dont know how to set SET SQL_MODE = ''; at runtime though in the actual webpage.
I've been reading something was changed from sql 5.5 to 5.7 to make this happen. Lots of people seem to have had issues with it from Googling the problem
Yes I know I can mess about trying to set this globaly directly in sql BUT at the moment I'd rather not go down that route considering I DO have a query that works. Last time I tried to do anything like that I competely destroyed XAMPP and its never run since, so I have to be careful as I dont want to be left without a server environment!
Copy link to clipboard
Copied
I've been reading something was changed from sql 5.5 to 5.7 to make this happen. Lots of people seem to have had issues with it from Googling the problem
By @osgood_
if you still have the link at hand, I would be interested... personally I never use column names but aliases.
In case I'm going to run an XAMPP including the latest version of MariaDB and see if I can reproduce the error in order to catch the problem, and be able to stop this kind of behavior