Visual foxpro DBF update using php

Question

I am using php to update some tables under VFP 9.0 using ADO COM.

I am able to select and update the DBF until i specify any Where clause.
The moment i add a where clause to the query, it simply returns or updates 0 rows.

$conn = new COM("ADODB.Connection");
$conn->Open('Provider=VFPOLEDB.1;Data Source="C:\\testDB.dbc";'); 

$query = "UPDATE TABLE1 set COL1 = \"AA\", COL2 = \"Updated value\" ";
$conn->Execute($query);

$query = "SELECT * FROM TABLE1 ";
$rs = $conn->Execute($query) or die("Error in query: $query. " . $conn->ErrorMsg());
while (!$rs->EOF) {
    echo " Got COL1: " . $rs->Fields("COL1") . " :: COL2: " . $rs->Fields("COL2") . " id: " . $rs->Fields("ID") . "\n";
    $rs->MoveNext();
}

Result:

 Got COL1: AA :: COL2: Updated value                  id: 0
 Got COL1: AA :: COL2: Updated value                  id: 1
 Got COL1: AA :: COL2: Updated value                  id: 2

Code 2: With Where clause

$query = "UPDATE TABLE1 set COL1 = \"BB\", COL2 = \"NEW2\" WHERE ID = 1";
$conn->Execute($query);
$query = "SELECT * FROM TABLE1 ";
$rs = $conn->Execute($query) or die("Error in query: $query. " . $conn->ErrorMsg());
while (!$rs->EOF) {
    echo " Got COL1: " . $rs->Fields("COL1") . " :: COL2: " . $rs->Fields("COL2") . " id: " . $rs->Fields("ID") . "\n";
    $rs->MoveNext();
}

Result:

 Got COL1: AA :: COL2: Updated value                  id: 0
 Got COL1: AA :: COL2: Updated value                  id: 1
 Got COL1: AA :: COL2: Updated value                  id: 2

The ID column is the key in the above table.
I am relatively new to VFP. I am not sure if this a Visual Foxpro setting or something else which prevents the updates or select if done selectively.

Answer 1

The standard SQL-style statements you are using are easily compatible with VFP. As simple as your sample is, I would try one slight alteration first. Instead of using escape ", just use single quotes around your sample such as

UPDATE TABLE1 set COL1 = 'AA', COL2 = 'Updated value' where ID = 1

See if that does anything. Once that is working, I would then go to parameterized queries for safety... especially when web-based. VFP doesn't work with "named place-holders" like some other databased, but ordinal based place-holders by using a '?' where your "value" should be applied... such as

UPDATE TABLE1 set col1 = ?, col2 = ? where id = ?

Then, when adding the parameters, add based on the same sequence as the ? place-holders

YourAdo.Parameters.Add( "ForColumn1", variableForColumn1 );
YourAdo.Parameters.Add( "ForColumn2", variableForColumn2 );
YourAdo.Parameters.Add( "IDKeyColumn", variableForID );

Then, you should be good to go.

Sorry I can't help specifically with PHP as thats not one I'm directly familiar with, but should help guide you on it.