Updating Mysql table using array with PHP

[ancientmoon]

Please help.

 

I am using an array of one field of the table to scrap one website using Curl. The script has output to the browser all the results but I don't know how to update the table with all the data I got from the array.

 

Please show me how to update it. Here is the code I use. Thank you.

 

 

<?php

/*
* Constants
*/
define("API_KEY", "API");
define("MAX_RESULTS", 10);

// Connect to MySQL
$link = mysql_connect('localhost', 'USERNAME', 'PASSWORD');
echo "Connected to MySQL<br />";
if (!$link || !mysql_select_db('DATABASE', $link)) {
die('Unable to connect or select database!');
}

// Query the table and build an array of numbers
$vins = array();
$result = mysql_query('SELECT numbers FROM table WHERE `Success` = 0 AND `Failed` < 3 LIMIT '.MAX_RESULTS);
while($row = mysql_fetch_array($result)) {
$vins[] = $row[0];
}

// Clean up
mysql_free_result($vins);

// Build our post data structure
$post_data = array(
"api_key" => API_KEY,
"vins" => $vins
);

// Initialize our curl session
$ch = curl_init();

// Set the URL we're making the request to
curl_setopt($ch, CURLOPT_URL, "link");

// Tell the remote server that we're sending JSON encoded data
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json"));

// Tell curl that we want to capture the returned data from the service in $response
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// Set the number of POST "fields", which is 1 since we're sending a chunk of JSON
curl_setopt($ch, CURLOPT_POST, 1);

// Set the HTTP POST request body. This is our array encoded as JSON.
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($post_data));

// Make the request
echo "Sending request...\n";
$date = new DateTime();
echo $date->format('U = Y-m-d H:i:s') . "\n";
mysql_query("UPDATE table SET SentTimestamp = '2013-01-30 14:15:00' WHERE numbers = '$row ['numbers']' ");


$response = curl_exec($ch);
if (!$response) {
// Uh oh, we encounted a problem. Print the error out.
echo curl_error($ch);

} else {
// Yay! We got a response. Print it out
echo "Received response.\n";
mysql_query("UPDATE table SET `Returntimestamp` =  '2013-01-30 14:15:36'");
$date = new DateTime();
echo $date->format('U = Y-m-d H:i:s') . "\n";
// Now we can decode the response (which is JSON) into a PHP object which will be easier to work with.
$vins = json_decode($response);

if ($vins->success) {
// Print out recall data
foreach ($vins->result as $vin) {
$nRecalls = count($vin->recalls);
mysql_query("UPDATE table SET `Success` = 1 WHERE ?????");
echo "\nVIN: $vin->vin\n";
echo "Recalls: $nRecalls\n";
if ($nRecalls > 0) {
foreach ($vin->recalls as $recall) {
  mysql_query("UPDATE table SET `ID` = $recall->id, 'Recall Date` =  $recall->date WHERE ?????");
echo "  ID: $recall->id\n";
echo "  Date: $recall->date\n";
echo "  Description: $recall->description\n";
echo "  Letter: $recall->letter\n\n";
}
}
}
} else {
echo "Hmm looks like we hit trouble:\n  $response\n";
foreach ($vins->result as $vin) {
mysql_query("UPDATE table SET `Failed` = `Failed` + 1 ");
}
}
}

curl_close($ch);

Edited February 9, 2013 by fenway
code blocks added

[fenway]

I'm certain you want WHERE clauses on all of your UPDATE statements.

 

And I don't follow what doesn't work -- don't show me all your code, it doesn't help.