Accesing dates in Mysql thru PHP

carlmty

I have a calendar of procesess, and I need to obtain a later date, different than the operation's date.

When I do the query to the Mysql database, it displays the date of the first movement of the calendar, and this is wrong, because it suppose to display the later date of the movement.

Here is the code, to explain myself more:

 <?php
 /* Get operation date */
  $result=mysql_query("select * from fecoper",$Conexion);
  $row=mysql_fetch_array($result);
  if ($row)
    {
       $fope = $row["fecope_fec"];
    }
     echo $fope;
  /* It gets the effective date of payment */
  $result=mysql_query("select * from calendar where fec_cal > $fope",$Conexion);
  $row=mysql_fetch_array($result);
  if ($row)
    {
    $fefec = $row["fec_cal"];
       $sw = 1;
    }

echo "calendar", $fefec;
?>

And the table definitions for this example:

CREATE TABLE calendar (
fec_cal date NOT NULL default '0000-00-00',
dia_cal int(1) NOT NULL default '0',
fcorsin_cal date NOT NULL default '0000-00-00',
fcorcon_cal date NOT NULL default '0000-00-00',
aplisin_cal int(1) NOT NULL default '0',
aplicon_cal int(1) NOT NULL default '0',
tasavis_cal decimal(4,2) NOT NULL default '0.00',
tasafij_cal decimal(4,2) NOT NULL default '0.00',
persin_cal int(2) NOT NULL default '0',
percon_cal int(2) NOT NULL default '0',
festivo_cal char(1) NOT NULL default '',
habil_cal char(1) NOT NULL default '',
pagsep_cal char(1) NOT NULL default '',
percat_cal int(1) NOT NULL default '0',
aplicat_cal char(1) NOT NULL default '',
tasaptmo_cal decimal(4,2) NOT NULL default '0.00',
fcorcat_cal char(1) NOT NULL default '',
pagtra_cal char(1) NOT NULL default '',
pagpre_cal char(1) NOT NULL default '',
cappla_cal char(1) NOT NULL default '',
PRIMARY KEY (fec_cal)
) TYPE=MyISAM COMMENT='Tabla del Calendario';

#

Populate the tablecalendar into the database

#

INSERT INTO calendar VALUES ('2003-01-01', 3, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 1, 1, 'N', 's', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-02', 4, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 1, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-03', 5, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 1, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-04', 6, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'N', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-05', 7, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'N', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-06', 1, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-07', 2, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-08', 3, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-09', 4, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-10', 5, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 2, 1, 'N', 'S', 'N', 1, 'N', '0.00', 'S', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-11', 6, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 1, 'N', 'N', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-12', 7, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 1, 'N', 'N', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-13', 1, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 1, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-14', 2, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 1, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-15', 3, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 1, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-16', 4, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-17', 5, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 3, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-18', 6, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'N', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-19', 7, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'N', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-20', 1, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-21', 2, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-22', 3, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-23', 4, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-24', 5, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 4, 2, 'N', 'S', 'N', 2, 'N', '0.00', 'S', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-25', 6, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'N', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-26', 7, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'N', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-27', 1, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'S', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-28', 2, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'S', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-29', 3, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'S', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-30', 4, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'S', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');
INSERT INTO calendar VALUES ('2003-01-31', 5, '0000-00-00', '0000-00-00', 0, 0, '0.00', '0.00', 5, 2, 'N', 'S', 'N', 3, 'N', '0.00', 'N', 'N', 'N', 'N');

CREATE TABLE fecoper (
fecope_fec date NOT NULL default '0000-00-00'
) TYPE=MyISAM COMMENT='Tabla de Fecha de Operacion';

#

Populate the tablefecoper into the database

#

INSERT INTO fecoper VALUES ('2003-01-03');

Any help would be very appreciated.

barand

Try it with single quotes round the date value

"select * from calendar where fec_cal > '$fope' "

hth