Can't sort my table by a particular column, records are returned in autinc order.

[boylesg]

I am using MySQL Workbench.
I have created the following table.

It contains the folllowing data. They are in alphabetical order except the last two - 50 and 51.

I am attempting to sort via 'name' it via this PHP code:

						function DoGenerateAdditionalTradesCheckBoxes()
						{	
							global $g_dbFindATradie;
							$nCount = 0;
							$nNumCols = 20;
							 
							$queryResult = $g_dbFindATradie->query("SELECT id, name, description FROM trades ORDER BY name");
							
							while ($row = $queryResult->fetch_assoc())
						    {
						    	if (($nCount == 0) || (($nCount % $nNumCols) == 0))
						    		echo "<td>";
								echo "<input type=\"checkbox\" id=\"check_" . $row["name"] . "\" name=\"" . $row["name"] . "(" . $row["id"] . ")\" onclick=\"OnClickTradesCheck(this)\" />";
								echo "<label>" . $row["name"] . "</label><br/>";	
					    		$nCount++;
						    	if (($nCount % $nNumCols) == 0)
						    	{
						    		echo "<td>";
						    		$nCount = 0;
						    	}
						    }
						    $queryResult->free_result();
						}

And this is the result:

So it appears as thought the ORDER BY part of my SQL query is being ignored. What is causing that?

I can't check the query in Workbench because there does not seem to be anyway you view the sorted data output of a query in it.

 

[SkipVought]

Yes, your physical table is UNORDERED. The only time you can observe any order is in a query via an ORDER BY clause. But you will not see that in your table.

Don't sweat it!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[boylesg]

$queryResult = $g_dbFindATradie->query("SELECT id, name, description FROM trades [highlight #A40000]ORDER BY name[/highlight]");

 

[Chris Miller]

Is it the HTML that orders the rows by id again?

As you ORDER BY the queried result is ordered, but it may be sorted by HTML where you have the id. Look into the CSS, I guess.
Or look into the raw HTML, that should be in the fetched order.

Chriss

 

[spamjim]

You are probably bumping into a conflict with a reserved word ("name"):

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Employ the backtick: `

Instead of:

$queryResult = $g_dbFindATradie->query("SELECT id, name, description FROM trades ORDER BY name");

Try:

$queryResult = $g_dbFindATradie->query("SELECT `id`, `name`, `description` FROM `trades` ORDER BY `name`");