Why is my ORDER BY query not working right with multiple columns?

PHP
1

New coder here trying to figure out why my ORDER BY output is not providing the intended output.

There are 2 columns I am trying to output. Column #1 is a category number. Column #2 is an item number.

I want the results to sort according to the category # first, and then the item .NET

ORDER BY tableCategories.cat_id ASC, tableItems.item_id ASC

The data I am currently querying is all from the same category number, and is numbered 1 to 54. However, the above statement will essentially order it by item# sequentially, it does not start at #1. It will either start at #12 or at number #32. Each time I hit refresh on the browser, it will toggle back and forth between starting at #12, and then the next time at #32.

If I only just order by item #, then it properly starts at #1.

Does anyone have an idea why this is happening?

2

What happens if you omit the ASC? Will it sort correct?

3

I have been looking into this more and there are a couple other factors involved.

Firstly, item # is a varchar, since some items will have letters. To compensate for this, I have read that I can use tableItems.item_id+0 ASC, tableItems.item_id ASC to preserve the natural order.

Now when I type this all into to my query, it will work fine:

ORDER BY tableCategories.cat_id ASC, tableItems.item_id+0 ASC, tableItems.item_id ASC

However, the sort order could change on other criteria other than the above. To account for this, I just have the following:

ORDER BY tableCategories.cat_id ASC, ?

When I execute the statement though, it does not work. My PHP statement is

$stmt->execute([$row_sub['sets_id'], $setOrder]);

For one of my conditions that I am testing for, I have:

$setOrder = 'tableItems.item_id+0 ASC, tableItems.item_id ASC';

When I do this, I get the same problem as I described above. However, if do the following then it works fine:

ORDER BY ?

$setOrder = 'tableCategories.cat_id ASC, tableItems.item_id+0 ASC, tableItems.item_id ASC';

Is there a reason why this does not work?

ORDER BY tableCategories.cat_id ASC, ?
4

If I omit ASC then the numbers are in random order.

6

actually, ASC is the default

so if you omit ASC, you get ASC

1 Like
9

It’s ASC’s all the way down.

But in seriousness, your problem is a problem of misunderstanding prepared statements.

Handing a Prepared Statement a string tells it to wrap your string in quotes.

What you think you’re submitting to the server:
ORDER BY tableCategories.cat_id ASC, tableItems.item_id+0 ASC, tableItems.item_id ASC
What you’re ACTUALLY submitting to the server:
ORDER BY tableCategories.cat_id ASC, "tableItems.item_id+0 ASC, tableItems.item_id ASC"

(And i’m sure Rudy will correct me if i’m wrong)
Telling the server to order by a string is nonsensical and will lead to indeterminate results (?)

1 Like
10

alas, i don’t do php

nope, ordering by a string works (in a manner of speaking) because every row gets the same string appended to it, and that’s what it sorts on – i expect the results to be random

as for those double quotes, they could also be interpreted as identifier delimiters, i.e. the thing inside them is a column name, and this will produce an error – it all depends on whether mysql interprets them according to standards, or as string quotes

look up ANSI_QUOTES SQL mode

11

This is indeed the case, the PHP code will need to be changed.

Prepared statements work with values but not identifiers. AFAIK, those need to be built up prior to the prepare.

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.