Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have the following array I want to store in my database...

$insData = array(
    'uid' => $fbme['id'],
    'first_name' => $fbme['first_name'],
    'last_name' => $fbme['last_name'],
    'email' => isset($fbme['email']) ? $fbme['email'] : '',
    'link' => $fbme['link'],
    'affiliations' => $networks,
    'birthday' => $info[0]['birthday_date'],
    'current_location' => isset($fbme['location']['name']) ? $fbme['location']['name'] : '',
    'education_history' => $education,
    'work' => $workInfo,
    'hometown_location' => isset($fbme['hometown']['name']) ? $fbme['hometown']['name'] : '',
    'interests' => $info[0]['interests'],
    'locale' => $info[0]['locale'],
    'movies' => $movies,
    'music' => $music,
    'political' => $info[0]['political'],
    'relationship_status' => $info[0]['relationship_status'],
    'sex' =>  isset($fbme['gender']) ? $fbme['gender'] : '',
    'tv' => $television,
    'status' => '0',
    'created' => $now,
    'updated' => $now,
);

I've tried searching google on how to do this and all I can find is information stating my array needs to be split, before inserting into the table. Is this correct? Sorry for the naivity, very new to php.

share|improve this question
1  
Your first step is probably to define your table(s) structure; you can do this very easily in phpMyAdmin. Then do some research around inserting into tables - a web search on "PHP PDO insert example" should bring back hundreds of results. Finally give that a go, paste it into your question, and someone will offer pointers! – halfer Apr 7 '12 at 12:52
Do you have access to google ? Just wonder and surprised – sakhunzai Apr 7 '12 at 12:53
"my array needs to be split before inserting into the table" - that depends. Does your array above contain several tables/rows worth of information? If yes to the second, yes to the first. – halfer Apr 7 '12 at 12:53
Does a table/tables match the exact schema? – aitchnyu Apr 7 '12 at 13:02

6 Answers

up vote 7 down vote accepted

You can not insert a array directly to mysql as mysql doesn't understand php data types. Mysql only understands SQL. So to insert this array into a mysql database you have to convert it to an sql statement. This can be done manually or by a library. The output should be an INSERT statement.

Here is a standard mysql insert statement. 

INSERT INTO TABLE1(COLUMN1, COLUMN2, ....) VALUES (VALUE1, VALUE2..)

If you have a table with name fbdata with the columns which are presented in the keys of your array you can insert with this small snippet. Here is how your array is converted to this statement.

$columns = implode(", ",array_keys($insData));
$escaped_values = array_map('mysql_real_escape_string', array_values($insData));
$values  = implode(", ", $escaped_values);
$sql = "INSERT INTO `fbdata`($columns) VALUES ($values)";
share|improve this answer
1  
Note that escaping your values is a very important step. It prevents sql injection. – Guido Gautier Apr 7 '12 at 13:02
up vote 2 down vote

There are a number of different ways... I will give you an example of one using prepared statements:

$prep = array();
foreach($insData as $k => $v ) {
    $prep[':'.$k] => $v;
}
$sth = $db->prepare("INSERT INTO table ( " . implode(', ',array_keys($insData)) . ") VALUES (" . implode(', ',array_keys($prep)) . ")");
$res = $sth->execute($prep);

I'm cheating here and assuming the keys in your first array are the column names in the SQL table. I'm also assuming you have PDO available. More can be found at http://php.net/manual/en/book.pdo.php

share|improve this answer
up vote 1 down vote

Let's not forget the most important thing to learn out of a question like this: SQL Injection.

Use PDO and prepared statements.

Click here for a tutorial on PDO.

share|improve this answer
up vote 1 down vote

Serialize the array and you'll have a text on your database column, that will solve the problem.

I do that, for instance to save objects, that way I can retrieve them easily.

share|improve this answer
virtual -1, saving data in a non-normalized table is bad practice. – shiplu.mokadd.im Apr 7 '12 at 13:04
1  
@shiplu.mokadd.im - I agree in the main, but it is not always true. Tables with a small number of custom child columns can get a great speed benefit using this approach - Wordpress uses it. – halfer Apr 7 '12 at 13:14
It can be beneficial. But you should not use it when you are developing something. You should use this when you got performance problem and the benchmark shows positive result. Not before that. Its premature optimization. – shiplu.mokadd.im Apr 7 '12 at 13:17
up vote 0 down vote

You have 2 ways of doing it:

  • You can create a table (or multiple tables linked together) with a field for each key of your array, and insert into each field the corresponding value of your array. This is the most common way
  • You can just have a table with one field and put in here your array serialized. I do not recommend you do do that, but it is useful if you don't want a complex database schema.
share|improve this answer
up vote 0 down vote

Personally I'd json_encode the array (taking into account any escaping etc needed) and bung the entire lot into an appropriately sized text/blob field.

It makes it very easy to store "unstructured" data but a real PITA to search/index on with any grace.

A simple json_decode will "explode" the data back into an array for you.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.