Expired date alias in Mysql

Asked

Viewed 149 times

-1

I have a query select which receives the user date in the database MySQL and the shape of the field is datetime, and the PHP treats the output and in HTML data as the date of registration of a personal consumption plan and when the plan will expire, I wonder if it is possible to create an alias for a subquery that returns the string "EXPIRED" if the expiration date of the record is 1 day or more.

The current query looks like this.

SELECT data_cad,data_expira,pl_nome FROM pl_misc WHERE pl_id = $Xyz LIMIT 1;

I wish I had something like AS EXPIRADO that could return in PHP with a echo. I really want to know if I could print ex: $Row['expired'] and print "Expired", "Not expired", you know, IF and ELSE condition inside Mysql itself.

mysql

  • Eliseu, I found a role in the php documentation that might help you. Please see if the Datetime::diff function helps you. Link: https://www.php.net/manual/en/datetime.diff.php

  • @Rodrigotognin really wanted to know if with IF, ELSE or any other way inside Mysql itself would be possible , even to run the query directly in phpmyadmin capiche.

  • Eliseu, I just answered another user with a command that can help you. I’ll be putting the code as an example in the answer.

1 answer

1


Eliseu, using Case When might help you. Here’s an example:

SELECT
   CASE
      WHEN DATEDIFF(data_expira, CURDATE()) < 0 THEN "Expirado" 
   ELSE data_expira END as Data
FROM pl_misc WHERE pl_id = $xyz LIMIT 1;

My idea is to take the current date and compare it to the expiration date. If the current date is longer than the expiration date, it shows "Expired".

  • #1064 - You have a syntax error in your SQL next to 'varchar, getdate(), 110) as data, CASE WHEN data > data_expira THEN 'expires' in line 1.

  • Check the fields, the names are right, the table too, but returns the error.

  • I made an example in my mysql here, I will change the answer code.

  • I understood the logic, interesting beast, thanks for that, now I can use direct in phpmyadmin. tmj

  • Thank you so much for the support, Eliseu! I also learned from this by going after the functions to assemble the query!

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.