Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

boylesg

Programmer
May 12, 2008
3
0
0
AU
I am using MySQL Workbench.
I have created the following table.

MYSQL_ipcjde.jpg


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

mysql2_dtbjh3.jpg


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

PHP:
						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:
OUTPUT_p7bwfc.jpg


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.
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
$queryResult = $g_dbFindATradie->query("SELECT id, name, description FROM trades [highlight #A40000]ORDER BY name[/highlight]");
 
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
 
You are probably bumping into a conflict with a reserved word ("name"):
Employ the backtick: `

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

Try:
PHP:
$queryResult = $g_dbFindATradie->query("SELECT `id`, `name`, `description` FROM `trades` ORDER BY `name`");
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top