How can I work with date in php and mysql?

Question

I want to know how can I work with dates in MySQL and PHP. I want to know how many days remain, i want to know how many day is defference(distance) between nowTime and createTime ?

<?php
    $response = array();

    // include db connect class
    require_once __DIR__ . '/db_connect.php';

    // connecting to db
    $db = new DB_CONNECT();

    ////ISSET
    if (isset($_POST['projectId'])) {
        $projectId = $_POST['projectId'];

        $result = mysql_query("SELECT *FROM projects WHERE projectId='$projectId'") ;
        if (mysql_num_rows($result)>0) {
            $result = mysql_fetch_array($result);

            $response["title"]=$result["title"];
            $response["exp"]=$result["exp"];
            $response["userIdKarmand"]=$result["userIdKarmand"];
            $response["userIdKarfarma"]=$result["userIdKarfarma"];
            $response["cost"]=$result["cost"];
            $response["zemanat"]=$result["zemanat"];
            $response["time"]=$result["time"];
            $response["createTime"]=$result["createTime"];
            $response["type"]=$result["type"]; 
            $response["nowTime"]=DATE("y-m-d");
            $response["remainTime"]=DATE("y-m-d")-    strtotime($response["createTime"])+$response["time"];
            echo json_encode($response);
        } 
        else {
        }
    }
    else {
   }
?>

It's not working. What can I do? I want compare createtime and nowtime and find out how many days I have time?

Step one is stop using a 10 year old database API that is very insecure and long deprecated. https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1 — miken32, Sep 21 '18 at 15:51

score 0 · Answer 1 · answered Sep 21 '18 at 17:41

0

strtotime converts formatted string date format to timestamp. You can't substract timestamp data from string data. You have to first convert $response["remainTime"] to timestamp using strtotime() and then substract strtotime($response["createTime"]) from this value.

answered Sep 21 '18 at 17:41

Zebra8844

141
5

i think i dont explain my request correctly. i want to know how many day is between nowTime and createTime ? – zneku Sep 22 '18 at 02:53
If "nowTime" and "createTime" are timestamps, have a look to [link](https://stackoverflow.com/questions/4079814/finding-days-between-2-unix-timestamps-in-php). If these are string dates, see the date_diff() function. See the PHP documentation for examples. – Zebra8844 Sep 24 '18 at 09:44

score 0 · Answer 2 · answered Sep 21 '18 at 18:46

The best way to go through a MySQL result would be to loop through the fetch_assoc() function.

$result = mysql_query("SELECT * FROM projects WHERE projectId = '$projectId'");

// while() through an associative array from the MySQL object
while($result =& $result->fetch_assoc()) {
    $response["title"]                = $result["title"];
    $response["exp"]                  = $result["exp"];
    $response["userIdKarmand"]        = $result["userIdKarmand"];
    $response["userIdKarfarma"]       = $result["userIdKarfarma"];
    $response["cost"]                 = $result["cost"];
    $response["zemanat"]              = $result["zemanat"];
    $response["time"]                 = $result["time"];
    $response["createTime"]           = $result["createTime"];
    $response["type"]                 = $result["type"];
    $response["nowTime"]              = DATE("Y-m-d H:i:s"); // this is how you would need to format dates to work with MySQL
    $remainTime = (strtotime($response['nowTime']) - strtotime($response['createTime'])); // this would return how many seconds have passed since $response['createTime'] until now
    $response["remainTime"]           = $remainTime;
 }
echo json_encode($response);

This would allow you to have how many seconds since $response['createTime'] held in the $remainTime variable.

How can I work with date in php and mysql?

2 Answers2