insert an array into mysql database

Question

I have the following array which I would like to insert into mysql database.

Item[0] = Soccer
Item[1] = Rugby
Item[2] = Football
Item[3] = Netball
Item[4] = Hockey

I am using the following function to insert into the database, located in functions.php:

//Capture items 
    function item($register_data)
    {
        array_walk($register_data,'array_clean');

        $fields = ' '.implode(',',array_keys($register_data)).' ';
        $data = '\''.implode('\',\'',$register_data).'\'';

        //Insert user Data into the database
        $query = "INSERT INTO items ($fields) VALUES ($data)";
        mysql_query($query);
    }

Now this is how I insert:

for($i =0;$i<4;$i++)
                {
                    $item = array($i = item[i]);        
                }


//Call the function to insert into the database
item($item);

This method doesn't seem to work. Please assist

1) Use mysql_error() to get hints to what went wrong. 2) Switch to mysqli or PDO as mysql_x functions are deprecated. — Sirko
– Sirko, Commented Mar 14, 2013 at 12:47
Doesn't work how? Also, mysql_* is deprecated... Check into PDO or MySQLi. — brbcoding
– brbcoding, Commented Mar 14, 2013 at 12:48
What does your database look like? That array looks like a list of set of records rather than columns, so why are you inserting them as fields. Also arraykeys (0, 1, 2) are terrible names for your fields. — Husman
– Husman, Commented Mar 14, 2013 at 12:49

artahian · Accepted Answer · 2013-03-14 13:02:23Z

1

PHP has a built in serialize & deserialize functions (http://php.net/manual/en/function.serialize.php) for that. You can pass any object or array to it, and it will return a serialized string, which you can store in a single field in the database.

edited Mar 14, 2013 at 13:02

answered Mar 14, 2013 at 12:55

artahian

2,10314 silver badges17 bronze badges

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

1 Comment

user1783675 Over a year ago

I dont really wanna serialize the input data. I would like to insert it individually. One after Another

Edward A · Accepted Answer · 2013-03-14 13:16:19Z

1

Since your items table has two columns, one of which is id (auto increment) you're going to want to skip over it and only specify the second column ItemName. This is described in the mysql documentation on this page.

Lets say you want to insert 5 sports in this table:

$sports[0] = "Soccer";
$sports[1] = "Rugby";
$sports[2] = "Football";
$sports[3] = "Netball";
$sports[4] = "Hockey";

You'll want the query to look something along these lines:

INSERT INTO items (ItemName) VALUES ('Soccer'), ('Rugby'), ('Football'), ('Netball'), ('Hockey');

The code would look something like this:

function insert($insert_data)
{
    $fields = implode(', ', $insert_data);
    $data = '(\''.implode('\'), (\'', $insert_data).'\')';

    //mysql_query("INSERT INTO items (ItemName) VALUES $data;");
    echo "INSERT INTO items (ItemName) VALUES $data;";
}

$sports = array();
$sports[0] = "Soccer";
$sports[1] = "Rugby";
$sports[2] = "Football";
$sports[3] = "Netball";
$sports[4] = "Hockey";

insert($sports);

You may also want to sanitize the strings first and take a look at mysqli since mysql is pretty outdated.

answered Mar 14, 2013 at 13:16

Edward A

2,3102 gold badges18 silver badges31 bronze badges

5 Comments

user1783675 Over a year ago

Nice Example. Let me try this

user1783675 Over a year ago

What if I dont have a static number of fields for the $sports array ?

Edward A Over a year ago

@user1783675 You mean if the sports array has more than 5 sports? The insert function is going to generate a query depending on how many sports it has, as long as the array is larger than 1 it should be fine.

user1783675 Over a year ago

yes @Edward-a. The number of sporting activities varies all the time. for user it can be 2, another 5 and another user 15.

Edward A Over a year ago

@user1783675 As long as there is one or more sports in the array it should do the job.

Collectives™ on Stack Overflow

insert an array into mysql database

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related