1

All I need to do is take two datetime fields and set a third field to its result.

This has to be easy but I've been working on it for 2 days and cannot get the result I'm looking for.

$con = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME) or die('Connection failed: ' . mysqli_connect_error());

$helpme=mysqli_query($con, "UPDATE chat_main SET Exact_Time = DATEDIFF(Start_TimeStamp, End_TimeStamp)");

mysqli_close($con);
Lori
  • 130
  • 12
  • 1
    What you should fetch from an `update` query? – Federkun May 29 '15 at 18:39
  • `$helpme` is the result of an UPDATE query. It does not return a result. So, you cannot use `mysql_fetch_array` to get a result. If your query was a SELECT, you could get a result. – kainaw May 29 '15 at 18:39
  • Alright, so you are saying, use the select, then do fetch, then update table with result? – Lori May 29 '15 at 18:40
  • Yes. After the UPDATE, you use: `$helpme = mysqliquery($con, "SELECT Exact_Time from chat_main");` and then fetch a result. Of course, you should check to see if you get an error from the UPDATE or the SELECT. If there is an error, you can't fetch anything. – kainaw May 29 '15 at 18:41
  • I will try it now. Just looks backwards, since the datediff calculation seems like it should first so there is something to update the db with. – Lori May 29 '15 at 18:46
  • The UPDATE query you already have is updating the database. Everything after that is not necessary to update the database. The echo is just so you can see the new values. – kainaw May 29 '15 at 18:49
  • possible duplicate of [warning problem: expects parameter 1 to be mysqli\_result](http://stackoverflow.com/questions/2077263/warning-problem-expects-parameter-1-to-be-mysqli-result) – Jay Blanchard May 29 '15 at 18:57
  • I understand...It isn't updating the database though and I'm not receiving an error. All three fields are datetime - could this be the issue? I've tried changing that also and still no updating database. – Lori May 29 '15 at 18:59
  • Add error reporting to the top of your file(s) right after your opening PHP tag for example ` – Funk Forty Niner May 29 '15 at 19:00
  • @Fred-ii- already have that on the page, nothing shows and no update in db. – Lori May 29 '15 at 19:02
  • I edited my comment. Try using a `WHERE` clause. – Funk Forty Niner May 29 '15 at 19:03
  • but the thing is, that syntax for the `while`, is for SELECT, not update. that's why you're getting that error. so just remove the `while` and use `mysqli_affected_rows()` in a conditional, or just nothing at all and check using `mysqli_error()`. you have a few choices to check for success/fail. – Funk Forty Niner May 29 '15 at 19:03
  • 2
    @Lori The DATEDIFF function returns an integer (the number of days between the two dates). It looks like your two fields are on the same day, so DATEDIFF will likely return 0. I think you want TIMEDIFF. – kainaw May 29 '15 at 19:06
  • Please see edits above. That portion is removed and I'm just dealing with the update statement. It isn't updating with or without WHERE and I'm receiving zero errors with the error reporting on the page. – Lori May 29 '15 at 19:06

2 Answers2

2

The issue is that DATEDIFF returns the number of days between two dates. It appears that in the context of your implementation, the two dates are on the same day. So, you always get 0 as a result of DATEDIFF. I suggest using TIMEDIFF, which will be the difference of the two times, not the two dates.

kainaw
  • 4,256
  • 1
  • 18
  • 38
2

This question should really be answered. I'll remove the answer if someone else adds a better answer.

The DATEDIFF function operates on the "date" portion of the DATETIME only, and returns an integer number of days.

To get the difference between two DATETIME values as a number of seconds, you can use the TIMESTAMPDIFF function.

To get the difference returned as a TIME value, you can use TIMEDIFF function.

This begs the question why you would need to store this, since this expression can be evaluated in a SELECT statement. (One good reason for storing it is you want to add an index on it, and MySQL doesn't support function based indexes.)

And, an UPDATE statement does not return a resultset, so you can't fetch rows from it like you would from a SELECT statement.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks @Spencer7593. I understand all of this, I was only fetching the rows to see the info for testing purposes. The timediff is a crucial part of the project I'm working on and I require that it is stored. No other reason. – Lori May 29 '15 at 19:33