Jump to content

Insert Array values into mysql database


ludo1960

Recommended Posts

4 hours ago, Barand said:

Where does the array come from?

I'll ask again. The solution would differ depending on the array's origin. (For example, if it's from a query fetch() or posted form data). So what are you trying to accomplish here?

(If you won't answer my questions, why should I bother to answer yours?)

Link to comment
Share on other sites

The arrays origin is from a call to a JSON source,

$response = json_decode(file_get_contents($url), true);

the array is then created from the data received:

$myarray = array(
'County' => $response['state'],
'Country' => $response['country']
);
Link to comment
Share on other sites

Is this what you are looking for

$db->query("DROP TABLE ludo");
$db->query("CREATE TABLE ludo (
            id int auto_increment not null primary key,
            county varchar(20),
            country varchar(20)
            )");
            
$data = [ 'county' => 'Cheshire', 
          'country' => 'England'];

$fields = array_keys($data);
$placeholders = array_fill(0, count($data), '?');

$res = $db->prepare("INSERT INTO ludo (" . join(',', $fields) . ") VALUES (" . join(',', $placeholders) . ")");

$res->bind_param('ss', ...array_values($data));

$res->execute();

 

Link to comment
Share on other sites

You could always engage brain, leave out the "array_keys" bit and provide the column names/placeholders yourself EG

$data = [ 'county' => 'Cheshire', 
          'country' => 'England'];

$res = $db->prepare("INSERT INTO ludo (county, country) VALUES (?,?)");

$res->bind_param('ss', ...array_values($data));

$res->execute();

 

Link to comment
Share on other sites

Binding is optional with PDO or you can pass an array of values (as I did)

To get the most out of, set the right options when connecting. This my connection code (The constants and function definition are in an include file)

const HOST     = 'localhost';
const USERNAME = '????';
const PASSWORD = '????';
const DBNAME   = "????";

function pdoConnect() 
{
    $dsn = "mysql:dbname=".DBNAME."; host=".HOST."; charset=utf8";

    $db = new pdo($dsn, USERNAME, PASSWORD, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    return $db;
}

$db = pdoConnect();

[EDIT]

It also lets you used name placeholders which can be helpful if you have a large number in your query. So I could have used

$res = $db->prepare("INSERT INTO ludo (county, country) VALUES (:county, :country)");

$res->execute($data);

 

Link to comment
Share on other sites

That isn't what it means.

The PDO connection is and object. A result from a query is an object. A statement returned by preparing a query is an object. (This is also true for mysqli but, unlike mysqli, the PDO result and statement objects have identical methods. With mysqli you have two different sets of methods to learn).

As for firing objects at the database, there are object databases out there but MySQL isn't one of them.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.