Convert "Y-m-d H:i:s" to "d.m.Y H:i:s"

Question

So I have a mysql data base with dates in "Y-d-m H:i:s" but on my website I have to make so that when user filters by date and time he writes in "d.m.Y H:i:s" format bat still gets the results. How do I convert that? Website is virtual exchange so on this page administrtor can filter by date and time or moderator and it writes out combined amount sold by each currency in specified datetime period. This is my code. Thank You!

<?php 
    session_start();
    if(!isset($_SESSION["id"])){ 
        header("Location:login.php");
    }
    if($_SESSION["tip"] != 0){ 
        header("Location:index.php");
    }
    
    include_once("baza.php");
    $veza = spojiSeNaBazu();
    $id_novi_iznos="";

    $upit = "SELECT `valuta`.`naziv`, SUM(`zahtjev`.`iznos`) AS 'iznos' FROM `zahtjev` JOIN `valuta` ON `prodajem_valuta_id`=`valuta_id` ";

    if(isset($_POST["submit"])){
        $id_korisnika = $_SESSION["id"];
        $kreiran_od = $_POST["kreiran_od"];
        $kreiran_do = $_POST["kreiran_do"];
        $moderator = $_POST["mod"];

        if(isset($_POST["kreiran_od"]) && $_POST["kreiran_od"] != "" && isset($_POST["kreiran_do"]) && $_POST["kreiran_do"] != ""){
            $upit .= "WHERE `datum_vrijeme_kreiranja` < '$kreiran_do' AND `datum_vrijeme_kreiranja` > '$kreiran_od'";
        }
        if(isset($_POST["mod"]) && isset($_POST["mod"]) != "" && isset($_POST["kreiran_od"]) && $_POST["kreiran_od"] != "" && isset($_POST["kreiran_do"]) && $_POST["kreiran_do"] != ""){
            $upit .= "AND `moderator_id` = $moderator";
        }else if(isset($_POST["mod"]) && isset($_POST["mod"]) != ""){
            $upit .= "WHERE `moderator_id` = $moderator";
        }
        
    }
    $upit .= " GROUP BY `prodajem_valuta_id`";
    $rezultat = izvrsiUpit($veza, $upit);
    zatvoriVezuNaBazu($veza);
    
    function moderatori(){
        $veza = spojiSeNaBazu();
        $upit = "SELECT * FROM korisnik WHERE tip_korisnika_id = 1";
        $moderatori = izvrsiUpit($veza, $upit);
        
        while ($korisnik = mysqli_fetch_array($moderatori)) {
            echo "<option value='$korisnik[0]'>$korisnik[2]</option>" ;
        }
        
        zatvoriVezuNaBazu($veza);
    }

?>

<!DOCTYPE html>
<html>
    <head>
        <title>Ukupni iznos prodanih valuta </title>
        <meta charset="utf-8">
        <meta name="autor" content="Benček Tena">
        <meta name="datum posljednje promjene" content="31.8.2020">
        <link rel="stylesheet" type="text/css" href="iwaa.css">
    </head>
    
    <body>
        <header>
             <?php include 'header2.php';?>
        </header>
        
        <div class="iznosi">
            <h3>Unesite novi iznos</h3>
            <form id="obrazac" name="obrazac" method="post" 
            action="<?php echo $_SERVER["PHP_SELF"];?>"> 
            
                <label for="kreiran_od">Zahtjev kreiran od</label>
                <input name="kreiran_od" type="text" placeholder="Kreiran od.." />
                <br>
                <label for="kreiran_do">Zahtjev kreiran od</label>
                <input name="kreiran_do" type="text" placeholder="Kreiran do.." />
                <br>
                <select id="mod" name="mod">
                <?php moderatori() ?>
                </select>
                <br>
                <input class="button3" name="submit" type="submit" value="Unesi"/>
            </form>
            <table id="tablica" width="100%">
                <caption>Ukupni iznos prodanih valuta</caption>
                <thead>
                    <tr>
                        <th>Naziv valute</th>
                        <th>Ukupni iznos</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                        if(isset($rezultat)){
                            while($red = mysqli_fetch_array($rezultat)){
                                echo "<tr>";
                                echo "<td>{$red[0]}</td>";
                                echo "<td>{$red[1]}</td>";
                                echo "</tr>";
                            }
                        }
                    ?>
                </tbody>
            </table>
        </div>
    
        <footer>
            <?php include 'footer.php';?>
        </footer> 
    </body>
</html>

nbk · Answer 1 · 2020-08-31T17:05:59.473

1

You didn't tell us which column you want to reformat

But in mysql You use DATE_FORMAT

SELECT DATE_FORMAT(NOW(),'%d.%m.%Y %H:%i:%s')

| DATE_FORMAT(NOW(),'%d.%m.%Y %H:%i:%s') |
| :------------------------------------- |
| 31.08.2020 17:49:23                    |

db<>fiddle here

You should urgently switch to **prepared statements with parameters see How can I prevent SQL injection in PHP?

When you want to do it in the mysql queries, which i don't really understand.

$upit .= "WHERE `datum_vrijeme_kreiranja` < DATE_FORMAT('$kreiran_do','%d.%m.%Y %H:%i:%s') AND `datum_vrijeme_kreiranja` > DATE_FORMAT('$kreiran_od','%d.%m.%Y %H:%i:%s')";

In php you would do it:

$old_date = strtotime($kreiran_do);
$new_date = date('d.m.Y H:i:s', $old_date);

edited Aug 31 '20 at 17:05

answered Aug 31 '20 at 16:50

nbk

45,398
8
30
47

I nedd to reformat $kreiran_od and $kreiran_do – Tena Benček Aug 31 '20 at 16:54
basicallly xou should urge3nt switch to **prepared statements with parameters** – nbk Aug 31 '20 at 16:56
izt is quite hard to understand, why you wnat to change the dateformat – nbk Aug 31 '20 at 17:06
it is for college assingment so the professor wants that every time user writes date it is in croatian format – Tena Benček Aug 31 '20 at 17:18
usually only the poutput is formatted. So change the output column with dateformat. Both input varaibles for the Where clause, don't need – nbk Aug 31 '20 at 17:35
I managed to fix it. Thank you very much! – Tena Benček Aug 31 '20 at 18:23

Convert "Y-m-d H:i:s" to "d.m.Y H:i:s"

1 Answers1