Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post

    2Dim Array inserted into MySQL table?

    Hello,

    I have a 2 dimensional php array I need to insert into a MySQL table. I can currently get the info I need out of the array using:

    PHP Code:
    foreach ($finalarr as $firstDimKey => $firstDimVal) { 
        foreach (
    $firstDimVal as $secondDimKey => $secondDimVal) {
        echo 
    "KEY: $secondDimKey -- VALUE $secondDimVal<br>";

    or

    PHP Code:
    foreach ($finalarr as $firstDimKey => $firstDimVal) { 
        echo 
    "Member ID for key $firstDimKey is {$firstDimVal['memberID']}<br>"

    This is my first attempt at using MySQL so any help is appreciated.

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    what is the question/problem?

    best regards

  • #3
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    The problem is I have no idea how to get the data contained in the array into a MySQL table.

    I can connect to the DB. I just have no clue how to write the query to get the data from the array to the database table.

  • #4
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,301
    Thanks
    4
    Thanked 204 Times in 201 Posts
    Serialize the array so it becomes a simple variable that you may store then use unserialize to restore the array to its original state after you retrieve it from the database.
    Dave .... HostMonster for all of your hosting needs

  • #5
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    So there is no way then to extract the individual vlaues for the keys and populate the table with them?

  • #6
    Regular Coder
    Join Date
    Jun 2007
    Location
    N. Ireland
    Posts
    351
    Thanks
    16
    Thanked 4 Times in 4 Posts
    Are you wanting to run through the 2-d array and insert into your db?

    Like this:

    PHP Code:
    Array
    (
        [
    22] => Array
            (
                [
    description] => Black Bag
                
    [image] => bag_black.jpg
                
    [sizes] => Array
                    (
                        [
    one_size] => 0
                    
    )

            )

        [
    23] => Array
            (
                [
    description] => Green Bag
                
    [image] => bag_green.jpg
                
    [sizes] => Array
                    (
                        [
    one_size] => 0
                    
    )

            )

    You can run through 2 for loops to gather up the data and write an insert statement for each:

    PHP Code:
    foreach($a2D as $key=>$value){
    foreach (
    $value as $key2=>$value2){
    $sql "INSERT INTO db (...) VALUES ('{$VALUE2['...']}')";
    }

    Does this help?

    Don't forget to use the '{' and '}' separators around each value that you are inserting as SQL will not be able to parse the information. The curly brackets help to define the values that should be entered.

    D.
    Daemonkin.
    If this was helpful, please add to my reputation
    Thousand Sons - Freelance Web Developer - ninetyonedegrees.com

  • Users who have thanked daemonkin for this post:

    Hayyel (03-09-2009)

  • #7
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    So I have:

    PHP Code:
    include 'dbconnect.php';
    include 
    'opendb.php';
    $finalarr unserialize(file_get_contents("../../subpages/uploads/guild.txt"));
    foreach (
    $finalarr as $firstDimKey => $firstDimVal) { 
        foreach (
    $firstDimVal as $secondDimKey => $secondDimVal) { 

    $sql "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,rank,0) VALUES ('{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}')";
    }  

    What am I missing/doing wrong?
    Last edited by Hayyel; 03-09-2009 at 02:04 PM.

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Hayyel View Post
    So I have:

    PHP Code:
    include 'dbconnect.php';
    include 
    'opendb.php';
    $finalarr unserialize(file_get_contents("../../subpages/uploads/guild.txt"));
    foreach (
    $finalarr as $firstDimKey => $firstDimVal) { 
        foreach (
    $firstDimVal as $secondDimKey => $secondDimVal) { 

    $sql "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,rank,0) VALUES ('{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}','{$secondDimVal}')";
    }  

    What am I missing/doing wrong?
    you have a 0 after rank in the fields name part of the query and I'm not sure that the way you build the values part give what you expect.
    try to outut the query to see what you have like this:
    PHP Code:
    echo '<pre>'.$sql.'</pre>'
    best regards

  • Users who have thanked oesxyl for this post:

    Hayyel (03-09-2009)

  • #9
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    Yep no where close to the results I expected.

    While I see what it is doing I am failing to understand how to get it to iterate through the Field Names and Values. Currently it just repeats the same one over and over.

    Seems like it needs a counter or something.

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Hayyel View Post
    Yep no where close to the results I expected.

    While I see what it is doing I am failing to understand how to get it to iterate through the Field Names and Values. Currently it just repeats the same one over and over.

    Seems like it needs a counter or something.
    you can build the query form your array using join or a loop with foreach or for.
    something like that:
    PHP Code:
    $sql "insert into mytable (fields list) values ";
    $rows = array();
    foreach(
    $myarray as $myrowarray){
       
    $row = array();
       foreach(
    $myrowarray as $filedname => $value){
          
    // do some checking here to be sure that fields are in same order you
          // and are same as in sql
          
    $row[] = $value;
       }
       
    $sqlfrag '('join(',',$row) . ')';
    }
    $sql .= join(',',$sqlfrag); 
    I didn't tested, could be wrong, it's only to have a idea how to do.

    best regards

  • #11
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    So it all finally looks good using this code:

    PHP Code:
    include 'dbconnect.php';
    include 
    'opendb.php';
    foreach(
    $finalarr as $firstDimKey => $firstDimValue){
       foreach(
    $firstDimValue as $secondDimKey => $secondDimValue){ 
         
    $sql "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,rank,0) VALUES ('{$secondDimValue}')"
       } 
    }
    $result mysql_query($sql) or die(mysql_error()); // execute the query 
    Only issue is now I receive a error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES ('')' at line 1

    It is caused by the 0 in the fields list.

    If I remove it I get an error:

    Column count doesn't match value count at row 1

    I have tried removing the field in the table and query to no avail. It is actually in the array like that. How could I get rid of it?\

    PHP Code:
    Array
    (
        [
    22] => Array
            (
                [
    description] => Black Bag
                
    [image] => bag_black.jpg
                
    [sizes] => Array
                [
    0] =>
            )

        [
    23] => Array
            (
                [
    description] => Green Bag
                
    [image] => bag_green.jpg
                
    [sizes] => Array
                [
    0] =>
            )


  • #12
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    the number of values between '(' and ')' must be same with the number of fields you want to insert. Use again the part with echo and pre to see what's wrong.

    best regards

  • #13
    New Coder
    Join Date
    Jan 2009
    Posts
    91
    Thanks
    17
    Thanked 1 Time in 1 Post
    I had the code totally wrong. I was misinterpreting what the sql echo was telling me. I had it set to put one value in each row instead of all 19 values.

    My new code is as follows and works perfectly. Is there any optimization I should look into?

    PHP Code:
        include 'dbconnect.php';
        include 
    'opendb.php';
    foreach(
    $finalarr as $firstDimKey => $firstDimValue){
        
          
    $sql "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,rank,unknown) VALUES ('{$firstDimValue['note']}','{$firstDimValue['careerID']}','{$firstDimValue['careerString']}','{$firstDimValue['rankInAlliance']}','{$firstDimValue['lastLogin']}','{$firstDimValue['bearerStatus']}','{$firstDimValue['LastLoginNumberHour24']}','{$firstDimValue['zoneID']}','{$firstDimValue['lastLoginNumberDay']}','{$firstDimValue['statusNumber']}','{$firstDimValue['lastLoginnumberMonth']}','{$firstDimValue['lastNumberLoginYear']}','{$firstDimValue['memberID']}','{$firstDimValue['onote']}','{$firstDimValue['founder']}','{$firstDimValue['name']}','{$firstDimValue['titleString']}','{$firstDimValue['rank']}','{$firstDimValue['unknown']}')";
          echo 
    '<pre>'.$sql.'</pre>';

    $result mysql_query($sql) or die(mysql_error()); // execute the query

    }
    echo 
    "SQL update successful!"
    THANK YOU all for your help!
    Last edited by Hayyel; 03-10-2009 at 03:36 AM.

  • #14
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Hayyel View Post
    So I guess my code is wrong still. The error is because it is doing the following:

    PHP Code:
    INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,rank,unknownVALUES ('')
    INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,founder,name,titleString,rank,unknownVALUES ('62'
    One Value for each row instead of 19 per row.
    $firstDimValue must be a array and must have 19 items in your case.
    If the items in $firstDimValue correspond to fields note,careerID,careerString,rankInAlliance,lastLogin,bearerStatus, lastLoginNumberHour24, zoneID, lastLoginNumberDay, statusNumber, lastLoginNumberMonth, lastLoginNumberYear, memberID, onote, founder, name, titleString, rank, in that order, you can do something like that:
    PHP Code:
    $sql "INSERT INTO guild_roster (note,careerID,careerString,rankInAlliance,
    lastLogin,bearerStatus,lastLoginNumberHour24,zoneID,lastLoginNumberDay,
    statusNumber,lastLoginNumberMonth,lastLoginNumberYear,memberID,onote,
    founder,name,titleString,rank,unknown) VALUES ("
    join(',',$firstDimValue) .")"
    and you don't need the inner foreach.
    Before that check if $firstDimValue is what you expect using print_r or var_dump.

    best regards

  • Users who have thanked oesxyl for this post:

    Hayyel (03-12-2009)


  •  

    Posting Permissions

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