Database date returning 12/31/1969

Asked

Viewed 4,071 times

7

I have in my script a call to date and to print for me brings me a date of 12/31/1969, and not the date that is in the database.

This is my Code:

@$pag = "$_GET[pag]";
        if($pag >= '1'){
         $pag = $pag;
        }else{
         $pag= '1';
        }

        $maximo = '5'; //RESULTADOS POR PÁGINA
        $inicio = ($pag * $maximo) - $maximo;


  $topico = $_GET['cat'];

  $noticias = mysqli_query($conexao,"SELECT  id, thumb, titulo, texto, categoria, 'data', autor, valor_real, valor_pagseguro, visitas FROM lp_post WHERE 
  categoria = '$topico' ORDER BY data DESC LIMIT $inicio,$maximo")or die (mysqli_error($conexao));

            if(@mysqli_num_rows ($noticias) <= '0'){
             echo "Nenhuma mensagem encontrada no momento"; 
                }else{

                    $numero = '0';

                  while($res_noticias = mysqli_fetch_array($noticias)){
                    $id = $res_noticias[0];
                    $thumb = $res_noticias[1];
                    $titulo = $res_noticias[2];
                    $texto = $res_noticias[3];
                    $categoria = $res_noticias[4];
                    $data = $res_noticias[5];
                    $autor = $res_noticias[6];
                    $valor_real = $res_noticias[7];
                    $valor_pagseguro = $res_noticias[8];
                    $visitas = $res_noticias[9];
                    $numero ++;




?>
<div class="categoria">
<a href="index.php?topicos=nav/single&amp;topico=<?php echo $id; ?>">
    <h1><?php echo $titulo; ?></h1>

    <span class="info">Data:<?php echo date("d/m/Y - H:m", strtotime($data)); ?> | Autor: <?php echo $autor ?> | Categoria: <?php echo $categoria ?> | Visitas:<?php echo $visitas ?> </span>

      <img src="uploads/<?php echo $categoria ?>/<?php echo $thumb ?>"  class="alinleft" alt="<?php echo $titulo ?>" width="100" height=""/>

    <p class="categoria_p"><?php echo strip_tags(trim(str_truncate($texto, 175, $rep=TRUNC_BEFORE_LENGHT))); ?></p>
  </a>  
</div> 

<?php
    }
 }
 ?>
    <div class="paginator">
<?php

        //USE A MESMA SQL QUE QUE USOU PARA RECUPERAR OS RESULTADOS
        //SE TIVER A PROPRIEDADE WHERE USE A MESMA TAMBÉM
        $sql_res = mysqli_query($conexao,"SELECT * FROM lp_post WHERE categoria = '$topico'");
        $total = mysqli_num_rows($sql_res);

        $paginas = ceil($total/$maximo);
        $links = '5'; //QUANTIDADE DE LINKS NO PAGINATOR

        echo "<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=1\">Primeira Página</a>&nbsp;&nbsp;&nbsp;";


        for ($i = $pag-$links; $i <= $pag-1; $i++){
        if ($i <= 0){
        }else{
        echo"<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=$i\">$i</a>&nbsp;&nbsp;&nbsp;";
        }
        }echo "$pag &nbsp;&nbsp;&nbsp;";

        for($i = $pag +1; $i <= $pag+$links; $i++){
        if($i > $paginas){
        }else{
        echo "<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=$i\">$i</a>&nbsp;&nbsp;&nbsp;";
        }
        }
        echo "<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=$paginas\">Última página</a>&nbsp;&nbsp;&nbsp;";
?>

The error given is the date of 12/31/1696 and not the date of the database.

The database configuration is timestamp.

  • This looks like having one -1 as value. OR can be syntax error. Rename column 'data' using the backtick ` instead of single quotes.

  • Give an example of the date coming from the bank.

  • the date in the database is in this format 2015-09-29 14:31:43

  • 31/12/1969 appears when date() failed to parse last date, I believe the error is that your query is returning the word data in place of 2015-09-29 14:31:43, as pointed by @bigown. Test the way the code is in the question, from a echo $data; before converting her.

  • 1

    @Aulo I saw that you switched the acceptance of one answer to another. Did you do this because you wanted to or was it by accident? You can only choose one answer. Vote can on all. See the [tour].

  • @bigown I also had already noticed and was about to comment on this... I think it was by accident, I did it myself when I didn’t know the site right and just wanted to thank everyone... :)

  • 1

    @gustavox I realize that anyone who is new does this a lot. If one wants to choose another, it is her right, but sometimes it is not what she wants. She did without realizing, there is "awarded" that received the "vote" last. IE, it is lottery.

  • Yes @bigown and in this case particularly seems extremely wrong, because in the first comment you had already given the answer, he first accepted his... anyway, there should be a mechanism to correct this kind of thing in the absence of the AP, that sometimes does not come back and is maintained the error/ injustice....

Show 3 more comments

2 answers

5


Your problem is that you are using simple quotes for the date field 'data' when you should use the crase

`data`

When the resultset returns to php, it sends the string data making the function strtotime($data) return the first timestamp day.

Fix your query to:

$noticias = mysqli_query($conexao, 
                "SELECT  id, thumb, titulo, 
                         texto, categoria, `data`, 
                         autor, valor_real, valor_pagseguro, 
                         visitas 
                   FROM lp_post 
                  WHERE categoria = '$topico' 
                  ORDER BY data DESC LIMIT $inicio,$maximo") 
              or die (mysqli_error($conexao));
  • 1

    OK! I made this correction and everything worked out. Thank you very much.Thanks.

4

Since I answered initially in the comment, I will post here.

To ensure that you are speaking of a database symbol, such as a column, for example and not to be confused with SQL syntax, you need to use backtick (crase). Your code is using simple quotes which is the delimiter of a text. So what is being used there is the text 'data'. When trying to interpret this text as date, it returns an invalid date which is a value prior to the 01/01/1970, the beginning of timestamp.

The solution is to fix the syntax.

SELECT id, thumb, titulo, texto, categoria, `data`, autor, valor_real,
            valor_pagseguro, visitas FROM lp_post

Or how it works too, after all data does not get confused with SQL in this case:

SELECT id, thumb, titulo, texto, categoria, data, autor, valor_real,
            valor_pagseguro, visitas FROM lp_post

I put in the Github for future reference.

  • 1

    Thank you very much for the good will of all of you who will help me. Thank you very much.

  • 1

    Initially I thought it might be the echo calling for dd/mm/YYYY, while the date in the bank is YYYY/mm/dd, but the strotime resolves (?) in the case I think right... It is that to convert the date I did thus, and the AP code seemed simpler... but I’m only doing this meeting because I wanted to ask the following: in case you need to use the ? Não é só deixar sem nada? Eu uso sem e funciona... tá errado? O melhor é incluir as datas com in select?

  • 1

    @Gustavox is right, I improved.

  • Ah, thanks! I’m glad I won’t have to change everything! ^^

Browser other questions tagged

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