updating mysql via values of associative array

Question

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

Md. Rakibul Hasan Khan · Accepted Answer · 2016-05-24 21:38:04Z

2

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.";
}

answered May 24, 2016 at 21:38

Md. Rakibul Hasan Khan

362 bronze badges

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

Comments

Pevara · Accepted Answer · 2016-05-24 21:21:58Z

1

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

answered May 24, 2016 at 21:21

Pevara

14.3k1 gold badge37 silver badges49 bronze badges

Comments

Milanzor · Accepted Answer · 2016-05-24 21:29:07Z

0

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);

answered May 24, 2016 at 21:29

Milanzor

1,93014 silver badges22 bronze badges

Collectives™ on Stack Overflow

updating mysql via values of associative array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related