insert comma seperated array in mysql

[anita_86]

Hi, I am working on a script where I want to select parent id for a new user and save his parent id along with parents parent ids in mysql table.

$last_id = mysql_insert_id(); 

//select parent id of last inserted user 
$sel = mysql_query("SELECT * FROM tbl_new_agent WHERE new_agnt_id = '".$last_id."'"); 
$row = mysql_fetch_array($sel); 
$a = $row['new_agnt_ParentId']; 

//select parents of parent which are supposed to be stored at parent_arr field in db as comma separated values                                      
$sel1 = mysql_query("SELECT * FROM tbl_new_agent WHERE new_agnt_id = '".$a."'"); 
$row1 = mysql_fetch_array($sel1); 

//here i want to add element in array.though its not working obviously 
$b = array_push($row1['parent_arr'],$a); 

$update = mysql_query("UPDATE tbl_new_agent SET parent_arr = '".$b."' WHERE new_agnt_id = '".$last_id."'");  

I have no idea how I can store comma separated values or store a single value initially and then add more elements.Can anyone give me idea in this regard?

[cereal]

Hi, check if this thread helps with your issue:

• http://www.daniweb.com/web-development/databases/mysql/threads/473216/alternative-way-of-an-insert-with-where-clause

[anita_86]

thanks cereal.i have tried using concat_ws.but it is updating array like ,,1 and so on.

[EvolutionFallen]

I think this is overcomplicated and we're mixing up terminology. There is no comma-separated array in your database. What you probably have is a string separated by commas, unless you're using JSON or serialize().

That being said, all you have to do is concatenate your ID onto the end of the string. Starting from replacing line 13:

$new_parent_arr = $row1['parent_arr'].','.$a;

$update = mysql_query("UPDATE tbl_new_agent SET parent_arr = '$new_parent_arr' WHERE new_agent_id = $last_id");

Overall I think there are better ways to go about this.

By the way, if you later want to work with these values in parent_arr, use explode() (http://www.php.net/manual/en/function.explode.php)

Lastly, the mysql extension for PHP is deprecated. I suggest you look into mysqli or PDO

[anita_86]

thank you so much EvolutionFallen.this solution is much simpler :) during google search I also found that storing comma separated values is not a good idea.

for now i have found this solution:

$update = mysql_query("UPDATE tbl_new_agent SET parent_arr = CONCAT('".$b."','".$a."',',') WHERE new_agnt_id = '".$last_id."'");

and using in this way in another page:

$agent_id = $_POST['comm_Agent'];  
$query = mysql_query("SELECT * FROM tbl_new_agent");

while($row = mysql_fetch_array($query)){
         $parent_arr = explode(',',$row['parent_arr']);
               if (in_array($agent_id, $parent_arr)) {
                            echo $row['new_agnt_id'];
               }
}

i am sure there are other 'neat' ways to achieve this.but so far above code is working as expected. thanks for your replies cereal and EvolutionFallen.

[EvolutionFallen]

Glad it's working for you! Please mark the thread as solved if we're all set.