7 Replies - 2660 Views - Last Post: 14 February 2014 - 01:37 PM

#1 Lieoften  Icon User is offline

  • D.I.C Regular

Reputation: 21
  • View blog
  • Posts: 357
  • Joined: 06-January 10

Trying to update a table with datediff()

Posted 13 February 2014 - 07:54 PM

So i've got some code here;
UPDATE starinformation SET starOwner = nextOwner WHERE underSiege = 'true' AND  DATEDIFF(SiegeStart, (current_timestamp)) = 1;


i've been following some tutorials online--and while i had this code working for about six minutes earlier--before I added the "underSiege = 'true'", it's now acting as though the entire code is invalid.

I'm not receiving any errors from the database, nor am i getting any errors in my code (PHP, PDO). but the code just doesn't want to work.

Attached is a picture of the database setup.

Is This A Good Question/Topic? 0
  • +

Replies To: Trying to update a table with datediff()

#2 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Trying to update a table with datediff()

Posted 13 February 2014 - 08:09 PM

Is underSiege really a string with the value 'true'? If so, then why would you not be using an actual boolean? If it already is a boolean, use 1 or TRUE, not a string 'true'.
Was This Post Helpful? 0
  • +
  • -

#3 Lieoften  Icon User is offline

  • D.I.C Regular

Reputation: 21
  • View blog
  • Posts: 357
  • Joined: 06-January 10

Re: Trying to update a table with datediff()

Posted 13 February 2014 - 08:21 PM

It's an ENUM actually. but point taken. changed it over to a boolean, still not updating the table.

This post has been edited by Atli: 13 February 2014 - 09:06 PM

Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Trying to update a table with datediff()

Posted 13 February 2014 - 08:34 PM

Are you sure there is some data that matches the criteria? Try converting the UPDATE into a SELECT and see what is returned.
SELECT starOwner, nextOwner
FROM starinformation
WHERE
	underSiege AND
	DATEDIFF(SiegeStart, CURRENT_TIMESTAMP) = 1;



Also keep in mind how DATEDIFF works:

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_datediff said:

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions.

Meaning that the high value should come first if you want a positive result. It subtracts the second argument from the first, so assuming SiegeStart is a date in the past, using the current timestamp for the second argument will never result in a positive value.
Was This Post Helpful? 0
  • +
  • -

#5 Lieoften  Icon User is offline

  • D.I.C Regular

Reputation: 21
  • View blog
  • Posts: 357
  • Joined: 06-January 10

Re: Trying to update a table with datediff()

Posted 13 February 2014 - 08:37 PM

that did not cross my mind. just switched it over--I'm not getting any returns for that. so now i just need to figure out Why.

This post has been edited by Atli: 13 February 2014 - 09:05 PM
Reason for edit:: Removed the quote. No need to quote the whole post right above yours.

Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Trying to update a table with datediff()

Posted 13 February 2014 - 09:14 PM

The "why" is obvious: There isn't any data matching your criteria; nothing is under siege since yesterday.

Keep in mind that there may exist rows that have been under siege since before yesterday, and this query won't match those. You are looking for stars under siege since yesterday only. (DIFF = 1)
Was This Post Helpful? 0
  • +
  • -

#7 Lieoften  Icon User is offline

  • D.I.C Regular

Reputation: 21
  • View blog
  • Posts: 357
  • Joined: 06-January 10

Re: Trying to update a table with datediff()

Posted 14 February 2014 - 12:17 PM

I'm testing this with a specific table that has been updated to the current_timestamp. i've removed the underSiege value, and i'm still getting nothing so Now i'm assuming the issue is with the datediff()
Was This Post Helpful? 0
  • +
  • -

#8 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1830
  • View blog
  • Posts: 5,761
  • Joined: 15-January 14

Re: Trying to update a table with datediff()

Posted 14 February 2014 - 01:37 PM

Then check it:

SELECT SiegeStart, DATEDIFF(SiegeStart, CURRENT_TIMESTAMP) FROM starinformation

This post has been edited by ArtificialSoldier: 14 February 2014 - 01:38 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1