MySQL sort order issue in Ajax/PHP dropdown menu

Question

Hoping someone can help me with a MySQL/PHP sort order issue.

A brief outline of what I am doing is as follows:

I have three (chained) selection boxes. The first box's output is "fixed" (it contains all the top-level categories in my store). The two subsequent boxes are populated depending on the choice made in the previous box... standard, straightforward stuff, I hear you say!

It all works great, and the selection (string) from the last box is appended to a "search results" page -- exactly what I need. My problem comes when trying to organise the sort order of the second and third boxes though.

Here is a sample of my code:

        public function ShowType()
    {
        $sql = "SELECT categories.*, categories_description.categories_name FROM categories, categories_description WHERE categories.categories_id = categories_description.categories_id AND categories.parent_id = $_POST[category] ORDER BY categories_name";
        $res = mysql_query($sql,$this->conn);
        $type = '<option value="0">Select...</option>';
        while($row = mysql_fetch_array($res))
        {
        $type .= '<option value="' . $row['categories_name'] . '">' . $row['categories_id'] . '</option>';
        }
        return $type;
    }

The Ajax replaces the (greyed out, non-clickable)

<option value="0">Select...</option>

part to

<option value="' . $row['categories_name'] . '">' . $row['categories_id'] . '</option>

once a selection (in the previous box) has been made.

My problem is the array displayed once the box is activated is not in alphabetical order. I want to sort the output in the selection box by categories_name but no matter what I try in the MySQL query, it won't have it!

Is the output to my "dynamic" tag unsorted because of the way it's updated by Ajax? Do I need to re-sort the array with PHP, and if so, can someone point me to a beginner's level (I'd hate to call myself intermediate!) tutorial on how to go about it please?

I hope I don't spark off any more arguments like I did last time! :-)

Cheers,

Andy.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — hakre, Oct 28 '12 at 01:17
Which part of http://dev.mysql.com/doc/refman/5.0/en/select.html is not leading you to success? You sort the values when you query the database. — hakre, Oct 28 '12 at 01:20
Thank you for your links, Hakre. I will look at how to replace the `mysql_*` function. I try sorting the values in the MySQL query (I've tried a number of different SORT BY values, but none seem to do anything!) but I will go and read your link from your second post too. — user1742819, Oct 28 '12 at 01:38
It is called `ORDER BY` not `SORT BY`. Check for `mysql_query` returning FALSE, that is on error. Then display the error with `mysql_error`. See as well http://stackoverflow.com/a/12770072/367456 — hakre, Oct 28 '12 at 12:20
Sorry, that was just me using the wrong term in my reply. In my code I have used `ORDER BY`. But pointing little typos like that out helps things stick in memory for the future! If I was to ever type `SORT BY` in code from now on, I will remember this post! :D — user1742819, Oct 28 '12 at 17:11

score 0 · Answer 1 · answered Oct 28 '12 at 08:58

0

In your SQL query you have:

ORDER BY categories_name

However, in your code for populating the dropdown you have:

<option value="' . $row['categories_name'] . '">' . $row['categories_id'] . '</option>

You are displaying categories_id but sorting by categories_name, so the resulting dropdown will not appear to the user to be ordered.

answered Oct 28 '12 at 08:58

Mitch Satchwell

4,770
2
24
31

Thank you for your comment, MitchS. Previously I had looked at this, and I have already swapped the two values you mention before asking my question, but it didn't alter the results of the dropdown. Are you suggesting that one of the `$row['xxxxx_xxxxx']` entries is superfluous, or that I should have another value in one of them? – user1742819 Oct 28 '12 at 17:15

score 0 · Answer 2 · answered Oct 29 '12 at 06:33

0

Use it: ORDER BY categories_id

answered Oct 29 '12 at 06:33

Akhilesh Kumar

849
1
15
28

Thank you for your suggestion, Akhilesh, but that doesn't have any effect... even if it did, I want the results in alphabetical order, and the ID isn't alphabetical, it's the order they were created! Actually, this is the order the items are being displayed in, regardless of how I try to sort the list in the query! I've tried `ORDER BY categories_id` `ORDER BY categories_name` `ORDER BY categories_description.categories_name`, and everything else! Nothing changes the order of the dropdown menu when it is populated! I am thinking I am going to have to change the category ID's in the database. – user1742819 Oct 29 '12 at 12:05

score 0 · Answer 3 · answered Oct 29 '12 at 12:33

Try these queries: "SELECT categories.*, categories_description.categories_name FROM categories, categories_description WHERE categories.categories_id = categories_description.categories_id AND categories.parent_id = '$_POST[category]' ORDER BY categories_name"

or

"SELECT categories.*, categories_description.categories_name FROM categories, categories_description WHERE categories.categories_id = categories_description.categories_id AND categories.parent_id = '".$_POST[category]."' ORDER BY categories_name"

MySQL sort order issue in Ajax/PHP dropdown menu

3 Answers3