multidimensional array into db with mysqli

Question

I have a lengthy array $prices that is structured like this

Array
(
    [Fund] => BGEF
    [Class] => G
    [Currency] => CAD
    [NAV] => 8.6442
    [NavChange] => 0.0431
    [PriceDate] => 2013-05-01
)
Array
(
    [Fund] => BGOF
    [Class] => G
    [Currency] => EUR
    [NAV] => 12.1503
    [NavChange] => 0.0226
    [PriceDate] => 2013-05-01
)
Array
(
    [Fund] => BIEF
    [Class] => G
    [Currency] => USD
    [NAV] => 9.6914
    [NavChange] => 0.0635
    [PriceDate] => 2013-05-01
)

I want to put it into a mysql table that has been created with the corresponding rows. fund_id,class,currency,nav,nav_change,price_date

here is my latest attempt to insert the rows into multiple db rows

    $mysqli = new mysqli( "localhost", "user", "pw","db" );
    if( $mysqli->connect_errno ){
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    $mysqli->select_db("db");
                foreach($prices as $rows){                      
                        $sql = "  INSERT INTO price_data(  price_date,fund_id,currency_id,class_id,nav,nav_change  ) 
                            VALUES (  $rows[PriceDate] , $rows[Fund] , $rows[currency] , $rows[Class] , $rows[NAV] , $rows[NavChange]  )   " ;

    $stmt = $mysqli->prepare($sql);
    $stmt->execute();

     }

Currently this is returning a Fatal error: Call to a member function execute() on a non-object. I'm not clear on a few things (obviously) with the mysqli approach, like where the loop goes and if this is the right kind of loop to access the data.

Any help would be great, thanks.

An example here : stackoverflow.com/questions/1488033/php-mysqli-multiple-inserts — nicolas
– nicolas, Commented May 2, 2013 at 21:52
Your query has problems. You could just put quotes around the variables in the VALUES part, but this possibly leaves you open to SQL injection as you are not doing anything to prevent that. What you should do is switch to binding parameters. php.net/manual/en/mysqli-stmt.bind-param.php — Jonathan Kuhn
– Jonathan Kuhn, Commented May 2, 2013 at 21:53

Sverri M. Olsen · Accepted Answer · 2013-05-02 22:28:03Z

2

There are a few things wrong with your code.

You are not using the array syntax correctly
In your query the non-number values need to be quoted
And you are querying the database several times, which is not necessary

I do not have your database at my disposal so the following code has not been tested in any way. Read the comments and try to understand the code:

// Connect to the database as usual
$mysqli = new MySQLi("localhost", "user", "pw", "db");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}";
}
// The first part of the SQL query
$query = "INSERT INTO `price_data` (`price_date`, `fund_id`, `currency_id`, `class_id`, `nav`, `nav_change`) VALUES";

// This is the format of a VALUES tuple; it is used
// below in sprintf()
$format = " ('%s', '%s', '%s', '%s', %f, %f),";

// Go over each array item and append it to the SQL query
foreach($prices as $price) {
    $query .= sprintf(
        $format,
        $mysqli->escape_string($price['PriceDate']),
        $mysqli->escape_string($price['Fund']),
        $mysqli->escape_string($price['Currency']),
        $mysqli->escape_string($price['Class']),
        $mysqli->escape_string($price['NAV']),
        $mysqli->escape_string($price['NavChange'])
    );
}
// The last VALUES tuple has a trailing comma which will cause
// problems, so let us remove it
$query = rtrim($query, ',');

// MySQLi::query returns boolean for INSERT
$result = $mysqli->query($query);

// Find out what happened
if ($result == false) {
    die("The query did not work: {$mysqli->error}");
} else {
    die("The query was a success!");
}

answered May 2, 2013 at 22:28

Sverri M. Olsen

13.2k3 gold badges38 silver badges54 bronze badges

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

3 Comments

jimlongo Over a year ago

That works! thanks Sverri. i don't understand the $format line, could you point me in the direction of some reading - what is that for?

Sverri M. Olsen Over a year ago

It is a format that the printf family of functions uses. You can read about it at sprintf() and on Wikipedia

jimlongo Over a year ago

Thanks again. Now to complicate things a bit more. I'd like the query to be an INSERT . . . ON DUPLICATE KEY UPDATE. There is a unique constraint on the first 4 columns, so if either of the last 2 changes it should be an update. How can i add that to the query in the following $query = "INSERT INTO wp_bh_price_data (price_date, fund_id, manager_id , currency_id,class_id, nav, nav_change) VALUES ON DUPLICATE KEY UPDATE nav = ???, nav_change = ???; "; – jim 3 mins ago edit

Collectives™ on Stack Overflow

multidimensional array into db with mysqli

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related