Exploding in php

Question

In my table 'users' there are 'friends' ,

Like this :

+----+------+---------+
| id | name | friends |
+----+------+---------+
|  1 | a    | 0,1,2   |
|  2 | b    | 0,1,3   |
|  3 | c    | 0,1     |
+----+------+---------+

How do I use the explode function to get the friends id one by one (not 0,1,2) that are separated by a comma (,) ;

How do I select the id? (Example) :

$sql = Select id from users where id = (exploded)

if (mysql_num_rows($sql) > 0 ) {
  $TPL->addbutton('Unfriend');
}else{
  $TPL->addbutton('Add as Friend')
}

Noooooooooo!!!! Normalize your database properly.... otherwise you're guaranteeing yourself and anybody else that ever needs to look at your system to a lifetime of pain and anguish.... not to mention the fact that a kitten died the moment you made the decision to store all of the friends data in a column in your user table — Mark Baker
– Mark Baker, Commented Jan 22, 2013 at 10:22
Also, be aware that the mysql_xxx() functions are obsolete and insecure. The are deprecated and not recommended for use. You should change your code to use either mysqli or PDO instead as soon as you can. — SDC
– SDC, Commented Jan 22, 2013 at 10:25

Lix · Accepted Answer · 2013-01-22 10:41:00Z

5

The solution here is actually a slight change in your database structure. I recommend you create a "many-to-many" relational table containing all of the users friends referenced by user.

+---------+-----------+
| user_id | firend_id |
+---------+-----------+
|       1 |         2 |
|       1 |         3 |
|       1 |         4 |
|       2 |         1 |
|       2 |         5 |
+---------+-----------+

If you are storing lists of values within one field then that is the first sign that your database design is not quite optimal. If you need to search for a numerical value, it'll always be better to place an index on that field to increase efficiency and make the database work for you and not the other way around :)

Then to find out if a user is a friend of someone, you'll query this table -

SELECT * FROM users_friends WHERE 
  `user_id` = CURRENT_USER AND `friend_id` = OTHER_USER

To get all the friends of a certain user you would do this -

SELECT * FROM users_friends WHERE `user_id` = CURRENT_USER

edited Jan 22, 2013 at 10:41

answered Jan 22, 2013 at 10:23

Lix

48.2k12 gold badges107 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SDC Over a year ago

+1 although I'd suggest also having a unique ID field for this table as well as the two linking ID fields.

Lix Over a year ago

@SDC - an id field would be a bit redundant here. You would never really use it for anything. All you would have to do is make sure the two fields are unique together so that you prevent duplicates.

Tommaso Belluzzo · Accepted Answer · 2013-01-22 10:19:18Z

0

Just a simple example that will make you clear how to proceed:

// Obtain an array of single values from data like "1,2,3"...
$friends = explode(',', $row['friends']);

Then, back in your query:

// Obtain back data like "1,2,3" from an array of single values...
$frieldslist = implode(',', $friends);
$sql = "SELECT * FROM users WHERE id IN ('" . $frieldslist . "')";

answered Jan 22, 2013 at 10:19

Tommaso Belluzzo

23.7k8 gold badges77 silver badges102 bronze badges

1 Comment

Muhd Nazmi Over a year ago

Can we implode(seperate) a things that have been exploded(remove comma)

Pedro del Sol · Accepted Answer · 2013-01-22 10:20:20Z

0

to get an array of if ids from your string explode would be used like this

$my_array = explode("," , $friends);

but you'd probably be better using the mysql IN clause

$sql = "Select id from users where id in (".$row['friends'].")";

answered Jan 22, 2013 at 10:20

Pedro del Sol

2,8419 gold badges43 silver badges52 bronze badges

2 Comments

SDC Over a year ago

the IN clause is fine, as long as the contents of the friends field is guaranteed to contain only comma separated numeric values. If there's even the slightest possibility of contamination, then a SQL string like this could be an opportunity for a SQL injection attack. Even if you think it is safe, you'd be creating a situation where if an attacker could get an unsafe value into it, it would open you up to further attack. Because of that, I'd say that it would be best not to use IN directly on the field. It's better to code defensively and avoid even potential attacks.

Pedro del Sol Over a year ago

of course, you are correct; i was really just directing the op to the in clause to get mysql to do some of the work. be vigilant kids!

user1400718 · Accepted Answer · 2013-01-22 10:34:32Z

0

Just a quick idea. Change your database's table. It is certain that after a while many problems will arise.

You could have something like this.

    id      hasfriend
    1          2
    1          3
    2          1 no need to be here (You have this already) 
    2          4 
       .....

You can do this by using indexes for uniqueness or programming. You may think of something better. Change your approach to the problem to something like this.

answered Jan 22, 2013 at 10:34

user1400718

Collectives™ on Stack Overflow

Exploding in php

4 Answers 4

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related