Page 1 of 1

date mixed with if in php and mysql

Posted: Wed Nov 18, 2009 8:39 am
by thadson
Hi,

I'm running a script several times a day.
When I run it the first time after midnight, I want the field "today" copy itself over to the field "yesterday" then i want the "date" field updated to today's date.
I tried this:

Code: Select all

 
$sql='SELECT date FROM mydb WHERE date < CURRENT_DATE()';
IF($sql){
    $sql='UPDATE mydb SET yesterday = today';
        $sql='UPDATE mydb SET today = CURRENT_DATE';
}
 
However it keeps updating the yesterday field from the today one even though the date is already today's date...
I tried a couple of variations, with same result.

Can anyone point me in the right direction, please?

Re: date mixed with if in php and mysql

Posted: Wed Nov 18, 2009 10:36 am
by requinix
Where is the call to mysql_query? What's your actual code?

Re: date mixed with if in php and mysql

Posted: Wed Nov 18, 2009 12:58 pm
by thadson
It is 3 files, all pretty short:

function.php :

Code: Select all

 
function db_on() {
  $link=mysql_connect(DB_SERVER,DB_SERVER_USERNAME,DB_SERVER_PASSWORD) or die('The mySQL server is down');
  mysql_select_db(DB_DATABASE,$link) or die('mySQL database problem');
  define('_sql',$link);
}
 
function quote($value) {
  if (get_magic_quotes_gpc()) $value=stripslashes($value);
  $value='\''.mysql_real_escape_string($value).'\'';
  return $value;
}
 
function db_off() {
  mysql_close(_sql);
}
 
constant.php :

Code: Select all

 
<?php
  define('HTTP_SERVER', 'http://localhost');
  define('HTTPS_SERVER', 'http://localhost');
  define('ENABLE_SSL', false);
  define('HTTP_COOKIE_DOMAIN', 'localhost'); 
  define('HTTPS_COOKIE_DOMAIN', 'localhost');
  define('HTTP_COOKIE_PATH', '/');
  define('HTTPS_COOKIE_PATH', '/');
  define('DIR_WS_INCLUDES', 'inc/');
 
  define('DB_SERVER', 'localhost');
  define('DB_SERVER_USERNAME', 'mydb_username');
  define('DB_SERVER_PASSWORD', 'pw1234');
  define('DB_DATABASE', 'mydb');
  define('USE_PCONNECT', 'false');
  define('STORE_SESSIONS', 'mysql');
?>
 
test.php :

Code: Select all

 
<?php
session_start();
require_once('inc/constant.php');
require_once('inc/function.php');  //basic functions
db_on();
 
$sql='SELECT date FROM mydb WHERE date < CURRENT_DATE()';
IF($sql){
    $sql='UPDATE mydb SET yesterday = today'; 
}
$res=mysql_query($sql,_sql);
db_off();
?>
 
let's say today's date is 11/18/2009
That db part looks like this:

yesterday today date
1000 1500 2009-11-17
100 200 2009-11-17
555 657 2009-11-17

Because date is less than CURRENT_DATE() it should end up like this:

yesterday today date
1500 1500 2009-11-18
200 200 2009-11-18
657 657 2009-11-18

However if the date is already today's date like this:

yesterday today date
1500 2000 2009-11-18
200 300 2009-11-18
657 700 2009-11-18

Than today must not overwrite yesterday!

Then I will replace the today column's data, but that is a whole different story, and that part of the code is already working...

Posted: Wed Nov 18, 2009 2:02 pm
by Jonah Bron
So, there is only one row in this table?

If that is so, just SELECT today FROM mydb, then use use strtotime(), and compare the query results with strtotime('today').

Re: date mixed with if in php and mysql

Posted: Wed Nov 18, 2009 2:17 pm
by thadson
I do not know what do you mean by this:
Jonah Bron wrote:So, there is only one row in this table?

If that is so, just SELECT today FROM mydb, then use use strtotime(), and compare the query results with strtotime('today').
There are several rows and several columns, here is the mysql file:

Code: Select all

CREATE TABLE IF NOT EXISTS `mydb` (
  `id` int(5) NOT NULL DEFAULT '0',
  `yesterday` int(30) NOT NULL DEFAULT '0',
  `today` int(30) NOT NULL DEFAULT '0',
  `date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `mydb`
--
 
INSERT INTO `mydb` (`id`, `yesterday`, `today`, `date`) VALUES
(100, 1000, 1500, '2009-11-17'),
(101, 100, 200, '2009-11-17'),
(102, 555, 657, '2009-11-17');
(I'm sure it could be better... I'm just started learning this.)

in the script, elswhere I already handle time:
$sql='UPDATE mydb SET date='.quote(date('Y-m-d H:i:s'))';
and it works

I assumed I can use similar in the comparison.

What I have the problem with is the IF() statement:

I only want to update the yesterday column, IF the date is less than today's date

Re: date mixed with if in php and mysql

Posted: Thu Nov 19, 2009 4:09 pm
by thadson
Can anyone help me with this please? :crazy:

Posted: Thu Nov 19, 2009 4:37 pm
by Jonah Bron
If I understand correctly, you really don't need an if. Just use a convergence of you SQL statements above:

Code: Select all

$sql = mysql_query('UPDATE mydb SET yesterday = today WHERE date < CURRENT_DATE()');
Or, something like that. Untested.

Re: date mixed with if in php and mysql

Posted: Fri Nov 20, 2009 12:59 pm
by thadson
Thank you...
That actually seems to work... :D