php insert multi-dimensional array to the database

[KanaTronix]

Hello I have this multi dimensional array which needs to be inserted to the database table.

[business] => Array
        (
            [title] => Email Marketing
            [URL] => email-marketing
            [category] => 3
            [region] => 2
        )

    [description_] => Array
        (
            [text] => Some desc
        )
    [logo_] => Array
        (
            [blob] => mainlogo.png
        )

    [location_] => Array
        (
            [text] => Array
                (
                    [0] => Lemara Main Office
                    [1] => Themi branch
                    [2] => Sinoni branch
                )
            [priority] => Array
                (
                    [0] => 1
                    [1] => 2
                    [2] => 3
                )

        )

    [photo_] => Array
        (
            [path] => Array
                (
                    [0] => lemaraphoto.png
                    [1] => themiphoto.png
                    [2] => sinoniphoto.png
                )
        )
    [video_] => Array
        (
            [path] => Array
                (
                    [0] => lemaravideo.mp4
                    [1] => themivideo.mp4
                    [2] => sinonivideo.mp4
                )

        )

    [product_] => Array
        (
            [p_id] => Array
                (
                    [0] => product photo
                    [1] => product 3 photo
                    [2] => Product 2 photo
                )
            [text] => Array
                (
                    [0] => product desc
                    [1] => product 3 desc
                    [2] => product 2 desc
                )

            [expire] => Array
                (
                    [0] => product expire
                    [1] => product 3 expire
                    [2] => Product 2 expire
                )
            [title] => Array
                (
                    [0] => product
                    [1] => Product 3
                    [2] => Product 2
                )

        )

    [service_] => Array
        (
            [p_id] => Array
                (
                    [0] => Service 2 photo
                    [1] => service 3 photo
                    [2] => service photo
                )
            [text] => Array
                (
                    [0] => service 2 desc
                    [1] => service 3 desc
                    [2] => service desc
                )

            [expire] => Array
                (
                    [0] => Service 2 expire
                    [1] => service 3 expire
                    [2] => service expire
                )
            [title] => Array
                (
                    [0] => Service 2
                    [1] => service 3
                    [2] => service
                )

        )

)

That's the data. The database table, the table field and the corresponding value to be inserted in to the database. I'm using CodeIgniter any help would be appreciated. 

I have managed to make it wotk in this format

[[business] => Array
        (
            [title] => Email Marketing
            [URL] => email-marketing
            [category] => 3
            [region] => 2
        )

    [description_] => Array
        (
            [text] => Some desc
        )
    [logo_] => Array
        (
            [blob] => mainlogo.png
        )
]

I used this code to do it

function add_bz($data){
  $this->db->trans_start(); $er=0;
  foreach($data as $table => $sql){
   if($table==="business"){
    $this->db->insert($table, $sql);
    $id = $this->db->insert_id();
   } else {
   array_merge($sql, array('idd' => $id));
   $this->db->insert($table, $sql);}
  }
  $this->db->trans_complete();
  if ($this->db->trans_status() === FALSE){print "Transaction Fails";return FALSE;}
  return TRUE;
}

Thanks in advance.

[ginerjm]

This would be a terribly poor use of a database.  Number 1 rule of db design is you create fields for specific and singular (?) data items, not make fields that contain "data structures' which is what you want to do.  Data is simply that - data.  DBs do not hold 'presentation' - they only hold values.  You are trying to store data in a specific format and that makes your data virtually unusable for any other purpose.

 

If you really need to use that data regularly in this array format, write yourself a class for it that contains the logic needed to transport your data to and from the db and the array.  Of simply write a function that you can include in whatever scripts that need to access this data.

 

Think about your array and how it is built and then design your table so that you can easily break down your array and store it and vice versa.

[KanaTronix]

This would be a terribly poor use of a database.  Number 1 rule of db design is you create fields for specific and singular (?) data items, not make fields that contain "data structures' which is what you want to do.  Data is simply that - data.  DBs do not hold 'presentation' - they only hold values.  You are trying to store data in a specific format and that makes your data virtually unusable for any other purpose.

 

If you really need to use that data regularly in this array format, write yourself a class for it that contains the logic needed to transport your data to and from the db and the array.  Of simply write a function that you can include in whatever scripts that need to access this data.

 

Think about your array and how it is built and then design your table so that you can easily break down your array and store it and vice versa.

Thanks for tip. I know a lot about DB designs. And this design is one of the best if you could see it. The names on the array are just names not data types and thanks for reply but I managed to solve the issue with this code

    function add_bz($data){
		$this->db->trans_start();
		//print_r($data);
		foreach($data as $table => $sql){
			for($r=0;$r<count($sql);$r++){
				if($table==="business"){
					$this->db->insert($table, $sql[$r]);
					print $id = $this->db->insert_id();
				} else {
					$sql[$r] = array_merge($sql[$r], array('idd' => $id));
					$this->db->insert($table, $sql[$r]);
				}
			}
		}
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){print "Insert OP Fails";return FALSE;}
		return TRUE;
	}

[ginerjm]

I find your statement "I know a lot about database designs" hard to believe.  One who knows such things would never use it this way.  You have DATA that is now unusable except in this one circumstance.  What good is that DATA to any other purpose you may in the future?  It's like creating a telephone book with the phone numbers in Cyrillic characters and the names in English ones.  It's only good for people who can read both and so is your highly constricted data storage plan.

 

IMO - You don't have much of a future as a db administrator.

[KanaTronix]

I find your statement "I know a lot about database designs" hard to believe.  One who knows such things would never use it this way.  You have DATA that is now unusable except in this one circumstance.  What good is that DATA to any other purpose you may in the future?  It's like creating a telephone book with the phone numbers in Cyrillic characters and the names in English ones.  It's only good for people who can read both and so is your highly constricted data storage plan.

 

IMO - You don't have much of a future as a db administrator.

This is a logic testing case. When all ideas and logic are fully ready to use it'll be pluggable and very cool that what you are seeing. The reason I use the arrays is that form is to entertain dynamic feature adding which wouldn't need any interaction with the code directly.

[ginerjm]

Huh?  What was that you said? 

 

Oh, never mind.  We obviously think differently.