Insert Array values into mysql database

[ludo1960]

Hi folks,

If I have an array  where the database table columns match the array keys, how can I insert just the array values into the mysql database using a mysqli connection? 

[Barand]

Where does the array come from?

[ludo1960]

$myarray = array(

 

'ID' => 1,

'ID2 => 2,

'ID3' => 3

);

[benanamen]

When you when you start using incrementing numbers for your column names  it is a big red flag to a design problem. Take a minute and learn about database normalization.

[ludo1960]

Ok how about:

$myarray (

'state' => Texas,

'country => USA

);

[Barand]

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

[ludo1960]

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']

);

[Barand]

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

 

[ludo1960]

Not quite, your code uses the array keys and the array values, I just want to pass the array values to the mysql table.

[Barand]

Silly me!.

When you stated that the keys were the same names as the columns I thought there was some relevance to that.

[ludo1960]

lol, the keys are irrelevant, just need to insert the array values.

[Barand]

https://dev.mysql.com/doc/refman/5.7/en/insert.html

[ludo1960]

Aye very good, anybody else got an answer to just insert array values without using array keys? 

[Barand]

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

 

[ludo1960]

Yes that makes sense, was wondering what the 

bind_param('ss',

meant, seems https://www.w3schools.com/php/php_mysql_prepared_statements.asp explains it rather well. Nice answer Barand, thanks!!

[Barand]

PDO makes it even easier. I would strongly advise you switch from mysqli.

The PDO solution is

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

$res->execute(array_values($data));

 

[ludo1960]

PDO it is then! Does that mean I don't need the: 

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

line then? Thanks again.

Edited January 17, 2019 by ludo1960

[Barand]

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

 

Edited January 17, 2019 by Barand

[ludo1960]

Perfect!  As I understand it PDO stands for PHP Data Object, so instead of firing an array at the database, is it not better to fire an object at the database? 

Edited January 17, 2019 by ludo1960

[Barand]

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.

[ludo1960]

All working nicely, thanks for taking the time to walk me through it, Cheers!!!