Update mysql field row with curl http response code

Update mysql field row curl http response code

[razn8]

When I run the mysql select and curl post below I need to retrieve the new http response and update that in the mysql row, so if repost sends back a 200, it won't send the data again the next time the script runs.

I'm thinking its something like this:

-------

curl variables......

//execute post
$result = curl_exec($ch);

// Get values from response header
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);

//close connection
curl_close($ch);

// Update httpCode in mysql
mysql_query("UPDATE tablename ( `httpCode`) VALUES ( '$httpCode' )");

-------
What do I use to get the $htttpCode value to UPDATE into the same row that I just queried and posted?

------------------------

Here is my code for the file:

// Connects to your Database
mysql_connect("localhost", "user", "password") or die(mysql_error());
// select database.
mysql_select_db("dbname") or die(mysql_error());

// Select data from mysql
$rows = mysql_query(
"SELECT
VendorID,
Type,
FirstName,
LastName
FROM tablename
WHERE
httpCode <> 200"
);
while($row = mysql_fetch_array($rows)) {

// Fill CURL POST fields with this information,

//set POST variables
$url = 'http://domain.com/';
$fields = array(
'VendorID' => urlencode($row['VendorID']),
'Type' => urlencode($row['Type']),
'FirstName' => urlencode($row['FirstName']),
'LastName' => urlencode($row['LastName'])

);

//format the data for the url POST
foreach($fields as $key=>$value) { $fields_string .= $key.'='.$value.'&'; }
rtrim($fields_string,'&');

//open connection
$ch = curl_init();

//set the url, number of POST vars, POST data
curl_setopt($ch,CURLOPT_URL,$url);
curl_setopt($ch,CURLOPT_POST,count($fields));
curl_setopt($ch,CURLOPT_POSTFIELDS,$fields_string);
curl_setopt($ch,CURLOPT_CONNECTTIMEOUT, 30);
curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)");
curl_setopt($ch,CURLOPT_SSL_VERIFYPEER, false);

//execute post
$result = curl_exec($ch);

// Get values from response header
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);

//close connection
curl_close($ch);

} // return to loop to attempt a POST on the next unsuccessful row ...

[enigma1]

you could use some identifier so you will know it targets the same row. This is table dependent, so from your code I see a VendorID if that is the identifier to rely on, then it needs to propagate into the httpCode table. So the update query will look like:

"UPDATE tablename set httpCode = " . some_filter_string($httpCode) . " where VendorID='" . (int)$vendor_id . "'";

So the next time you query the httpCode for the VendorID it will be the most recent response code which you could examine for 200.

[razn8]

Thanks for the reply! I resolved this with the following:

I just added this update statement after closing the curl:

// Update httpCode in mysql
$row = mysql_query("UPDATE tablename SET httpCode='$httpCode'" )
or die(mysql_error());

I have a test page that receives the curl reposts and inserts into a my test table, and the client has their own url that inserts into their table. All the failed records insert to my test url row by row as expected, but the client's url appears to be receiving all failed records in a single row, opposed to separate rows. I actually have an auto increment id named 'id'. Would not using the "where id='" .(int)$id."'" field as you recommend cause the problem my client is experiencing.