www.webdeveloper.com
Results 1 to 4 of 4

Thread: Insert Multidimensional array into Mysql Database

  1. #1
    Join Date
    Feb 2014
    Posts
    2

    Lightbulb Insert Multidimensional array into Mysql Database

    Hi Guys,


    I'm having difficulties inserting Multidimensional php array into mysql database. Could you please see what am I doing wrong?

    PHP Code:
    $array = array(
         array(
            
    'id' => 1,
            
    'firstName' => 'Paul',
            
    'surname' => 'Crowe',
            
    'age' => 28,
            
    'gender' => 'male',
            
    'connections' => array(2),
            
    'cities' => array('Dublin' => 80'New York' => 100'Paris' => 95'Madrid' => 100'London' => 80'Barcelona' => 100'Moscow' => 20)
        ),
         array(
            
    'id' => 2,
            
    'firstName' => 'Rob',
            
    'surname' => 'Fitz',
            
    'age' => 23,
            
    'gender' => 'male',
            
    'connections' => array(13),
            
    'cities' => array('Dublin' => 40'New York' => 100'Paris' => 65'Madrid' => 90)
        ),
         array(
            
    'id' => 3,
            
    'firstName' => 'Ben',
            
    'surname' => "O'Carolan",
            
    'age' => null,
            
    'gender' => 'male',
            
    'connections' => array(2457),
            
    'cities' => array('Paris' => 90'Madrid' => 40'London' => 85'Barcelona' => 90'Moscow' => 80)
        ),
         array(
            
    'id' => 4,
            
    'firstName' => 'Victor',
            
    'surname' => '',
            
    'age' => 28,
            
    'gender' => 'male',
            
    'connections' => array(3),
            
    'cities' => array('Paris' => 80'Madrid' => 80'London' => 80'Barcelona' => 80'Moscow' => 40)
        ),
         array(
            
    'id' => 5,
            
    'firstName' => 'Peter',
            
    'surname' => 'Mac',
            
    'age' => 29,
            
    'gender' => 'male',
            
    'connections' => array(3611107),
            
    'cities' => array('Dublin' => 60'New York' => 100'Paris' => 75)
        ),
    );

    foreach(
    $array as $data){

        
    $sql "INSERT INTO people (firstName, surname, age, gender, connections, cities)
        VALUES (
    $data[firstName]$datas[surname]$data[age]$data[gender]$data[connections]$data[cities])";



  2. #2
    Join Date
    Feb 2014
    Posts
    2
    Quote Originally Posted by pixelweblab View Post
    Hi Guys,


    I'm having difficulties inserting Multidimensional php array into mysql database. Could you please see what am I doing wrong?

    PHP Code:
    $array = array(
         array(
            
    'id' => 1,
            
    'firstName' => 'Paul',
            
    'surname' => 'Crowe',
            
    'age' => 28,
            
    'gender' => 'male',
            
    'connections' => array(2),
            
    'cities' => array('Dublin' => 80'New York' => 100'Paris' => 95'Madrid' => 100'London' => 80'Barcelona' => 100'Moscow' => 20)
        ),
         array(
            
    'id' => 2,
            
    'firstName' => 'Rob',
            
    'surname' => 'Fitz',
            
    'age' => 23,
            
    'gender' => 'male',
            
    'connections' => array(13),
            
    'cities' => array('Dublin' => 40'New York' => 100'Paris' => 65'Madrid' => 90)
        ),
         array(
            
    'id' => 3,
            
    'firstName' => 'Ben',
            
    'surname' => "O'Carolan",
            
    'age' => null,
            
    'gender' => 'male',
            
    'connections' => array(2457),
            
    'cities' => array('Paris' => 90'Madrid' => 40'London' => 85'Barcelona' => 90'Moscow' => 80)
        ),
         array(
            
    'id' => 4,
            
    'firstName' => 'Victor',
            
    'surname' => '',
            
    'age' => 28,
            
    'gender' => 'male',
            
    'connections' => array(3),
            
    'cities' => array('Paris' => 80'Madrid' => 80'London' => 80'Barcelona' => 80'Moscow' => 40)
        ),
         array(
            
    'id' => 5,
            
    'firstName' => 'Peter',
            
    'surname' => 'Mac',
            
    'age' => 29,
            
    'gender' => 'male',
            
    'connections' => array(3611107),
            
    'cities' => array('Dublin' => 60'New York' => 100'Paris' => 75)
        ),
    );

    foreach(
    $array as $data){

        
    $sql "INSERT INTO people (firstName, surname, age, gender, connections, cities)
        VALUES (
    $data[firstName]$datas[surname]$data[age]$data[gender]$data[connections]$data[cities])";


    I probably should mention aswell that in the database there are 2 more tables. Cities and connections.

    I'm really struggling with this can't figure out a way.

  3. #3
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    663
    Can you tell us WHAT the difficulty is?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,252
    For one thing, you'll need quotes around each non-numeric value in the VALUES() list, and you'll want to sanitize them against SQL injection errors/attacks.

    Better yet, use PDO or MySQLi, and make use of prepared statements and bound parameters, which are especially useful for repeating tasks like this. Assuming PDO, it might be something like:
    PHP Code:

    $sql 
    "
        INSERT INTO people (firstName, surname, age, gender, connections, cities)
        VALUES (':firstName', ':surname', ':age', ':gender', ':connections', ':cities')
    "
    ;
    $stmt $pdo->prepare($sql);
    if(
    $stmt == false) {
        throw new 
    Exception(print_r($pdo->errorInfo,1).PHP_EOL.$sql);
    }
    foreach(
    $array as $data) {
        
    $values = array(
            
    ':firstName'   => $data['firstName'],
            
    ':surname'     => $data['surname'],
            
    ':age'         => $data['age'],
            
    ':gender'      => $data['gender'],
            
    ':connections' => $data['connections'],
            
    ':cities'      => $data['cities']
        );
        if(
    $stmt->execute($values) == false) {
            throw new 
    Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
        }

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles