Insert Varible Date (HTML/PHP) In DBF File 2

[Fathir]

I made an HTML and PHP application using a DBF database via an ODBC connection, as follows:

FOXPRO DATABASE (MASTER.DBF) :
Employee Character 50
Datebirth Date 8

CODE HTML :
Date Of Birth : <input TYPE="DATE" NAME="VARDATE">
I fill data/variable => 12-08-1970

CODE PHP :
//convert variable date//
$PHPDATE = $_POST['VARDATE']; => result : 1970-08-12
$FOXDATE = DATE('d-m-Y',strtotime($PHPDATE)); => result : 12-08-1970

//insert variable date//
$CON=ODBC_CONNECT('DATABASE','','');
$SQL="INSERT INTO MASTER (DATEBIRTH) VALUES ('$FOXDATE')";
$SQL_RESULT=ODBC_PREPARE($CON,$SQL);
ODBC_EXECUTE($SQL_RESULT);
ODBC_CLOSE($CON);

There is an error message when saving the PHP variable "$FOXDATE" into field "DATEBIRTH" of MASTER.DBF :
Warning: odbc_execute() [function.odbc-execute]: SQL error: [Microsoft][ODBC Visual FoxPro Driver]Data type mismatch., SQL state 22005 in SQLExecute in C:\xampp\htdocs\SAVE.PHP on line 145

Please explain and help with the error message above

Thank you for the help

 

[Olaf Doschke]

PHP DATE() function creates a string, your DBF field is a Date. In VFP SQL unlike other SQL dialects there is no implicit string to date type conversion.

One way to make the conversion would be CTOD:

$SQL="INSERT INTO MASTER (DATEBIRTH) VALUES (CTOD('$FOXDATE'))";

CTOD depends on VFP settings both in the day/month or month/day order and the separator character, so better is to use:

$FOXDATE = DATE('Y-m-d',strtotime($PHPDATE));
...
$SQL="INSERT INTO MASTER (DATEBIRTH) VALUES ({^$FOXDATE})";

The third option would be using a parameterized query and a date parameter.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Fathir]

Thank you so much for your help Bro Olaf,
It solved with option :

CODE :
$FOXDATE = DATE('Y-m-d',strtotime($PHPDATE));
...
$SQL="INSERT INTO MASTER (DATEBIRTH) VALUES ({^$FOXDATE})";

Regards,