CODEWITHSUNDEEP
phpmysql
Dan
Dan

Reputation: 31

updating mysql via values of associative array

I got an database that looks like

COUNTRY RATES   
AUD     0.5
BRL     1.2
CAD     1.6
CHF     1.4
CZK     2.5

now i want to update the rates using this array

$rates = array (
    'AUD' => '0.5',
    'BRL' => '6.2',
    'CAD' => '2.6',
    'CHF' => '7.4',
    'CZK' => '1.5',
 );

i tried using this code

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "eurotoforgein";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$table = 'rates';


$values = "'" . implode("','", array_values($rates)) . "'";

$sql = "UPDATE `eurotoforgein` SET 'RATES' WHERE rates =  $values";



if(!$result = $conn->query($sql)){ 

die('There was an error running the query [' . $conn->error . ']'); 

}

else{
echo "Data Updated.";
}

but i get There was an error running the query [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''RATES' WHERE rates = '0.5','1.2','1.6','1.4','2.5'' at line 1]

any help is welcome

Upvotes: 0

Views: 585

Answers (3)

Md. Rakibul Hasan Khan
Md. Rakibul Hasan Khan

Reputation: 36

It is my solution:

$sql = '';
$rates = array (
    'AUD' => '0.5',
    'BRL' => '6.2',
    'CAD' => '2.6',
    'CHF' => '7.4',
    'CZK' => '1.5',
 );
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "eurotoforgein";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

foreach($rates as $iso=>$rate){
$sql .= "UPDATE `eurotoforgein` SET `RATES`='{$rate}' WHERE COUNTRY = '{$iso}'";
}

if(!$result = $conn->query($sql)){ 

die('There was an error running the query [' . $conn->error . ']'); 

}else{
echo "Data Updated.";
}

Upvotes: 2

Milanzor
Milanzor

Reputation: 1930

Your SQL queries comes out like this:

UPDATE `eurotoforgein` SET 'RATES' WHERE rates = '0.5','6.2','2.6','7.4','1.5'

That's not valid SQL.

Build your query this way:

$sql = '';
foreach($rates as $country => $value){
    $sql .= "UPDATE `eurotoforgein` SET `rates` = '$value' WHERE `country` =  '$country';";
}

This creates a string containing multiple UPDATE queries and can be executed in

1 go, $conn->query($sql);

Upvotes: 0

Pevara
Pevara

Reputation: 14310

You'll have to update those values one by one. This should help you on the way:

foreach ($rates as $country => $rate) {
  $sql = "update rates set RATES = $rate where COUNTRY = $country";
  // execute
}

Have a look at the UPDATE syntax if you want to learn more: http://dev.mysql.com/doc/refman/5.7/en/update.html

Upvotes: 1

Related Questions