date mixed with if in php and mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thadson
Forum Newbie
Posts: 19
Joined: Fri Jul 15, 2005 12:29 pm

date mixed with if in php and mysql

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: date mixed with if in php and mysql

Post by requinix »

Where is the call to mysql_query? What's your actual code?
thadson
Forum Newbie
Posts: 19
Joined: Fri Jul 15, 2005 12:29 pm

Re: date mixed with if in php and mysql

Post 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...
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Post 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').
thadson
Forum Newbie
Posts: 19
Joined: Fri Jul 15, 2005 12:29 pm

Re: date mixed with if in php and mysql

Post 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
thadson
Forum Newbie
Posts: 19
Joined: Fri Jul 15, 2005 12:29 pm

Re: date mixed with if in php and mysql

Post by thadson »

Can anyone help me with this please? :crazy:
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Post 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.
thadson
Forum Newbie
Posts: 19
Joined: Fri Jul 15, 2005 12:29 pm

Re: date mixed with if in php and mysql

Post by thadson »

Thank you...
That actually seems to work... :D
Post Reply