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?

4 Years
Discussion Span
Last Post by EvolutionFallen

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


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

Edited by EvolutionFallen


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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.