php - Is there a way to show a WHERE clause just for one field in MySQL?

Question

I do have some courses, to which students can enroll. There's a users table (usuarios), a courses (cursos) table and a third table that shows each student enrollment (cursosUsuarios), like this:

CREATE TABLE usuarios(
    userID int unsigned not null auto_increment primary key,
    userName char(50) null,
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE cursos (
    cursoID int unsigned not null auto_increment primary key,
    nombreCurso char(100) not null,
    estadoCurso char(50) not null,
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE cursosUsuarios (
    cursosUsuariosID int unsigned not null auto_increment primary key,
    userID int not null,
    cursoID int not null,
    userHabilitado int(1) not null DEFAULT '0',
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Each user can be enrolled in each course, but up until it is "enabled" by the admin, it cannot access the class. Being enabled means that the "userHabilitado" field is "1" instead of the default "0".

Now, I'm trying to show a list of all available courses at the user's profile, so the admin can see in which ones the user has enrolled and change that "0" to "1" in the "userHabilitado" field.

This is my query:

SELECT 
cursos.cursoID AS idcurso, cursos.estadoCurso,
cursosUsuarios.userHabilitado AS 'ok',                              
GROUP_CONCAT(cursosUsuarios.userID SEPARATOR ',') AS 'usuarios'
FROM cursos LEFT JOIN cursosUsuarios
ON cursos.cursoID = cursosUsuarios.cursoID
LEFT JOIN usuarios
ON cursosUsuarios.userID = usuarios.userID
WHERE cursos.estadoCurso='abierto'
GROUP BY cursos.cursoID;

This is the result I've got:

Now, as I need to show all the open courses (the courses where the estadoCurso field is set to "abierto"), I've not added a WHERE clause to filter the results by user.

What I do is to take the user ID from the url and explode the "usuarios" field to find the user that I'm looking for, like this:

                echo '<div class="checkbox">';
                while ($curso=mysqli_fetch_assoc($buscarCurso)) {
                    $usuario = $_GET['userID'];
                    $usuarios = explode(',', $curso['usuarios']);
                ?>
                    <input type="checkbox" 
<?php if (in_array($usuario, $usuarios)) {echo 'checked';}?> 
name="cursoID[]" value="<?php echo $curso['idcurso'];?>">
<?php echo '<b>'.$curso['nombreCurso'].'</b> ';?> 
<?php echo 'Id. curso: '.$curso['idcurso'].' <b>Estado usuario:</b> <input type="text" name="ok['.$curso['idcurso'].']" value ="'.$curso['ok'].'">';?>

So, if the user ID is in the $usuarios array the checkbox is checked, as it means that the user is enrolled.

My problem is how to show the userHabilitado field in that list. In the image above, the user 70 is enrolled in three courses: courseID 15, 16 and 18. In each case the userHabilitado field is set to "1", but the result is incorrect there.

I want to replace cursosUsuarios.userHabilitado AS 'ok' to something like: cursosUsuarios.userHabilitado WHERE usuarios.userID = 70, but inside that field.

Is that possible within MySQL?

Answer 1

Prior to MySQL 5.7 the default was to allow non FULL group by. Which means you can have a group by (that uses aggregate functions like sum and max and count and group_concat) with other non-aggregated columns (let's call them NON AGGS) like your first 3 shown not all part of your group by clause. It allowed it but the results would typically work out like this:

• It worked great because you know your data well and are trying to achieve a distinct

• It worked out awful because it was a snafu

Prior to 5.7, ONLY_FULL_GROUP_BY existed but it was turned OFF by default.

So in MySQL 5.7 along comes the ONLY_FULL_GROUP_BY defaulted ON. As such if you attempt a group by, but with not all the NON AGGS in the group by clause, you would get an Error.

Consider the following problem in 5.6 below:

create table thing
(   col1 int not null,
    col2 int not null,
    age int not null
);
insert thing(col1,col2,age) values 
(1,2,10),
(1,3,20),
(2,3,20),
(2,2,10);

select col1,col2,max(age) from thing group by col1;
+------+------+----------+
| col1 | col2 | max(age) |
+------+------+----------+
|    1 |    2 |       20 |
|    2 |    3 |       20 |
+------+------+----------+

What happens above is not all the NON AGGS are in the group by. It returns the max(age) by col1. But since col2 was not in the group by, it used the Cluster Index or Physical Ordering and brought it, inadvertently perhaps (a snafu, a mistake), the wrong value for col2. Depending on your intentions or knowing your data or even caring. The engine didn't care; perhaps you do.

To avoid these common mistakes or inadvertent data return, MySQL 5.7 turns on ONLY_FULL_GROUP_BY by default.

In your case, the wrong rows are making up your results presumably for columns 2 and 3.

See the Manual Page entitled MySQL Handling of GROUP BY.

---

Example 2

-- drop table if exists person;
create table person
(   id int auto_increment primary key,
    firstName varchar(100) not null,
    lastName varchar(100) not null
);

-- drop table if exists fruitConsumed;
create table fruitConsumed
(   id int auto_increment primary key,
    theDate date not null,
    fruitId int not null, -- does not really matter. Say, 1=apple, 2=orange from some other table
    personId int not null,
    qty int not null
);

-- truncate table person;
insert person (firstName,lastName) values 
('Dirk','Peters'),
('Dirk','Smith'),
('Jane','Billings');

-- truncate table fruitConsumed;
insert fruitConsumed (theDate,fruitId,personId,qty) values
('2016-10-31',1,1,2),
('2016-10-31',2,1,5),
('2016-10-31',2,2,12),
('2016-11-02',2,2,3);

Query:

select p.firstName,p.lastName,sum(fc.qty) 
from person p 
join fruitConsumed fc 
on fc.personId=p.id 
group by p.firstName,p.lastName; 
+-----------+----------+-------------+
| firstName | lastName | sum(fc.qty) |
+-----------+----------+-------------+
| Dirk      | Peters   |           7 |
| Dirk      | Smith    |          15 |
+-----------+----------+-------------+

The above works great on MySQL 5.6 and 5.7 regardless of the setting for ONLY_FULL_GROUP_BY

now consider

select p.firstName,p.lastName,sum(fc.qty) 
from person p 
join fruitConsumed fc 
on fc.personId=p.id 
group by p.firstName; 

+-----------+----------+-------------+
| firstName | lastName | sum(fc.qty) |
+-----------+----------+-------------+
| Dirk      | Peters   |          22 |
+-----------+----------+-------------+

The above is often acceptable on MySQL 5.6 without ONLY_FULL_GROUP_BY enabled and fails on 5.7 with ONLY_FULL_GROUP_BY enabled (error 1055). The above output is basically gibberish. But below it is explained somewhat:

We know that Dirk, a Dirk, just one Dirk, is the only one to survive the inner join. There are 2 Dirks. But because of the group by p.firstName, we are left with just one Dirk. We need a lastName. Because of the non-conformity to
the SQL Standard, MySQL can allow this with ONLY_FULL_GROUP_BY turned off. So it just picks any old lastName. Well, the first one it finds, and that is either in cache or the one in the physical ordering.

And it went with Peters. The fruit count sum is for all Dirks.

So if you code like this, the non-conforming non-ONLY_FULL_GROUP_BY gives you gibberish.

And as stated, MySQL 5.7 ships defaulted to not allowing this. But it is tweakable to the old way if you choose.

It is highly recommended that you fix your queries and leave ONLY_FULL_GROUP_BY as enabled.