How to Store multidimensional array into mysql using PHP

Question

i have created a database called "test" and create a table called "biodata". I have created 3 columns called "Name" "Age" and "Description" into biodata table. Now how to store my array result into each column.

Below is the complete code...

<?php

    $ip = "localhost";
    $username = "root";
    $password = "";
    $dbname = "test";
    $res = mysql_connect($ip,$username,$password);
    if(!$res)
    {
        echo "DB Connection Failed.";
        exit;
    }
    if(!mysql_select_db("test"))
    {
        echo "NOT SELECTED";
        exit;
    }

        $company =  array(
                    'Record1'=>array('Shabbir',26,'Designer'),
                    'Record2'=>array('Burhan',24,'Architecture'),
                    'Record3'=>array('Huzeifa',20,'Accountant'),
                );

        foreach ($company as $employees=>$details){

        echo '<strong>'.$employees.'</strong><br>';

        foreach($details as $employeeinfo){

            echo $employeeinfo.'<br>';

        }

        }

        $sql = "INSERT INTO biodata (Name, Age, Description) VALUES ($employeeinfo[0], $employeeinfo[1], '$employeeinfo[2]')";
        mysql_query($sql);

?>

Baba · Accepted Answer · 2012-04-17 20:01:57Z

1

Your mysql_query is suppose to be inside your foreach statement ... you also need to sanitize your data because of SQL Injection

you also don't need 2 foreach statement ...

Correction

foreach ($company as $employees =>$details){
    echo '<strong>'.$employees.' - OK</strong><br>';
    mysql_query(sprintf($sql,mysql_real_escape_string($details[0]),mysql_real_escape_string($details[1]),mysql_real_escape_string($details[2])));
}

Full Script Arrangement

$ip = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$res = mysql_connect($ip,$username,$password);
$sql = "INSERT INTO biodata (Name, Age, Description) VALUES ('%s', '%d', '%s')";
$company =  array(
        'Record1'=>array('Shabbir',26,'Designer'),
        'Record2'=>array('Burhan',24,'Architecture'),
        'Record3'=>array('Huzeifa',20,'Accountant'),
);

if(!$res)
{
    echo "DB Connection Failed.";
    exit;
}

if(!mysql_select_db("test"))
{
    echo "NOT SELECTED";
    exit;
}

foreach ($company as $employees =>$details){
    echo '<strong>'.$employees.' - OK</strong><br>';
    mysql_query(sprintf($sql,mysql_real_escape_string($details[0]),mysql_real_escape_string($details[1]),mysql_real_escape_string($details[2])));
}

edited Apr 17, 2012 at 20:01

answered Apr 17, 2012 at 19:50

Baba

95.3k29 gold badges172 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shabbir Aftab Over a year ago

can yo please explain me why you use '%s' into VALUES??

Baba Over a year ago

I was trying to format the values properly .. see php.net/manual/en/function.sprintf.php for more information on sprintf ... I glad it worked for you ... don't forget to accept

Shabbir Aftab Over a year ago

Now i want to retrive the data from mysql and want to display on webpage? is this possible??

user188654 · Accepted Answer · 2012-04-17 20:00:42Z

1

A side note. No matter how small your loops are (low number of iterations) do not put queries inside of it. Instead use loops only to construct one complex query containing all the data and then execute the query outside the loop.

EDIT: Example of a query you could construct in a loop.

INSERT INTO table 
    (name, age, position)
VALUES
   ('Shabbir', 26, 'Designer'),
   ('Burhan', 24, 'Architecture'),
   ('Huzeifa', 20, 'Accountant');

answered Apr 17, 2012 at 20:00

user188654

Comments

DaOgre · Accepted Answer · 2012-04-17 19:46:25Z

0

Your SQL query is just in the wrong place:

foreach ($company as $employees=>$details)
{
    echo '<strong>'.$employees.'</strong><br>';
        foreach($details as $employeeinfo)
        {
            echo $employeeinfo.'<br>';
        }
    $sql = "INSERT INTO biodata (Name, Age, Description) VALUES ($details[0], $details[1], '$details[2]')";
    mysql_query($sql);
}

answered Apr 17, 2012 at 19:46

DaOgre

2,10016 silver badges28 bronze badges

1 Comment

Shabbir Aftab Over a year ago

Hey Guyz there is something more i want to retrive the data from mysql and want to display on webpage?

Collectives™ on Stack Overflow

How to Store multidimensional array into mysql using PHP

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related