Date problem in PHP (SELECT)

Asked

Viewed 79 times

0

Good afternoon! I have a PHP question! I have a code to show all the fields of a table where the date is 2018.

$sql_manutencao = 'SELECT * FROM manutencao WHERE data_trabalho LIKE "2018%" AND cod_utilizador ='.$id;

The code is working perfectly but I would like to make the condition of the 2018 date automatic! But only for the year 2018!

I want somehow the date I put in the LIKE both give for the format dd-mm-yyyy and for the format yyyy-mm-dd but without using LIKE "%2018%"

I also warn that I am not the one running the comic and may be subject to regular changes!

If you need any more information just say that I change the code. Thank you!

sql

  • What do you mean by "automatic"? Want data_trabalho be always 2018? If that’s the case, you could use EQUAL "2018" instead of LIKE.

  • What do you mean? I don’t understand your doubt,

  • If it is working perfectly, what do you want to change? What do you mean by "date is automatic but only for 2018"? If it is only for 2018, what would be the "automatic"?

  • I want somehow the date I put in the LIKE both give for the format dd-mm-yyyy and for the format yyyyyy-mm-dd But without using LIKE "%2018%"

  • 1

    But the date in the bank will not always be in the same format?

  • Not really! Who manages the comic is not me! And may be subject to changes regularly!

  • What is the data type of the data_work field?

  • Your question is not about PHP but about SQL, could you inform which DBMS? Mysql, Postgresql... If you are one of the previous take a look at the function YEAR of Mysql and EXTRACT postgresql.

  • My DBMS is Mysql and the data type of the data_work field is "date"

  • If the field is date type then use YEAR(your_date) to pick only the year, regardless of whether the display format is day/month/year, month/day/year or year-month-day.

  • ok! thank you! it was solved!

Show 6 more comments

1 answer

0


<?
    $data = "13/05/2019";

    $data = explode("/",$data);

    if( strlen($data[0]) == 2 )
    {
        // este comando abaixo irá inverter a data em caso de um formato ou outro.
        $data = array_reverse($data);
    }

    $data = date('Y',strtotime(implode("/",$data)));

    $sql_manutencao = "SELECT * FROM manutencao where date_format(data_trabalho,'%Y') = '$data' AND cod_utilizador =".$id;

  • Can I just put the year instead of the full date? Because actually I only want the year but my problem is that the date in the BD is as "date" format or is with the format of yyyy-mm-dd but I want to make the selection by year!

  • I changed the code above. You can pass either dd/mm/yyy or yyyy/mm/dd date which in query will be compared only year.

  • I really appreciate your help but still not giving... I put the date 13/05/2018 and shows nothing being that I have a user with the date 13/05/2018

  • I added the date_format, I forgot.

  • You’ve done it now! Thanks for your help!

Browser other questions tagged sql

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