0

I have a generic function (php) that retrieves multiple records of a table by matching a column value and orders them by a datetime field. In some instances the datetime is the same for multiple records, inserted at nearly the same time, and on these tables there is another column that is a record number, an automatically incrementing number. I would like to check if the table has this record number column present, and if so, sort by it. Otherwise if the record number column does not exist in the table I want to sort by the datetime field. I could write a separate function, sorting for record numbers, but prefer this to be handled by a single function using a mysql solution.

I just want to know if this can be done?

Using IF, Exist, or orderby?

callison
  • 11
  • 3
  • 1
    Welcome, to improve your experience on SO please [take the tour](http://stackoverflow.com/tour) and read [how to ask](https://stackoverflow.com/help/how-to-ask), an [On Topic question](https://stackoverflow.com/help/on-topic), then look at the [Question Check list](https://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist), the [perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) and how to create a [Minimal, Complete and Verifiable Example](http://stackoverflow.com/help/mcve) – RiggsFolly Jul 05 '22 at 15:26
  • You are basically asking us to either design, or design and write code from a Specification. That is not what StackOverflow is for, we help each other fix code related issues we are having, we dont write code for you – RiggsFolly Jul 05 '22 at 15:26
  • RiggsFolly, No. Just want to know how you can order by one column if another doesn't exist in the table. Can mysql do that? IF, EXIST, ORDERBY? – callison Jul 05 '22 at 15:30
  • Isn't it a simple line of Mysql code? Not asking for much here. – callison Jul 05 '22 at 15:35
  • _Isn't it a simple line of Mysql code?_ Did you try any of those options that you describe? That sounds quite simple – RiggsFolly Jul 05 '22 at 15:40
  • I've searched extensively here and elsewhere. Just not sure if what I am after can be done at all. Sort on a column A if it exist, sort on column B if it doesn't. – callison Jul 05 '22 at 15:42

1 Answers1

1

As a very broad description, you can think of SQL query execution as consisting of separate stages:

  1. Parsing the query: checking the syntax, working out which bits are table names, where strings begin and end, whether it's a SELECT or an UPDATE, etc. This is where MySQL takes the string provided by PHP and breaks it into some internal pieces.
  2. Planning the query: working out what tables and columns are involved, how they need to be filtered and manipulated, what indexes and optimisations can be used. The only unknowns left are the placeholders you use for your data to prevent SQL Injection.
  3. Executing the query: plugging in actual values, fetching indexes and data, and running through the steps of the plan to give the desired output or effect. This is where the actual values you bound to your placeholders are used.

The important point is that step 2 generally doesn't deal with "if" and "maybe", it expects to be told directly "use this column, of this table". Any choice of what table to use would have to happen before anything hits the database.

In your case, that means the choice of table happens in PHP; but PHP is just building a string, it doesn't know about what columns exist, so it can't automatically make this decision either.

In short, you will need to write some custom code to do this: have a list of which tables use which column to sort; or make an extra query to a system view like INFORMATION_SCHEMA.COLUMNS to look it up, and then have the conditional logic in PHP.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • So mysql cannot do what I want because it cannot handle a "case" where the column is absent from the table. That might be why I had such a hard time finding the answer and posed the question here. If it can't be done so be it. I'll just write another function to interact with those tables specifically. – callison Jul 05 '22 at 15:53
  • @callison Precisely, a `CASE ... END` clause is run in what I've called here "stage 3": it takes *values*, and plugs them into a planned set of operations on a specific set of tables and columns. – IMSoP Jul 05 '22 at 15:56