0

I am attempting to get my Access database written in VBA to communicate with a MySQL database on my clients website through a set of PHP web services I wrote. I have managed to get the Access db to retrieve data from the MySQL db but can't get it to post anything. I have narrowed the problem down to the fact that my HTTP request isn't sending the arguments I assign it.

Here are some questions and sites I have looked at already. These were not helpful, because the majority weren't dealing with PHP and were instead looking directly at websites, or were dealing with GET rather than POST.

Http Post not posting data
excel vba http request download data from yahoo finance
VBA HTTP GET request - cookies with colons
Pass Parameters in VBA HTTP Post Request
Perform hidden http-request from vba
How can I send an HTTP POST request to a server from Excel using VBA?
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/
https://stackoverflow.com/questions/29190759/vba-oauth2-authentication-http-get-request
VBA ServerXMLHTTP https request with self signed certificate
How can I send an HTTP POST request to a server from Excel using VBA?
Sending http requests with VBA from Word Sending http requests with VBA from Word

My code for VBA is:

Dim strJSONEncodedJob As String
        strJSONEncodedJob = "[{""ExpenseID"":""" & astrExpenseIDs(intI) & "}]"
        URL = "I removed the URL when posting"
        objHTTP.Open "POST", URL, False
        objHTTP.setRequestHeader "Content-Type", "application/json"
        objHTTP.send (strJSONEncodedJob)
        strResponse = objHTTP.responseText
        MsgBox strResponse

And my PHP code is:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$json = file_get_contents('php://input');
$data = json_decode($json, true);

$stmt = $conn->prepare("DELETE FROM tblExpenses WHERE ExpenseID=?");
$txtExpenseID = $data['ExpenseID']; 
$stmt->bind_param("i", $txtExpenseID);
$stmt->execute();

echo '{"result" : "success"}';


$stmt->close();
$conn->close();

I get the success statement as a msgbox in VBA, as expected, however the record is not deleted from the MySQL db.

Does anyone have a solution?

Thanks in advance.

Update When I echo $json I get the JSON encoded string, which means the arguments ARE being passed. However, when I echo $data['ExpenseID'] I get a blank msgbox.

Community
  • 1
  • 1
Matt Kelly
  • 1,451
  • 2
  • 13
  • 30
  • If you echo `$json` and `$txtExpenseID` do you see what you expect? And I'm not a PHP person but wouldn't you populate the value of `$txtExpenseID` *before* you bind it as a parameter? – Tim Williams May 05 '15 at 06:47
  • Yes sorry that was a typo, I will amend that. When I echo $json, I get the json string, which I wasn't before, so that suggests that the arguments are being passed. However I still can't get it to execute that command (delete the records in MySQL). When I echo $data['ExpenseID'] I get nothing. – Matt Kelly May 05 '15 at 07:14
  • Your json-encoded object is an array containing a single object, so if you want to get at that object's properties you'd first have to index it from the array. Something like `$txtExpenseID = $data[0]['ExpenseID'];` (again, I'm not a PHP person so just guessing the syntax) – Tim Williams May 05 '15 at 15:55
  • Thank you! That indexing did it. This problem has been killing me and you fixed it. Thank you so much. How do I set your comment as the answer? – Matt Kelly May 07 '15 at 01:28

1 Answers1

0

Your json-encoded object is an array containing a single object, so if you want to get at that object's properties you'd first have to index it from the array. Something like 

$txtExpenseID = $data[0]['ExpenseID'];

(again, I'm not a PHP person so just guessing the syntax)

Tim Williams
  • 154,628
  • 8
  • 97
  • 125