Php Array nested to mysql (dynamic array)

[Dex2k]

Hey there

 

Looked already through the whole internet

 

Somebody has a function which goes through a nested php Array no matter what structure

The function should analyse the data entries and lookup for the keys and values

It all sounds easy but when I come to the first nest there's another nest in it I get a headache.

At the end I want the php function to create or insert values according to the array structure into the MySQL tables.

Wonder why I haven't seen and solution out there

 

Output : Create table1 (struc1,struc2...)

Or. Insert into table1 (struc1,struc2...) (val1,val2,...)

I myself would be satisfied with that easy solution

[requinix]

That's really vague.

 

How about an example of the input and corresponding output? Actual example, not just some description of it.

[Dex2k]

function array_keys_recursive ($array,$d=0) {

  $flat = array();



  foreach($array as $key => $value) {

    

if (is_array($value)) {

        $flat = array_merge($flat, array_keys_recursive($value));

       

}

    else {



       if (is_int($key)) { 


 $key = "Dummy" . $key . "`";

$flat[] = "`" . $key . " VARCHAR(50) ";

   }   else{$flat[] =  $key . " VARCHAR(50)";}        }   }   return  $flat; }


function traverseArray($array,$table=NULL,$d=1)

{

    $fields  =    array();

$values  =    array();

   // Loops through each element. If element again is array, function is recalled. If not, result is echoed.

    foreach ($array as $key => $value)

    {

        if (is_array($value))

        {

         traverseArray($value,$table,$d+1); // Or

       }

        else

        {

if (is_int($key)) { 

 $key = "Dummy" . $key . "`";

$fields[] = "`" . $key . "`"; 

}

else

{$fields[] = "`" . $key . "`";}



$values[] = "'" . $value . "'";

$field_list = join(",", $fields);

$value_list = join(",", $values);

$query = "<br><br><br>INSERT INTO `" . $table . "` (" . $field_list . ") VALUES (" . $value_list . ");";       


}

    }

 echo $query;

}

$get_data_json= (file_get_contents('test.txt')); //   or the other file test2.txt

$myArray = json_decode($get_data_json, true);


 

$myArray2 = traverseArray($myArray,"table1");

 

 $myArray3 = array_keys_recursive($myArray);

$myArray3 = array_unique($myArray3);

 

 $query = "CREATE TABLE `table1` (" . implode (', ', $myArray3). ");<br>";

test.txt

test2.txt

Edited July 10, 2017 by gizmola

[Dex2k]

both should be inserted or created if possible vial only one function 

anyways i even would be glad if i could get the inserts in one row for text.txt with all the tables . Atm they are seperated due to array one dataset aways cause of "for each" you can see from output . 

 

Creating works 

OUTPUT FOR CREATE TABLE: 

CREATE TABLE `table1` (type VARCHAR(50), state VARCHAR(50), influence VARCHAR(50), has_boss VARCHAR(50), faction_id VARCHAR(50), constellation_id VARCHAR(50), staging_solar_system_id VARCHAR(50), `Dummy0` VARCHAR(50) , `Dummy1` VARCHAR(50) , `Dummy2` VARCHAR(50) , `Dummy3` VARCHAR(50) , `Dummy4` VARCHAR(50) , `Dummy5` VARCHAR(50) , `Dummy6` VARCHAR(50) , `Dummy7` VARCHAR(50) , `Dummy8` VARCHAR(50) );

OUTPUT FOR INSERT TABLE (doesnt work !) need the dummy keys and values behind the second insert mean in one line!) 

INSERT INTO `table1` (`Dummy0``,`Dummy1``,`Dummy2``,`Dummy3``,`Dummy4``,`Dummy5``) VALUES ('30001410','30001411','30001412','30001413','30001414','30001415');
INSERT INTO `table1` (`type`,`state`,`influence`,`has_boss`,`faction_id`,`constellation_id`,`staging_solar_system_id`) VALUES ('Incursion','mobilizing','1','1','500019','20000207','30001414');

Edited July 9, 2017 by Dex2k

[Dex2k]

function array_keys_recursive ($array,$d=0) {
  $flat = array();


  foreach($array as $key => $value) {
    
if (is_array($value)) {
        $flat = array_merge($flat, array_keys_recursive($value));
       
}
    else {


       if (is_int($key)) { 
 $key = "Dummy" . $key . "`";
$flat[] = "`" . $key . " VARCHAR(50) "; 
   }
  else{$flat[] =  $key . " VARCHAR(50)";}
  


    }
  }
  return  $flat;
}

sorry first function got corrupted somehow when editing

[Jacques1]

Oh my.

 

I think the first thing you should do is learn the basics of database design. You definitely do not store a list of values in a gigantic Excel-style list of columns. Data is stored in rows, not in columns. If you need to assign multiple items to another item, you use two separate tables which are linked via a foreign key.

 

Auto-generating tables is generally ill-advised. Not only is it hard to do correctly (you haven't even come close). It also means you're running those completely unprotected queries under a privileged account, maybe even the database superuser. This is an SQL injection vulnerability on steroids.

 

There are two sane options:

• You create the tables ahead of time and carefully insert the data with prepared statements.
• If the data is so dynamic that you don't know the structure yet, use a schemaless database like MongoDB. This actually lets you store JSON documents directly, so you wouldn't even have to convert them.

Edited July 9, 2017 by Jacques1

[Dex2k]

<p>

 

Oh my.

 

I think the first thing you should do is learn the basics of database design. You definitely do not store a list of values in a gigantic Excel-style list of columns. Data is stored in rows, not in columns. If you need to assign multiple items to another item, you use two separate tables which are linked via a foreign key.

 

Auto-generating tables is generally ill-advised. Not only is it hard to do correctly (you haven't even come close). It also means you're running those completely unprotected queries under a privileged account, maybe even the database superuser. This is an SQL injection vulnerability on steroids.

 

There are two sane options:

• You create the tables ahead of time and carefully insert the data with prepared statements.
• If the data is so dynamic that you don't know the structure yet, use a schemaless database like MongoDB. This actually lets you store JSON documents directly, so you wouldn't even have to convert them.

Oh my you haven't even posted a solution it's just use application x and then do it and of course change your whole database to mongo bongo Seems you have no idea

I myself I am a beginner but atleast I try . I know the structure of a database and I am aware of sql injections but it's basically something I do at the end if really needed for my local running little code.

[Jacques1]

So you're beginner. Cute. I've been programming professionally with all kinds of languages and database systems for many, many years. Which means one of us knows what the fuck he's talking about, and the other one doesn't.

 

I've given you a very clear answer, and no self-respecting programmer will tell you anything else. If you don't like competence, don't come to a forum for programmers. Go to one of those Plz-fix-my-code idiot communities.

 

 

 

I know the structure of a database and I am aware of sql injections but it's basically something I do at the end if really needed for my local running little code.

 

Congratulations, you've managed to put the two lamest excuses into a single sentence: “I know all that, but ...” and “It's just a school project!” (or your variant: “It's just running locally!”).

 

Which means you aren't aware of anything. And appearently you like it that way.

[kon]

 

 

I know the structure of a database and I am aware of sql injections  Dev2k

 

Anyone participating in a forum should try at least to understand what the other person says. That you are a “beginner” doesn't exclude you from this rule. You store in your db a “field_list” that is actually an array that has been imploded by comma. What is the reason for that ?. If you know the field couldn't you create the same fields in db? RDMS means Relational database management system and if you have all of your fields in one big txt field there is not relational no more. It is like storing your data to a txt file without even the index files we used those days (copybooks and etc). 

[gizmola]

Hey Dex2k:

 

Jacques may have editorialized a bit, but there is good reason for that. Every day people like you come to this forum looking for help. Sometimes, the thing they are having a problem with, boils down to the fact that they are using the wrong technique to do the job.

 

If I understand you correctly, what you want is essentially a schema - less solution to reading in nested arrays of indeterminate structure, and turning those into database tables.

 

So the first problem is, that relational databases don't work that way. They have schemas. In order to work well, they require that entities be created and that those entities (tables) get related to other tables. But let's come back to that in a second.

 

First let's look at a problem you just whitewash over, with a nested array:

 

 

Here's one that is totally real, and ok:

 

$r[] = array('bossman', 'name' => 'Bob', 'address' => array(array('type' => 'home', 'city' => 'Los Angeles'), array('type' => 'work', 'city' => 'Beverly Hills')), 'employee_id'=>  1, 'spouse' => array('name' => 'Linda'));
$r[] = array('flunky', 'name' => 'Bob, Jr', 'address' => array(array('type' => 'home', 'city' => 'Los Angeles'), array('type' => 'vacation', 'city' => 'Beverly Hills')), 'employee_id'=>  121, 'notes' => "This is Bob's son!");

var_dump($r);

array(2) {
  [0]=>
  array(5) {
    [0]=>
    string(7) "bossman"
    ["name"]=>
    string(3) "Bob"
    ["address"]=>
    array(2) {
      [0]=>
      array(2) {
        ["type"]=>
        string(4) "home"
        ["city"]=>
        string(11) "Los Angeles"
      }
      [1]=>
      array(2) {
        ["type"]=>
        string(4) "work"
        ["city"]=>
        string(13) "Beverly Hills"
      }
    }
    ["employee_id"]=>
    int(1)
    ["spouse"]=>
    array(1) {
      ["name"]=>
      string(5) "Linda"
    }
  }
  [1]=>
  array(5) {
    [0]=>
    string(6) "flunky"
    ["name"]=>
    string(7) "Bob, Jr"
    ["address"]=>
    array(2) {
      [0]=>
      array(2) {
        ["type"]=>
        string(4) "home"
        ["city"]=>
        string(11) "Los Angeles"
      }
      [1]=>
      array(2) {
        ["type"]=>
        string( "vacation"
        ["city"]=>
        string(13) "Beverly Hills"
      }
    }
    ["employee_id"]=>
    int(121)
    ["notes"]=>
    string(18) "This is Bob's son!"
  }
}

So right away you can see that you are going to have a lot of problems here, because there are keys with the same name in different elements, and even at different levels of the hierarchy. You will not be able to have 2 columns with the same name. What are you going to do then? At any particular point, there could be an array element keyed as 'name' that might have an entirely different value in it than the one that caused you to originally create a 'name' column.

 

At the point that you start trying to flatten this, you are also going to lose the hierarchy, which in a relational database would probably be represented with potentially complex relationships that require significant thought by the database architect. No simplistic generic code is going to be able to replace that.

 

Secondly, you see that there's a situation in this example, where there are keys and sub arrays in the 2 top level array elements. Bob has a spouse array, whereas Bob,Jr does not. Bob Jr has a 'note' key. 'Bob' doesn't. So you would potentially at any particular level, need to be changing the schema of the table(s), probably leading to a very confusing and essentially meaningless series of columns that will probably be repeating groups and other violations of the rules of normalization that govern relational database design.

 

The big picture here, that would be noticed by a database architect, is that Bob, Bob,Jr, and Linda are all 'People' and that a good design would be to put them all in a 'Person' table, and then relate People to each other using a 'relationship' type.

 

A generic tool will never have this insight, just looking at the data, so you will inevitably end up with something highly generic, as in a simple table with name=value pairs, perhaps with a datatype for the data, and a grouping mechanism to group a set of attributes together.

 

It's hard to see what the value of this type of exercise would be, or the use, as php data structures that work with databases are typically based on the database and not the other way around. Even in that situation there is a tremendous amount of potential for complexity with any sophisticated db schema. Check out an ORM like Doctrine2 if you want to see what something sophisticated looks like.

 

Since a schema is going to get in your way in an exercise like this, Jacques suggested a nosql database like MongoDB as an alternative. Mongo documents are like rows in a database, only there is no schema or strict requirements that any particular document have the same structure as another document in the collection. You seemed unwilling to even look at that tool for this, without trying to understand what is different about it. I also should mention that phpfreaks has a solid tutorial on using MongoDB with PHP that is 7 years old, so it's not like it's something nobody has heard of or used.

 

I would hope you would be more open, and less thin skinned, to the help that people are trying to give you, even if it's at times blunt and perhaps not what you wanted going into it.

[Dex2k]

Sorry for the emotions but I don't like it if I ask one question and somebody in the first post begins with

oh my

 

Think if we clean the "oh my" in both posts it wouldnt have gotten so emotional

 

Sorry for that didn't meant to

[Dex2k]

ok if its not a good idea to flatten how do i get it to mysql relationship

 

your array converted to json

[{"0":"bossman","name":"Bob","address":[{"type":"home","city":"Los Angeles"},{"type":"work","city":"Beverly Hills"}],"employee_id":1,"spouse":{"name":"Linda"}},{"0":"flunky","name":"Bob, Jr","address":[{"type":"home","city":"Los Angeles"},{"type":"vacation","city":"Beverly Hills"}],"employee_id":121,"notes":"This is Bob's son!"}]

please try it out to see what i mean 

 

http://pojo.sodhanalibrary.com/convertJsonToSQL.html

Edited July 10, 2017 by Dex2k

[Dex2k]

P.S. I know the given example is in JS  but the same should be makeable in PHP or not? 

[Jacques1]

I think we already had this discussion.

 

The simple one-function solution you're looking for doesn't exist. This approach is wrongheaded, and you should abandon it once and forever.

 

If you insist on using MySQL, you'll have to

• analyze all possible data
• hope there are only one-to-one and one-to-many relationships
• manually create all tables with the right types and foreign-key relationships
• then iterate over the JSON documents to insert the items (with prepared statements) and connect them through their foreign keys

This is doable, but it won't be easy, especially for a beginner. Don't expect it to be a quick job where you can just use code from the Internet.

 

The second option (which you seem to ignore) is to use a database which is actually designed for storing this kind of data. With MongoDB, you could insert the exact JSON documents you already have. There wouldn't be any need to convert the document structure into a relational structure. But of course learning MongoDB also takes some time, and it means you won't be able to query the database with SQL.

 

So pick your poison. Again, there is no shortcut, because the data you're dealing with is inherently complex. There may be half-working hacks, but I can assure you that you'll regret those later. Now you relatively small problem. If you store large amounts of data in the wrong format, you're screwed.

Edited July 10, 2017 by Jacques1

[Dex2k]

Guys dont call me dickhead but honestly I cannot go for the MONGO thingy

 

I tried a litte bit and came to following result.

 

Can somebody just help me finish it ? 

 

I hope it also helps some other people with same issue.

 

it has  still some bugs 

--> doesnt take create tables with most fields in it for each table that should be created . Normal should only create 1x query or each table(1x per parent, 1x per child)

--> Puts a "," in the last input or create SQL function 

--> datafields not assigned to INSERT INTO function yet 

<?php 
  


$get_data = (file_get_contents('free_getIncursions.json')); 


  $myArray = json_decode($get_data, true);//Convert json 2 array
  




function plotTree_create($arr,  $table=Null , $indent=0, $mother_run=true){
   
if ($mother_run) {
        // the beginning of plotTree. We're at rootlevel
        //echo "start\n";
    }


    foreach ($arr as $k=>$v){
        // skip the baseval thingy. Not a real node.
        if ($k == "__base_val") continue;
        // determine the real value of this node.
        $show_val = (is_array($v) ? $v["__base_val"] : $v);
        // show the indents
        echo str_repeat("  ", $indent);
        if ($indent == 0) {
            // this is a root node. no parents
         echo   ")<br>Create $table  (";  


 if (is_int($k))
{
$k = "ID" ;
        
} 
 echo $k . " VARCHAR(50) , " ;
        } elseif (is_array($v)){
            // this is a normal node. parents and children
         echo   ")<br> Create $table.sub_";


 echo $k  ."(`ID` int(11), ";
        } else {
            // this is a leaf node. no children
            echo "";
if (is_int($k))
{
$k = "Dummy_" .$k;
        
} 
echo $k . " VARCHAR(50) , " ;
        }


        // show the actual node
        //echo $k . " (" . $show_val. ")" . "\n <br>";




        if (is_array($v)) {
            // this is what makes it recursive, rerun for childs
            plotTree_create($v,$table ,($indent+1), false);
        }


    }


    if ($mother_run) {
       echo");";
  // echo "end\n";
    }
}


function plotTree_insert($arr,  $table=Null , $indent=0, $mother_run=true ,$parent=0 ){
   
if ($mother_run) {
        // the beginning of plotTree. We're at rootlevel
        //echo "start\n";
    }


    foreach ($arr as $k=>$v){
        // skip the baseval thingy. Not a real node.
        if ($k == "__base_val") continue;
        // determine the real value of this node.
        $show_val = (is_array($v) ? $v["__base_val"] : $v);
        // show the indents
        echo str_repeat("  ", $indent);
        if ($indent == 0) {
            // this is a root node. no parents
         echo   ");<br> INSERT INTO $table ($k, ";  




//  echo " (ISPARENT) " ;
$parent =  $k;
        } elseif (is_array($v)){
            // this is a normal node. parents and children
         echo   ");INSERT INTO $table.sub_$k ($parent,";


//echo " (PARENTID) " ;
        } else {
            // this is a leaf node. no children
            echo "";
if (is_int($k))
{
$k = "Dummy_" .$k;
        
} 
echo " " . $show_val. "," ;
        }


        // show the actual node
        //echo $k . " (" . $show_val. ")" . "\n <br>";




        if (is_array($v)) {


            // this is what makes it recursive, rerun for childs
            plotTree_insert($v,$table ,($indent+1), false, $parent);
        }
  
    }


    if ($mother_run) {
         echo ");";
    }
}


plotTree_create($myArray,"Table1");
plotTree_insert($myArray,"Table1");


?>

Edited July 11, 2017 by Dex2k

[Jacques1]

Guys dont call me dickhead but honestly I cannot go for the MONGO thingy

 

We gave you two possible solutions. Two. If you rule out the MongoDB approach for some reason, that leaves you with the second option.

 

However, you clearly have no interest whatsoever in this discussion and just keep going back to the nonsense approach you already had. That's fine. But then we're wasting our time here.