How to store multi-dimentional arrays (associative arrays) to MySQL database using PHP?

Hi All,

I need help in storing multidimensional array(associative array) to MySQL using PHP. I have tried searching and was lucky to find Advanced loops with multi-dimensional array posting and this shed a little light that it was possible to write this to DB.

anyone can re-write below so data can be added to the database ?
Also what is the best table structure to use ?

function makeNestedList(array $Array){
	$Output = '<ul>';
	foreach($Array as $Key => $Value){
		$Output .= "<li><strong>{$Key}: </strong>";
		if(is_array($Value)){
			$Output .= makeNestedList($Value);
		}else{
			$Output .= $Value;
		}
		$Output .= '</li>';
	}
	$Output .= '</ul>';
	return $Output;
}
$Data = array("Some Info" => array("A" => "a", "B" => array("B1" => "b1", "B2" => "b2"), "C" => array("C1" => array("C11" => "c11", "C12" => "c12", "C13" => array("C131" => "c131", "C132" => "c132")), "C2" => "c2")));
echo makeNestedList($Data);

You’ll have to be more specific.

What are you trying to store information about? What is your table layout?

Thank you for your quick response StarLion.

I am trying to use the code I’ve seen to no avail and I am just new at work.

To be specific, I have below array:

$mychart = array( ‘Dhon’ => array( ‘position’ => ‘CEO’, ‘salary’ => 240000, ‘reports’=>array( ‘Jane’ => array( ‘position’ => ‘CIO’, ‘salary’ => 120000, ‘reports’=>array( ‘jaime’=>array(‘position’ => ‘Sales Rep’, ‘salary’ => 50000, ‘reports’=>array() ),‘jackson’=>array( ‘position’ => ‘CS Rep’, ‘salary’ => 24000, ‘reports’=>array() ),‘julia’=>array( ‘position’ => ‘CS Rep’, ‘salary’ => 24000, ‘reports’=>array() ) ) ), ‘charm’=>array( ‘position’ => ‘CTO’, ‘salary’ => 120000, ‘reports’=>array( ‘andrew’=>array( ‘position’ => ‘SysAdmin’, ‘salary’ => 75000, ‘reports’=>array( ‘erwin’ => array( ‘position’ => ‘Tech Support’, ‘salary’ => 24000, ‘reports’=>array() ) ) ),‘joy’=>array( ‘position’ => ‘Developer’, ‘salary’ => 100000, ‘reports’=>array() ) ) ) ) ) )

I am challenged at work to create a function that accepts associative array as input and stores it in mysql database.

  1. What is the best table structure to use and can you please add create table statement?
  2. How can I save such data into the database using PHP? How to add data to the existing database say for example another CS Rep reporting to Jane?

Dhon:
position: CEO
salary: 240000
reports:
Jane:
position: CIO
salary: 120000
reports:
jaime:
position: Sales Rep
salary: 50000
reports:
jackson:
position: CS Rep
salary: 24000
reports:
julia:
position: CS Rep
salary: 24000
reports:
New CS Rep User:
position: CS Rep
salary: 24000
reports:

charm:
position: CTO
salary: 120000
reports:
andrew:
position: SysAdmin
salary: 75000
reports:
erwin:
position: Tech Support
salary: 24000
reports:
joy:
position: Developer
salary: 100000
reports:

I know its a bit of a task but it will help me greatly. Thanks again for the help.

Using indentation to make things a bit more clear suggests that the nested array structure is off or is (IMHO) poorly designed using identical key names at various depths. i.e.

$mychart = array(
	'Dhon' => array(
		'position' => 'CEO',
		'salary' => 240000,
		'reports'=>array(
			'Jane' => array(
				'position' => 'CIO',
				'salary' => 120000,
				'reports'=>array(
					'jaime'=>array(
						'position' => 'Sales Rep',
						'salary' => 50000,
						'reports'=>array() ),
					'jackson'=>array(
						'position' => 'CS Rep',
						'salary' => 24000,
						'reports'=>array() ),
					'julia'=>array(
						'position' => 'CS Rep',
						'salary' => 24000,
						'reports'=>array() ) ) ),
					'charm'=>array(
						'position' => 'CTO',
						'salary' => 120000,
						'reports'=>array(
							'andrew'=>array(
								'position' => 'SysAdmin',
								'salary' => 75000,
								'reports'=>array(
									'erwin' => array(
										'position' => 'Tech Support',
										'salary' => 24000,
										'reports'=>array() ) ) ),
							'joy'=>array(
								'position' => 'Developer',
								'salary' => 100000,
								'reports'=>array() ) ) ) ) ) )

Well the quick and most straightforward way is to serialize your array to a string, check out php’s builtin serialize($var) function and unserialize($var) for information.

I wonder why you will need to store a multi-dimensional array in the first place, more precisely why do you need such a persistant multi-dimensional array at all? In most cases, you should rewrite your code and convert your array into object, and multi-dimensional array into 1-D array of objects. Try object oriented programming and do not use deep nested array, the latter is not good for application design and your array can easily becomes hard to read or maintain.

Thank you for your kind suggestions and I actually started looking at OOP. I believe like what you said it is more reliable and easier to read once done. However, I need to start somewhere and this one is my challenge at work. So if you can throw the lines of codes to serialize, saving entries to a mysql database and be able to browse and show the data by use of function then I’d really appreciate it or a pseudocode might do since I also understand that you all have other things. I am a newbie and would like to get this working using this solution for now and move to better coding like you said.

Thanks Mittineague. Appreciate your insights.

Please guide me how you would best handle this and actual coding will help a lot for me to better understand.

What table structure will you use? A pseudocode of the solution can help but if actual coding showing it how it was coded and written to mysql DB then I’d really appreciate it.

Thank you again for your time.

My first question is

The array has
numerical key - name value
position key
salary key
reports key

where the reports key can have a value of an array of the above, is that correct?

Yes I believe so since like you said the report output should be able to search the array.

Being able to search an array isn’t what I’m asking. What I’m interested in ATM is if
a “reports” is supposed to be able to have other “user arrays” that contain “position”, “salary” and “reports” as in post #4

Apologies but my asnwer is Yes.

I know you are a newbie and the idea/concept of OOP may appear to be overwhelming, but I recommend you to learn it the hard way as you do appear to have the very basic knowledge of coding already. PHP as a language does not enforce good programming and design practices, so newbies can easily develop bad habits that may take years to change if they do not follow good guidelines. One of this bad habits is procedural programming, which is very amateurish. It hinders your capability to think the right way, and will make it very difficult for you to find a job on the software developer market. So my two cents are, learn OOP before you already get used to the bad procedural coding habit, which many PHP newbies inevitably become of. This way you will be a better and professional programmer in shorter time. Once the concepts of object and object oriented design start to click, you will fall in love with it and never go back to the old spaghetti way.

I will definitely dive into it and thanks for your kindness. Because I started informally in programming, I will need to have a basis (hope Mittineague may have time to share some solutions) and this problem once I got a code that I understand I will therefore be able to convert it to OOP which will definitely improve not just the program but also my skills. I know you do understand where I am coming from :slight_smile:

Also, it will be useful if you can suggest any website that I can refer to as a start based from my level.

Thanks again.

anyone can help me convert this code to be able to save the data to mysql ? I can output them using the function below but can I also use this function to store the data to my DB and how?

<?php

$mychart = array( 'Dhon' => array( 'position' => 'CEO', 'salary' 
=> 240000, 'reports'=>array( 'Jane' => array( 'position' => 
'CIO', 'salary' => 120000, 'reports'=>array( 
'jaime'=>array('position' => 'Sales Rep', 'salary' => 50000, 
'reports'=>array() ),'jackson'=>array( 'position' => 'CS Rep', 
'salary' => 24000, 'reports'=>array() ),'julia'=>array( 
'position' => 'CS Rep', 'salary' => 24000, 'reports'=>array() )
 ) ), 'charm'=>array( 'position' => 'CTO', 'salary' => 120000, 
'reports'=>array( 'andrew'=>array( 'position' => 'SysAdmin', 
'salary' => 75000, 'reports'=>array( 'erwin' => array( 
'position' => 'Tech Support', 'salary' => 24000, 
'reports'=>array() ) ) ),'joy'=>array( 'position' => 
'Developer', 'salary' => 100000, 'reports'=>array() ) ) ) ) ) )


function makeNestedList(array $mychart){
    $Output = '<ul>';
    foreach($mychart as $Key => $Value){
        $Output .= "<li><strong>{$Key}: </strong>";
        if(is_array($Value)){
            $Output .= makeNestedList($Value);
        }else{
            $Output .= $Value;
        }
        $Output .= '</li>';
    }
    $Output .= '</ul>';
    return $Output;
}

?>
$insertQuery    = array();
                $insertData        = array();
                foreach ($rights_status['add'] AS $row ) {
                    $insertQuery[] = '(?,?)';
                    $insertData[] = $rank;
                    $insertData[] = $row['id'];
                }
                
                if (!empty($insertQuery)) {
                    $sql .= implode(', ', $insertQuery);
                    $stmt = $this->db->prepare($sql);
                    $stmt->execute($insertData);
                }

That’s the code that I use in a script to insert multiple rows of data into a table with one query, in that example one field is coming from an array, the other is the same for the rows being inserted. btw the above example is using PDO

SpacePhoenix - Thanks a lot.

Can you show how I can apply it with this array data? I’m really having a hard time understanding which is which and what is what especially now that there’s another PDO option I can use which is by the way good to know.

Please anyone can throw me actual codes to save below to the database? It is what I am having a hard time to do.

mychart = array( ‘Dhon’ => array( ‘position’ => ‘CEO’, ‘salary’
=> 240000, ‘reports’=>array( ‘Jane’ => array( ‘position’ =>
‘CIO’, ‘salary’ => 120000, ‘reports’=>array(
‘jaime’=>array(‘position’ => ‘Sales Rep’, ‘salary’ => 50000,
‘reports’=>array() ),‘jackson’=>array( ‘position’ => ‘CS Rep’,
‘salary’ => 24000, ‘reports’=>array() ),‘julia’=>array(
‘position’ => ‘CS Rep’, ‘salary’ => 24000, ‘reports’=>array() )
) ), ‘charm’=>array( ‘position’ => ‘CTO’, ‘salary’ => 120000,
‘reports’=>array( ‘andrew’=>array( ‘position’ => ‘SysAdmin’,
‘salary’ => 75000, ‘reports’=>array( ‘erwin’ => array(
‘position’ => ‘Tech Support’, ‘salary’ => 24000,
‘reports’=>array() ) ) ),‘joy’=>array( ‘position’ =>
‘Developer’, ‘salary’ => 100000, ‘reports’=>array() ) ) ) ) ) )

I’ve assumed that the array with the data to be inserted is called employees and that the data is going into a table that has 4 fields (name, position, salary, and reports).

try {
    $sql="
        INSERT INTO
            employees
            (
                  name
                , position
                , salary
                , reports
            )
        VALUES
    ";
    $insertQuery    = array();
    $insertData        = array();
    foreach ($emplyees AS $row ) {
        $insertQuery[] = '(?,?,?,?)';
        $insertData[] = $row['name'];
        $insertData[] = $row['position'];
        $insertData[] = $rank['salary'];
        $insertData[] = $rank['reports'];
        
    }
    
    if (!empty($insertQuery)) {
        $sql .= implode(', ', $insertQuery);
        $stmt = $this->db->prepare($sql);
        $stmt->execute($insertData);
    }
}
catch (PDOException $e) {
    echo "
        <p>ERROR! Sorry there was an error with the query.</p>
        <p>Query with error: $sql <br />Reason given: {$e->getMessage()}</p>
    ";
    return false;
}

Is the data being inserted into the database coming from a form?

Yes it will come from a form that simply accepts name, position, salary and to whom the person will be reporting to like below. Anything simple will be appreciated to show how a form will the above solution can accept the input and store them to the database.

Name:

Position:

Salary:

E-mail:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.