CODEWITHSUNDEEP
phpmysql
Night Wolf
Night Wolf

Reputation: 1

Update multiple columns with time() math

I have a table named vips with columns:

user, timePayed, timeEnds, months

The user, timePayed and months are taking values as I want (meaning they working). Now what they have is user: username

timePayed: time() (timestamp of when he payed)

months: 1-7 (its a selection of buyer)

Now I tried to put a function every time my page loads to "refresh" the 'timeEnds' column. So my function have this inside:

 $timeNow = time();
 $query = DB::query("SELECT * FROM vips WHERE timeEnd='new';");
 $row = mysql_fetch_assoc($query);
    
 $months = $row['months'];
 $timePayed = $row['timePayed'];
 $payEnds = strtotime('+'.$months.' months +1', ($timePayed - $timeNow));

Now I want to add somehow this:

 DB::query("UPDATE vips SET timeEnd='$payEnds' WHERE timeEnd='$payEnds';");

For every column to update the column -> payEnds = months * timesPayed but I can't. Columns looks like this:

 user  timePayed   TimeEnds     months
 78 1408958750  new          2
 79 1408958752  new          6
 73 1408958904  new          8

And should be looking like:

 user  timePayed   TimeEnds     months
 78 1408958750  5350978      2
 79 1408958752  5350978      6
 73 1408958904  5350978      8

Example: 5350978 = will become this (I think even this is wrong:

 DB::query("UPDATE vips SET timeEnd='$payEnds' WHERE timeEnd='$payEnds';");

Upvotes: 0

Views: 62

Answers (1)

Silviu-Marian
Silviu-Marian

Reputation: 10927

That approach would require multiple queries to run well.

Luckily MySQL was made for this so just run this query:

UPDATE vips 
SET timeEnds = UNIX_TIMESTAMP( (DATE_ADD( FROM_UNIXTIME(timePayed), INTERVAL months MONTH ) )) - UNIX_TIMESTAMP(NOW())

Which means:

FROM_UNIXTIME(timePayed) 
    -> convert from unix time to MySQL date
DATE_ADD( FROM_UNIXTIME(timePayed), INTERVAL months MONTH )  
    -> find out the expiry date
UNIX_TIMESTAMP(...)
    -> we convert the whole expiry date back to integer, for easier math
UNIX_TIMESTAMP(NOW())
    -> we see how much time is left between now and the expiry date

Downsides:

  • NOW() is not replication-friendly
  • not scalable, running this on 1000000 records on each request will be bad
  • may be a refactoring issue if you ever decide to use a view and a table instead of just this table

If it's a small thing you can leave it this way, but if you want to do a bigger thing sometimes, you should only calculate timeEnds when you need it.

Upvotes: 1

Related Questions