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.

4
  • 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? Commented May 5, 2015 at 6: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. Commented May 5, 2015 at 7: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) Commented May 5, 2015 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? Commented May 7, 2015 at 1:28

1 Answer 1

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)

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.