2

enter image description hereI am retrieving rows from MySQL to a a table in FPDF. this table has to be ordered by activities that have members (5.3, 5.4.4, 6.6, 11.1.2.3....) these numbers are random. I have tried many ways such as using CAST or order by * 1 but there is always a mistake in the order.

This query is the closest i have come to a correct order, but it is still incorrect. have attatched a sample of the result that should show 6.1 at the beginning. However, it starts with 10 sample

this is my table called pack.

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(10)      | NO   | PRI | NULL    | auto_increment |
| budget_hours   | int(10)      | YES  |     | NULL    |                |
| a_description  | varchar(255) | YES  |     | NULL    |                |
| bogie_type_id_ | int(10)      | YES  |     | NULL    |                |
| skillset       | varchar(100) | YES  |     | NULL    |                |
| budget_mins    | int(10)      | YES  |     | NULL    |                |
| activity       | varchar(10)  | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

and this is transactions table

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(10)      | NO   | PRI | NULL    | auto_increment |
| activity         | varchar(20)  | YES  |     | NULL    |                |
| status           | varchar(10)  | YES  |     | NULL    |                |
| name             | varchar(20)  | YES  |     | NULL    |                |
| bogie_id         | int(10)      | YES  |     | NULL    |                |
| bogie_serial_no_ | varchar(100) | YES  |     | NULL    |                |
| duration         | int(10)      | YES  |     | NULL    |                |
| start_timestamp  | int(10)      | YES  |     | NULL    |                |
| end_timestamp    | int(10)      | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
select transaction.activity,  pack.a_description,  transaction.name,
  pack.activity as pa, FROM_UNIXTIME(transaction.end_timestamp, '%d/%m/%Y') as et
from transaction
inner join pack  on pack.activity=transaction.activity
where transaction.bogie_id='$id' and bogie_type_id_='$bt'
order by REPLACE(pa,'.','')  ASC
Olivier
  • 13,283
  • 1
  • 8
  • 24
maja
  • 43
  • 8
  • 2
    Please show us column names and structure. – Nikolay Shindarov Apr 21 '21 at 14:16
  • Also be warned that your SQL query is open for injection. Better use prepared statements – Nico Haase Apr 21 '21 at 14:19
  • About how many records are we talking about each time you generate this? 10? 100? 1000? Can it be more than 1000? If the amount isnt that big, then you could fetch all records in arrays/objects and then simply use a [custom sort](https://stackoverflow.com/questions/11145393/sorting-a-php-array-of-arrays-by-custom-order) on php side, then you can make the sort as complex as you want. – Definitely not Rafal Apr 21 '21 at 14:20
  • The records are over 500, i will look into this approach, thank you. @DefinitelynotRafal – maja Apr 21 '21 at 14:25
  • 3
    Does this answer your question? [mysql sorting of version numbers](https://stackoverflow.com/questions/7508313/mysql-sorting-of-version-numbers/8408337) – Definitely not Rafal Apr 21 '21 at 14:26
  • I found the code behind this MySQL function to solve my problem INET_ATON. from this previous question on MySQL sorting of version numbers – maja Apr 22 '21 at 08:47

0 Answers0