up vote 1 down vote favorite

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?

share|improve this question
Hopefully your'e sanitizing your inputs in the production code. that code just screams SQL injection. – canadiancreed Sep 5 '11 at 2:59
of course!!! :) – Christopher Sep 5 '11 at 3:03

2 Answers

up vote 1 down vote accepted

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');";
share|improve this answer
Thanks Roshan - what field type would the family column in my database need to be VarChar? – Christopher Sep 5 '11 at 3:21
yes it would be varchar gud luck – Roshan Wijesena Sep 5 '11 at 3:48
up vote 2 down vote

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

MySQL Joins

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

share|improve this answer

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.