Order by not working - PHP

Navigation
3

I have this table:

I do this query to get sorted data and create a select : 

select id, nombre from usuarios_grupos ORDER BY 'nombre' ASC

... and this is the result, surprisingly does not order it .

Before it worked What have I changed?

Well, I went from a dedicated Windows server to another with Linux and Apache .

I add 12/1/17:

The funny thing is that it does apply a type of order, that is, this query:

select id, name from group_ users ORDER BY ASC name

Return:
Id Name Ascendant 1
10 Payment module
4 Supervisor
9 Store
2 Administrator
1 Creator
8 Partner
3 User

And this query:

select id, name from group_ users ORDER BY name DESC

Return:
 id name Descending 1
3 User
8 Partner
1 Creator
2 Administrator
9 Store
4 Supervisor
10 Payment module

This is the php code that is executed, with the same result: 

<select name="id_grupo" size="1" <?=$noacceso?>>
<?php
$sql = "select id, nombre from usuarios_grupos";
if((int)$_SESSION["grupo"] !== 1) $sql .= " where id<>1" ;
$sql .= " order by nombre";

$result = $conn->query($sql) Or Die ("ER0081: Error cargando datos");

while($rs = $result->fetch_object())
{
    If ((int)$rs->id !== $id_grupo)
    {
        Echo "<option value='" . $rs->id . "'>" . $rs->nombre . "</option>";
    }
    Else
    {
        Echo "<option selected value='" . $rs->id . "'>" . $rs->nombre . "</option>";
    }
}
$result->close();
?>

    
asked by Manu Burrero Sánchez 11.01.2017 в 14:04
source

5 answers

4

I asked you to run the following query to diagnose the problem: 

select id, nombre, ascii(nombre)
  from usuarios_grupos
 order by nombre

And your result was: 

id  nombre        ascii(nombre) 
--  ------        -------------
10  Módulo pago   32 
4   Supervisor    32 
9   Tienda        32 
2   Administrador 65 
1   Creador       67 
8   Socio         83 
3   Usuario       85

With that result you can see more clearly what the problem is. As you can see, the ASCII code of the first character of the first 3 registers is 32 , that is, a space ( ' ' ).

So for those 3 records, even if it is not clear at first sight, the names have the following values (note the space in front):

  • ' Módulo pago'
  • ' Supervisor'
  • ' Tienda'

And since the 32 character is considered inferior to the alphanumeric characters, that explains why those 3 records appear first.

Obviously, you will have to decide how to fix the problem. You may want to correct the data to remove excess spaces: 

update usuarios_grupos
   set nombre = trim(nombre);

Or maybe you agree to fix the ORDER BY to ignore the extra spaces: 

select id, nombre
  from usuarios_grupos
 order by trim(nombre)
    
answered by 13.01.2017 / 22:47
source
0

Hi, I think the problem comes from the type of collation since the column Nombre you have it as utf8_general_ci .

Accuracy to sort the data:

utf8_unicode_ci: It is based on the Unicode standard for ordering, and sorts accurately in a wide range of languages.

utf8_general_ci: It is very close to order correctly with Unicode in many common languages, but it has a series of inaccuracies when ordering in some languages, so it is not recommended for correct ordering in all languages. Languages.

Try changing the type of collation and tell us

    
answered by 11.01.2017 в 14:31
0

Have you tried putting the column number in the order by, ie ORDER BY 2 to sort by the second column? 

select id, nombre from usuarios_grupos ORDER BY 2 ASC
    
answered by 11.01.2017 в 15:32
-1

remove the quotes you have in nombre and it will work for you.

The order by would look like this: 

 order by nombre;

Greetings!

    
answered by 11.01.2017 в 16:41
-1

try with 

select ug.id, ug.nombre from usuarios_grupos ug ORDER BY ug.nombre ASC
    
answered by 11.01.2017 в 23:24
POST method to send form data with AJAX without JQUERY [duplicate] I do not add the row in the table by Jquery