Jump to content
ludo1960

Insert Array values into mysql database

Recommended Posts

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? 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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?)

Share this post


Link to post
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']
);

Share this post


Link to post
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();

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Silly me!.

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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();

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

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

line then? Thanks again.

Edited by ludo1960

Share this post


Link to post
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);

 

Edited by Barand

Share this post


Link to post
Share on other sites

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 by ludo1960

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.