up vote 0 down vote favorite

I am very new to mysql and php. I need to put an array from php into a data table:

The array looks like this:

$memberdata
    Key    value
    apple  5
    banana 8
    salmon 3
    candle 4
    ..........
    and 100 more...

My mysql table looks like this:

MemberTable
    ID(INT11)=PK    MemID(INT11)      stuff(varchar)     value(INT2)
    1               23                apple              5
    2               23                banana             8
    3               23                salmon             3
    4               23                candle             4
    5               45                banana             1
    6               45                apple              9

So each member; here member 23 and 45 can have the same stuff but with other values, so for every member i have one php array of data. (mysql ID is auto increment).

My question: is there a possibility to store an array directly into a mysql table...? In a book that i read they make a foreach loop and in the loop they open a connection to the database... so i thought that is maybe to time consuming:

book example:

$cxn = mysqli_connect($host,$user,$pass,$dbname);
$MemID = "23";
$query_pre = "INSERT INTO MemberTable ('MemID','stuff','value') VALUES (";
Foreach ($memberdata as $stuff => $value)
{
 $query = $query_pre . "$MemID,$stuff,$value)"; 
 $result = mysqli_query($cxn, $query) or die ("Couldn't execute query");
}

Regards, Thijs

link|improve this question
97% accept rate
Using a foreach is the best way to do this. – Rocket Dec 21 '10 at 19:35
feedback

5 Answers

up vote 1 down vote accepted

Well that's very strange book. Although idea in general is right, this particular code contains both PHP and mysql errors.

it should be something like this (not tested):

$cxn = mysqli_connect($host,$user,$pass,$dbname);
$MemID = "23";
$prequery = array();
Foreach ($memberdata as $stuff => $value)
{
  $stuff = mysqli_real_escape_string($stuff);
  $value = mysqli_real_escape_string($value);
  $prequery[] = "($MemID,'$stuff','$value')"; 
} 
$query = "INSERT INTO MemberTable (MemID,stuff,value) VALUES ".implode(",",$prequery);
$result = mysqli_query($cxn, $query) or die (mysqli_error($cxn).$query);
link|improve this answer
Hi, does this implode function puts all the data in one string, and then in one datacell in MySQL? Because the array can exceed 8000 characters (longtext) – Thijs Dec 22 '10 at 12:43
@Thijs yes, obviously. Try to read function descriptions at least. Or you won't learn anything. However, Mysql default data packet is 2M. check if your final query fits into it (hint: strlen()) – Your Common Sense Dec 22 '10 at 13:52
I didn't need to get all the data in one string... the database must be searchable afterwards... My question was if it was possible to store an array in a database but also in the correct place and not as one string in a cell and later explode it back into php... I am reading the serialize - unserialize part of php manual, but, if i am correct it puts an array in a standarized way into a string, ready for mysql... and that you can unserialize it later so that it is readable for php (puts it back in the array variable again..) I know i have a lot to learn... sorry! – Thijs Dec 25 '10 at 10:37
@Thijs I was speaking not of data cell but of data packet. As for the cells, it's okay. I see your problem now. You don't know what SQL QUERY you need, but asking for PHP CODE to produce it. Learn SQL first. As for my code it's okay, it works as you need. It performs MANY inserts using just one query string – Your Common Sense Dec 25 '10 at 13:29
Thank you for having so much patience... Do you have any tips for a good mysql book... (i've read php / mysql for dummies.. i know!!) if i understand your code correct you can insert a lot of data in one query as a packet, so first you put all the data in (in this case) 3 arrays $prequery[] = "($MemID,'$stuff','$value')"; and then out of the "foreach" loop you put it in the VALUES, and u use .implode to put the 3 arrays together... – Thijs Dec 25 '10 at 21:04
show 5 more comments
feedback
up vote 0 down vote

You avoided the big mistake of running a separate insert query for each associated array element. That is what a lot of beginners do and it is extremely inefficient when using this PHP MySQL extension. This statement comes with the assumption that you are doing one transaction per query.

Since this is only one query, this is about as efficient as you can get. If you were to find a different PHP MySQL extension (i.e. PDO) that had a function to take an array like you have here, all it would be doing in the background is generated this huge query for you then executing it.

So, in short, you're doing it correctly.

link|improve this answer
it is not him, but the book. and this code will never work. – Your Common Sense Dec 21 '10 at 20:14
feedback
up vote 1 down vote

The implementation you've written above is the most efficient way I'm aware of getting your data into the table. One thing you might want to consider is making sure that the record's not a duplicate of an already-existing record before inserting it. There are various ways to do that, including (this is not the most efficient way, but it works) clearing out that part of the table before doing the insert, doing a select query with a conditional run of your insert if its record count is 0, or using the INSERT OR UPDATE syntax, found here: MySQL reference docs.

I hope this helps!

Kyle

link|improve this answer
feedback
up vote -1 down vote

Your approach to insertion is correct, but your data isn't normalized. Keep this in mind if your table sizes begin to get too huge. Your MemID and StuffID would be the unique key.

link|improve this answer
feedback
up vote -1 down vote

Kind of an odd way to store the information for the users.

You can store the array directly in a single row. Use the serialize function, like $sqlinput = serialize($array) and insert $sqlinput into the DB for that user. Then when recalling it from the DB, use unserialize(). Alternatively, you could implode the array into a single string, and then insert it into the db, and then explode it again when recalling it.

link|improve this answer
I can't find a good example about the serialize function yet... But is this serialize function a function, which puts the whole column right in the database? so without the need of a foreach loop?? – Thijs Dec 22 '10 at 12:37
You are wrong. Buy yourself a book on database basics. – Your Common Sense Dec 22 '10 at 13:46
feedback

Your Answer

 
or
required, but never shown
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.