1

I am trying the list the remining time on an auction listing. Below is the SQL query to gather the item data. There is a created and end time in the database, stored as datetime. At the end of the foreach, I get the current time from the system.

What I want is the difference between them, so that I can display the remaining time on the auction. I have tried a few ways, but can't seem to solve it. How do I do it?

$query = $this->db->query("SELECT * FROM tbl_auction_listing WHERE list_pk = $item LIMIT 1;");
            foreach ($query->result() as $row)
            {
               $sellerid = $row->seller_fk;

               $title = $row->title;

               $description = $row->description;

               $img_url = $row->image_url;
               $category = $row->category_fk;
               $starttime = $row->start_datetime;
               $endtime = $row->end_datetime;
               $startprice = $row->start_price;
               $reserve = $row->reserve_price;
               $buyprice = $row->bin_price;
               $visits = $row->visits;  
               $now = date("Y-m-d H:i:s");

            }

Note: 

$remaining = $endtime->diff($now)->format("%m months, %d days, %H hours and %i minutes");

this line does not seem to work. It errors:

Fatal error: Call to a member function diff() on a non-object in controllers/listings.php on line 28

user3175451
  • 163
  • 1
  • 14
  • http://stackoverflow.com/a/20556191/ - if that's not what you're looking for, show us what you tried. There are many ways to do this. – Funk Forty Niner Sep 14 '14 at 04:15
  • I just added an edit which shows what I tried, thanks. – user3175451 Sep 14 '14 at 04:16
  • Try `timediff` instead of `diff` - might be the reason why. That, and/or a non-object; maybe a failed query. – Funk Forty Niner Sep 14 '14 at 04:17
  • There was a similar question posted earlier, this answer was submitted http://stackoverflow.com/a/25829875/ - again, many ways to do this. Have you Google'd for a bit? Checked Stack? I usually spend about an hour (min.) on Google myself, along with a fine cup of Espresso ;) *but that's just me.* – Funk Forty Niner Sep 14 '14 at 04:21
  • I generally peek around a bit. I tried editing my SQL query to `SELECT seller_fk, title, description, image_url, category_fk, remaining AS (TIMEDIFF(end_datetime,NOW()), start_price, reserve_price, bin_price, visits FROM tbl_auction_listing WHERE list_pk = 15 LIMIT 1;`, but that gives a syntax error. – user3175451 Sep 14 '14 at 04:24
  • Check out [`timediff`](http://www.w3resource.com/mysql/date-and-time-functions/mysql-timediff-function.php). Plus, did you try my suggestion(s)? – Funk Forty Niner Sep 14 '14 at 04:24
  • What syntax error exactly? Plus, you have a missing bracket `)` for `SELECT seller_fk, title, description, image_url, category_fk, remaining AS (TIMEDIFF(end_datetime,NOW()), start_price, reserve_price, bin_price, visits FROM tbl_auction_listing WHERE list_pk = 15 LIMIT 1;` - Try `SELECT seller_fk, title, description, image_url, category_fk, remaining AS (TIMEDIFF(end_datetime,NOW()), start_price, reserve_price, bin_price, visits FROM tbl_auction_listing WHERE list_pk = 15 LIMIT 1);` – Funk Forty Niner Sep 14 '14 at 04:25
  • not sure why the bracket was missing, but here was the error: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(TIMEDIFF(end_datetime,NOW()), start_price, reserve_price, bin_price, visits FRO' at line 1` – user3175451 Sep 14 '14 at 04:27
  • I'm not so hot with aliases, but try removing that bracket `(` and try `SELECT seller_fk, title, description, image_url, category_fk, remaining AS TIMEDIFF(end_datetime,NOW()), start_price, reserve_price, bin_price, visits FROM tbl_auction_listing WHERE list_pk = 15 LIMIT 1;` - if that still throws an error, consider going through some of the questions on Stack and Googling. I'm sure you'll find something. – Funk Forty Niner Sep 14 '14 at 04:32
  • yep. still a fail. Its 1am here; I'll sleep on it and start fresh tomorrow. If I solve it I'll come back and post the answer. – user3175451 Sep 14 '14 at 04:46
  • It's 1am here too (headed off too), a fresh head in the morning usually does the trick. If I find anything more also, I'll let you know, *cheers.* – Funk Forty Niner Sep 14 '14 at 04:48

2 Answers2

0

im no php expert, but according to the docs there is datetime diff and datetime sub.

in general if not possible to calculate directly, i tend to convert to unixtime seconds, calculate and convert back to datetime.

Christoph Lösch
  • 645
  • 7
  • 22
0

You need to create DateTime objects in PHP from your query results.

$now        = new DateTime();
$endtime    = new DateTime($row->end_datetime);
$difference = $endtime->diff($now);
$remaining  = $difference->format("%m months, %d days, %H hours and %i minutes");

Also, you cannot chain methods in PHP like in other languages, such as JavaScript.

Slaquor
  • 1
  • 3
  • I am currently using EXACTLY as you posted: `$now = new DateTime(); $endtime = new DateTime($row->end_datetime); $difference = $endtime->diff($now); $remaining = $difference->format("%m months, %d days, %H hours and %i minutes"); }` but I am still getting the error ` Fatal error: Call to undefined method DateTime::diff() in /home/content/61/11420661/html/btcbidder.com/application/controllers/listings.php on line 29`. You can see the example at [http://btcbidder.com/listings/view/14 – user3175451 Sep 14 '14 at 05:36
  • On the off chance that this is a php version error, I put in a request to upgrade with my host. I was running 5.3, which should have had this method available. I am upgrading to 5.4, so hopefully nothing breaks on my other sites in the process. – user3175451 Sep 14 '14 at 05:38
  • Upgraded to 5.4 and still the same error using the code you gave. – user3175451 Sep 14 '14 at 16:12
  • The code was just off the top of my head to give you a reference and a starting point. My apologies, I should have made that clear. Check out the PHP Docs on [DateTime::diff](http://php.net/manual/en/datetime.diff.php) to get more info. – Slaquor Sep 14 '14 at 17:13
  • Im not certain thats it.. I think the code is quite correct; its just that the system telling me that there is no diff method for DateTime objects.. – user3175451 Sep 14 '14 at 21:13