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
– Ajimi Mol, Commented Nov 20, 2019 at 11:16
There are 3 rows and those rows are displaying in application.I want to show the other rows also — Ajimi Mol
– Ajimi Mol, Commented Nov 20, 2019 at 11:34
then remove statement bookedDate is null,so it will show all — Rp9
– Rp9, Commented Nov 20, 2019 at 11:38

Rp9 · Accepted Answer · 2019-11-20 11:21:43Z

0

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
");

answered Nov 20, 2019 at 11:21

Rp9

1,9612 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bassem Shahin · Accepted Answer · 2019-11-20 11:22:05Z

0

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

answered Nov 20, 2019 at 11:22

Bassem Shahin

7067 silver badges14 bronze badges

1 Comment

Ajimi Mol Over a year ago

Sorry to ask.What is the relevant if i do so?Will it retrieve all the rows?

pnbps · Accepted Answer · 2019-11-20 11:40:51Z

0

Have you tried

ORDER BY -bookedDate asc, bookedDate asc

instead of

order by (bookedDate is null) asc , bookedDate asc

answered Nov 20, 2019 at 11:40

pnbps

672 silver badges6 bronze badges

Collectives™ on Stack Overflow

PHP mysql order by nullable date field

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related