How to create a function in PHP that can write an associative array to mysql

Hello,

I am looking at writing a function to write to mysql that will take in 2 variables a table name and an associative array, I would like to insert the values of the associative array to the provided table name.


I was thinking of iterating over the array in a for each loop and building the mysql query, however I'm not sure how to execute this once it has been built.

Is this possible and is there a better way of doing this?

Thanks
EverlearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Your design pattern makes a lot of sense to me.  I'll show you a code example in a moment.  It's pretty easy!
Ray PaseurCommented:
Here is what I might do.  I have not tested this very thoroughly and there is little error or exception handling, but it should show the basic moving parts.

Line 13 - we create a class with two methods, a constructor and an inserter.
Line 64 - we create the MySQLI connection object outside of the class so we can inject it.  This practice makes it possible to change to another database for testing.
Line 86 - we create a temporary table for testing.
Line 115 - our test data in an associative array
Lind 119 - we create an instance of the table loader class, and call the inserter method.

Some assumptions...
1. The table already exists (exception will be thrown otherwise)
2. The names of the array keys must match the table column names exactly, case-sensitive.

This is very much how an "Active Record" design pattern works.  Please read it over and post back if you still have any questions.
http://iconoun.com/demo/temp_everlearner.php

<?php // demo/temp_everlearner.php

/**
 * See: http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28646082.html
 */

// RAISE THE ERROR REPORTING LEVEL TO THE HIGHEST POSSIBLE SETTING
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


Class TableLoader
{
    public function __construct(mysqli $db, $table)
    {
        $this->db    = $db;
        $this->table = $table;
    }

    public function insert(array $arr)
    {
        $column_names = [];
        $column_values = [];

        // ESCAPE THE VARIABLES
        foreach ($arr as $key => $val)
        {
            $column_names[]  = $key;
            $column_values[] = $this->db->real_escape_string($val);
        }

        // PREPARE THE INSERT QUERY
        $sql
        = "INSERT INTO $this->table ("
        . implode(',', $column_names)
        . ') VALUES ('
        . "'"
        . implode("','", $column_values)
        . "')"
        ;

        // SHOW THE QUERY TO SEE IF IT LOOKS RIGHT
        echo PHP_EOL . $sql;

        // TRY THE QUERY
        try
        {
            $res = $this->db->query($sql);
        }
        catch (mysqli_sql_exception $e)
        {

            // HANDLE QUERY ERROR HERE
            throw $e;
        }

        // RETURN THE ID OF THE INSERTED ROW
        return $this->db->insert_id;
    }
}


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
, xwhen TIMESTAMP   NOT NULL
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATE AN ARRAY OF FIELDS TO USE FOR TEST DATA
$testdata = array( "fname" => "Bill", "lname" => "O'Reilly" );


// CREATE OUR NEW TABLE LOADER OBJECT AND INSERT DATA INTO THE TABLE
$db_loader = new TableLoader($mysqli, 'my_table');
$id = $db_loader->insert($testdata);
var_dump($id);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EverlearnerAuthor Commented:
Hi Ray!

Thanks for your answer, a lot more than I was expecting.

While researching I also came across prepared statements, would that be a useful thing in this case? If not what situations would you use them in?
Ray PaseurCommented:
Prepared statements are useful when you're going to use the same query (but not the same data) repeatedly.  They work hand-in-hand with "bound parameters," a concept that allows PHP and MySQL to cooperate by letting the MySQL engine have direct automatic access variables in your PHP scripts.  This has the potential to improve performance* and decrease the risk of unwanted injections into your database, since the programmer is not responsible for sanitizing and escaping the external data.  With prepared statements, the query string "program" and the data are sent separately - you do not modify the query string in the PHP program at all.

In this case, they would do no harm and might be helpful.  Some comparative database work is shown in this article:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

* For real performance gains, use appropriate indexes.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.