Fetching data from mysql table using php microtime not working properly

Question

I am displaying a long list of sales details from a table in mysql. The problem is I am being able to fetch data only for a month. As soon as the month changes it doesnt display anything. For ex: data being displayed from 1/05/2015 to 30/05/2015 but from 1/06/2015 it does not display anything.

I add the timestamp values on each sale in the database using the following code:

$time = round(microtime(true)*1000);

Rest of the code is here:

<?php
include 'connect.php';
$timestamp = "";
$return = array();
$response = "";
$sql = "SELECT * FROM receipts ORDER BY timestamp DESC LIMIT 1";
$result1 = mysqli_query($db, $sql);
if($details = mysqli_fetch_array($result1)){
    $timestamp = $details['timestamp'];
}

$sql = "SELECT * FROM sales WHERE timestamp>'$timestamp' ORDER BY date ASC";
$result = mysqli_query($db, $sql);

echo "<table class=table1>";
while($fetch_options=mysqli_fetch_array($result)){
    $memo=$fetch_options['memo'];
    $product=$fetch_options['pid'];
    $qty=$fetch_options['qty'];
    $amount=$fetch_options['amt'];

    echo "<tr>";
    echo "<td align=center>$memo</td>
             <td align=center>$product</td>
             <td align=center>$qty</td>
             <td align=center>$amount</td>";
    echo "</tr>";
}
echo "</table>";
?>

I feel this problem is something to do with the timestamp values. Thanks in advance.

score 0 · Answer 1 · answered Jun 05 '15 at 17:34

0

i would commented but it still don'T have enough rep to do that.

Something tells me you should use time()instead of microtime()

time() will return the time in seconds as an INT. microtime() will give you time in microseconds as STRING or as FLOAT by using true as a first parameter => microtime(true)

As i can see you are multiplying microtime() by 1000. That will give you the same as time().

Try using time() instead of round(microtime()*1000).

http://php.net/manual/en/function.microtime.php VS http://php.net/manual/en/function.time.php

answered Jun 05 '15 at 17:34

Louis Loudog Trottier

1,189
12
23

Ok. I surely would give it a try. But what about the values already set in the db? They are set using the above code? Would it matter anything? – Ayan Jun 05 '15 at 17:39
You should make a test but i think it will be ok since both are evaluated from Unix timestamp. Just that microtime() returns in microseconds and time() in seconds. And since you are manually converting microtime in time by multiply by 1000 the values should remains very close. Except for the `round` difference – Louis Loudog Trottier Jun 05 '15 at 17:51

score 0 · Answer 2 · answered Jun 05 '15 at 17:42

0

Mysql stores dates in the following format in a database:

YYYY-MM-DD HH:MM:SS.SSS

So if you want to include a date inside your database please use this format. The sorting system will work when you do this.

Example using PHP date

$date = date("Y-m-d H:i:s");
$sql = "INSERT INTO example ('date', 'some_random_val') VALUES ($date, 'value2')";

Example using MYSQL CURDATE date (For current date)

$sql = "INSERT INTO example ('date_name', 'date', 'some_random_val') VALUES ('DATE: Auto CURDATE()', CURDATE(), 'value2')";

I prefer the solution using the php date.

answered Jun 05 '15 at 17:42

Marnix Bouhuis

445
1
6
14

Would the php part cause any trouble while fetching the data previously present in db with microseconds? – Ayan Jun 05 '15 at 17:48
I don't think so because you get the value from the db. – Marnix Bouhuis Jun 05 '15 at 17:51
I meant the data present in db is in microseconds but the php you showed will insert data in the manner you specified. So how it is possible that sql would make a difference of greater or lesser if both are in different format? – Ayan Jun 05 '15 at 17:54
Maybe this will help: [Mysql wiki: Date and Time](http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html) The first part is a sheet with all the different functions. – Marnix Bouhuis Jun 05 '15 at 17:57

Fetching data from mysql table using php microtime not working properly

2 Answers2