php array data in mysql

Question

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

Answer 1

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);

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.