PHP mysql order by nullable date field

Question

Below query is providing result of 22 rows in my sql

select pk.partnerid
     , pk.id as packageId
     , pk.name as package
     , p.partnername as partner
     , bp.name
     , bp.bookedDate
     , bp.email
     , bp.phone 
  from packages as pk 
  join bookpackage as bp 
    on bp.pkId = pk.id 
  left 
  join partner as p 
    on p.id = pk.partnerid 
 where pk.status > 0 
 order by (bookedDate is null) asc
     , bookedDate asc

where as the same in application provides only 3 rows which has not null dates

mysqli_query($con, "
select pk.partnerid
     , pk.id as packageId
     , pk.name as package
     , p.partnername as partner
     , bp.name
     , bp.bookedDate
     , bp.email
     , bp.phone 
  from packages pk 
  join bookpackage bp 
    on bp.pkId = pk.id 
  left 
  join partner p 
    on p.id = pk.partnerid 
 where pk.status > 0 
 order
    by (bookedDate is null) asc
     ,  bookedDate asc
");

I want to show all data and order by date if date is not null.

Sorry its a typo mistake ..Updated on question..The output remains same — Ajimi Mol, Nov 20 '19 at 11:16
There are 3 rows and those rows are displaying in application.I want to show the other rows also — Ajimi Mol, Nov 20 '19 at 11:34
then remove statement bookedDate is null,so it will show all — Rp9, Nov 20 '19 at 11:38

score 0 · Answer 1 · answered Nov 20 '19 at 11:21

check bookedDate is not null in where condition

mysqli_query($con, "
select pk.partnerid
     , pk.id as packageId
     , pk.name as package
     , p.partnername as partner
     , bp.name
     , bp.bookedDate
     , bp.email
     , bp.phone 
  from packages pk 
  join bookpackage bp 
    on bp.pkId = pk.id 
  left 
  join partner p 
    on p.id = pk.partnerid 
 where pk.status > 0 
 and bookedDate IS NOT NULL
 order by bookedDate asc
");

score 0 · Answer 2 · answered Nov 20 '19 at 11:22

so if the date is null how it should be sorted? as I understand form your question, you have only 3 records that have then date, the other dates are null in case you need to sort those 3 then all the records, then you may choose 1 of the following solutions:

convert all null to 0 in your db if possible, you can also make the field as not null, and it will be added as 0 automatically if not inserted
you have an option in your query to replace null with 0, check this answer Replace null with 0 in MySQL

Sorry to ask.What is the relevant if i do so?Will it retrieve all the rows? — Ajimi Mol, Nov 20 '19 at 11:26

score 0 · Answer 3 · answered Nov 20 '19 at 11:40

0

Have you tried

ORDER BY -bookedDate asc, bookedDate asc

instead of

order by (bookedDate is null) asc , bookedDate asc

answered Nov 20 '19 at 11:40

pnbps

67
2
6

PHP mysql order by nullable date field

3 Answers3