insert php array into mySql

问题:

I am looking for some guidance.

I have a data form field which I am inserting into a table and am looking to association the data with the id's of other relevant data. I was wondering if there was recommended way to insert an array of relevant Id's in relation to the information I am referring too.

Below is what Im thinking...

Eg. php reads

 <?php
 $name = $_POST['name'];
 $info = $_POST['information'];
 $id = $_POST['id'];
 $family = array();
 ?>
 <?php 

 $select = "SELECT * 
              FROM  `names_family` 
              WHERE  `name` LIKE  '$name'
              LIMIT 0 , 30";

 $selected  = mysql_query($select, $connection);
if(!$selected){
die("Hal 9000 says: Dave the select family name ID query failed " . mysql_error());}


 while($row = mysql_fetch_array($selected)){
       $familyId = $row[0];
       $familyName = $row[1];

 array_push($family, $familyName => $familyId);            

}

 $insertInfo = "INSERT INTO `family_info`.`info` 
            (`name`, `info`, `family`)
            VALUES (
            '$name', '$info', '$family');";

 $insertedInfo  = mysql_query($insertInfo, $connection);
if(!$insertedInfo){
die("Hal 9000 says: Dave the insert info query failed " .   mysql_error());}
 ?>

Is this a recommended way to relate information? Or is another way to achieve the same result?

回答1:

there is another way

      $family=array()
     while($row = mysql_fetch_array($selected)){
           $familyId = $row[0];
           $familyName = $row[1];

      $family[]=$familyName.$familyId;            

    }

 $insertInfo = "INSERT INTO `family_info`.`info` 
            (`name`, `info`, `family`)
            VALUES (
            '$name', '$info', '$family');";

回答2:

What data type is the "family" column in MySQL? I'm pretty sure you can't straight up insert php arrays like that into MySQL. If it's possible, guess it's one of those things I didn't know because I never even tried.

The easiest way to do this is to encode your php array into a JSON string and decode it back into a php array when you read it.

$family = array();
...
$familyJsonString = json_encode($family);
...
$insertInfo = "INSERT INTO `family_info`.`info` 
            (`name`, `info`, `family`)
            VALUES (
            '$name', '$info', '$familyJsonString');";
...
$queryString = "SELECT * FROM family_info WHERE name = '$someName'";
$query = mysql_query($queryString, $connection);
$familyData = mysql_fetch_assoc($query);
$decodedFamilyArray = json_decode($familyData['family']);

where the family column should be a varchar or text type depending on how long the family array gets.

A more robust way to do this is to create a separate table to store your family data and use a MySQL JOIN statement to get the values associated to one entry in the family_info table.

here is some info on joins

Joining two tables without returning unwanted row

http://dev.mysql.com/doc/refman/5.0/en/join.html