Insert comma seperated array in mysql

Databases
1

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? :confused:

2

Hi Anita,

It seems like you are storing a tree structure of agents. That sounds a bit like a pyramid scheme. Are you running or working for someone who is running a pyramid scheme?

3

Thanks for your reply parallelist. Yes its almost like a pyramid scheme.I will use this data in another form where user selects an agent and by using in_array or something like that I will display text boxes in front of agent names.

4

I assume the field parent_arr is a varchar or text field in the DB?

If so you would just append the new ID on as a string, or you could explode the current list and add it on and then implode it back.

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

Or

$b = explode(',',$row1['parent_arr']);
$b[] = $a;
$b = implode(',',$b);
5

You never store comma separated arrays in a database - each entry in the comma separated array needs to be stored in its own row in a table in the database. This table would have a many to one relationship to the original table.

6

I agree normalization is the better solution. My response was based on what looked to be his decision to go the one field route for whatever reason.

7

thank you so much phpMyDirectory.this solution is much simpler :slight_smile: 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 felgall and phpMyDirectory.

8

You’re putting more work onto PHP than needs be.

It “works” but it isn’t the best way to go about it and you should really get away from both deprecated mysql_ and “storing what should be fields in a field” especially if you expect your database to get to any appreciable size.

Can you ALTER your database structure?