recursive remove the id

batowiise

Hi All,

All that i want to do with this script is to recursively remove the id of the account that is being deleted from the each friend they have. I am also targeting their friend's arrays in a loop and pluck their id out before deleting the account.

When the script runs, the deletion takes effect but the removal of the id from the friend's array does not work.

Can any one figure out the problem and help me out.

Regard.

$id = $_SESSION['id'];

include_once "scripts/connect_to_mysql.php";

$sql_frnd_arry_mem1 = mysql_query("SELECT friend_array FROM myMembers WHERE id='$id' LIMIT 1");

while($row=mysql_fetch_array($sql_frnd_arry_mem1)) { $frnd_arry_mem1 = $row["friend_array"]; }

foreach ($frnd_arry_mem1 as $key => $value) {
			  if ($value == $id) {
			      unset($frnd_arry_mem1[$key]);
			  } 
    }

$sql = mysql_query("UPDATE myMembers SET friend_array='$frnd_arry_mem1' WHERE id='$id'");

 $sqlTable1 = mysql_query("DELETE FROM myMembers WHERE id='$id'");

bradgrafelman

Why are you using a foreach loop on $row["friend_array"]? You can't store (or retrieve) an array in MySQL, so the foreach loop doesn't make much sense.

batowiise

now pls tell me if the foreach is not appropriate, what will the the appropriate approach?

bradgrafelman

That would depend on how you're storing the data. Can you show us your DB schema and perhaps a sample row or two?

batowiise

the field name is friend_array and of type longtext and holds numeric values example (2,5,3,10)

bradgrafelman

Ah, well there's your first problem.

You shouldn't store multiple values in a single field. Normalize your DB schema to at least the first normal form (which eliminates all multi-valued columns) by creating a separate "friends" table that likely contains only two columns; user_id and friend_id. Both are foreign keys back to your users table.

So for example, if my user ID was 100 and I wanted to friend the users whose ID's are 2, 5, 3, and 10, then you would see four tuples in this new "friends" relation: (100, 2), (100, 5), (100, 3), and (100, 10).

Not only does this follow basic SQL normalization practices, but it also makes the data much easier to work with (hence why this is a basic practice to begin with 😉).

NogDog

Or you could use [man]explode/man if you don't have time to do it "right". 😉

(I'm all for doing it right with data normalization and such, but I also know that sometimes we're up against tight schedules with legacy code and just have to get it working ASAP.)

Weedpacket

This may also be an opportunity to install an ON DELETE trigger on the myMembers table, so that the only command the application needs to send to the database is the one to remove the user, and then leave the DBMS to do the additional work of removing the corresponding ID from the user's erstwhile friends.