CODEWITHSUNDEEP
phpmultidimensional-arraymysqli
jimlongo
jimlongo

Reputation: 385

multidimensional array into db with mysqli

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.

Upvotes: 0

Views: 1305

Answers (1)

Sverri M. Olsen
Sverri M. Olsen

Reputation: 13263

There are a few things wrong with your code.

  1. You are not using the array syntax correctly
  2. In your query the non-number values need to be quoted
  3. 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!");
}

Upvotes: 2

Related Questions