MYSQL ORDER BY alphabetical order not working

Question

I have a DB with a table full of facility names. I am trying to output the facilities alphabetically by facility name + display the account that it is associated with.

Facilities table looks something like this:

id | account_id | facility_name |
 2 |     2      |  Facility A   |
 3 |     2      |  Facility B   |
 4 |     2      |  Facility A   |

Accounts table looks like this:

account_id | account_name |
    1      |  Account 1   |
    2      |  Account 2   | 
    3      |  Account 3   |

And I am trying to get my output to be like this:

 facility_name | account_name |
  Facility A   |  Account 1   |
  Facility B   |  Account 1   |

I am outputting this into a table using PHP so here is kinda what it looks like:

$sql = "SELECT * FROM facility INNER JOIN account ON account.account_id = facility.account_id WHERE facility.account_id = '". $q ."' ORDER BY 'facility_name'";

echo   "<table>
            <tr>
            <th>Facility Name</th>
            <th>Account Name</th>
            </tr>";
        while ($row = mysqli_fetch_array($data)) {
            echo "<tr>";
            echo "<td>" . $row['facility_name'] . "</td>";
            echo "<td>" . $row['account_name'] . "</td>";
            echo "</tr>";
        }
echo "</table>";

However this does not seem to be ordering my table my facility name, it is just outputting in the order that it was input in.

How can I output my data so that it is also ordered by 'facility_name'?

You're not checking for errors on your query and it would have thrown you that syntax error that you made. — Funk Forty Niner, Oct 07 '15 at 15:58

score 2 · Accepted Answer · answered Oct 07 '15 at 15:51

2

The problem is right here:

ORDER BY 'facility_name'";

You are LITERALLY ordering by the string "facility_name".

Take out the quotes like:

ORDER BY facility_name";

answered Oct 07 '15 at 15:51

Digital Chris

6,177
1
20
29

Yep, got it, thanks Ill accept this when I can! – BRBT Oct 07 '15 at 15:53

score 0 · Answer 2 · answered Oct 07 '15 at 15:51

The quotes are wrong. It should be just the field name without quotes:

ORDER BY facility_name

or using backticks (allowed, but only necessary when there are spaces or other special characters in the field name):

ORDER BY `facility_name`

If you use normal quotes like you do, it will be a string literal, so you are just sorting by the text 'facility_name', which has the same constant value for each row, so the sorting doesn't have effect.

score 0 · Answer 3 · answered Oct 07 '15 at 15:54

0

Appending this to your query should work:

ORDER BY facility_name ASC

If not, try putting it inside tick marks like this:

ORDER BY `facility_name` ASC

answered Oct 07 '15 at 15:54

Andrew

1,322
14
20

MYSQL ORDER BY alphabetical order not working

3 Answers3