insert associative array into mysql with php

Question

I got an associative array that looks like

$rates = array (
    'AUD' => '0.5',
    'BRL' => '1.2',
    'CAD' => '0.6',
    'CHF' => '0.4',
    'CZK' => '1.5',
 );

I want to put this array in mysql so that i can use the rates to make an currency converter. mine question is how do you do that i tried

$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';

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

$sql = 'insert into '.$table.'('.$keys.') values ('.$values.')';


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

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

}

else{
echo "Data inserted.";
}

the error i got is There was an error running the query [Unknown column 'AUD' in 'field list'] for the table used

// sql to create table
$sql = "CREATE TABLE eurotoforgein (
COUNTRY VARCHAR(6),
RATES FLOAT(10)

)";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

any help is welcome thanks

I'll give you a hint. INSERT INTO eurotoforgein (COUNTRY, RATES) VALUES ('AUD', 0.5), not INSERT INTO eurotoforgein (AUD) VALUES (0.5) — Jonnix
– Jonnix, Commented May 24, 2016 at 15:45
SQL requires string literals to be quoted (same as almost every single programming language that exists).... better yet, enter the 21st centurey and use prepared statements with bind variables using MySQLi or PDO — Mark Baker
– Mark Baker, Commented May 24, 2016 at 15:46

Richard · Accepted Answer · 2016-05-24 16:02:17Z

2

you can generate an insert for multiple values like this

$pairs = [];
foreach($rates as $key => $value)
{
    $pairs[] = "('$key', $value)";
}
$pairs_str = implode(',', $pairs);

$sql = 'insert into '.$table.' (COUNTRY, RATES) values ' . $pairs_str;

answered May 24, 2016 at 16:02

Richard

1,0557 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

insert associative array into mysql with php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest