Best way to store relationship/friend/following info in social networking script

[adambedford]

I'm writing a pretty simply PHP social networking site. It's pretty similar to twitter I guess but it will be quite different once it's done.

 

My problem is that I don't know the best way to store the friend/following information. I've thought of three ways:

• Each user has a table and each record stores the user ID of the person they follow
  
• I have a table for relationships that has a field for user1, a field for relationship (ie. following) and a field for user2 - the person user1 is following
  
• I have a table of users storing all their information and a field called 'following' where all the user IDs of the people that user is following - perhaps comma separated. The problem with this is I wouldn't know how to parse this in PHP so that I can query the information
  

 

Any suggestions would be really great.

[eli312]

First I'm just going to say if you don't know how to do this with out asking i guess your going to have a few problems.

 

Secondly answering your question.

What i would do is make a table (call it whatever you want) and then link the members id to freinds id. But that depends on how big your site going to get.

 

[ohdang888]

I have a table for relationships that has a field for user1, a field for relationship (ie. following) and a field for user2 - the person user1 is following

 

That one is the best

[adambedford]

OK thanks for the suggestions. I kind of suspected that would be the best solution....but I think it also might be the hardest to work with!

 

To then work with the list of friends each user has, would it be best to query the database (i can manage that part) and add the resulting list to an array? If so, how would I go about that.

 

Essentially, I want to be able to display a list of friends (that I can do - I think!) and then use that list of friends to retrieve status updates from another table.

 

Thanks

[aeroswat]

I'm writing a pretty simply PHP social networking site. It's pretty similar to twitter I guess but it will be quite different once it's done.

 

My problem is that I don't know the best way to store the friend/following information. I've thought of three ways:

• Each user has a table and each record stores the user ID of the person they follow
  
• I have a table for relationships that has a field for user1, a field for relationship (ie. following) and a field for user2 - the person user1 is following
  
• I have a table of users storing all their information and a field called 'following' where all the user IDs of the people that user is following - perhaps comma separated. The problem with this is I wouldn't know how to parse this in PHP so that I can query the information
  

 

Any suggestions would be really great.

 

Explain how you need to parse it? If this is a delimited list there are many different functions you can use in php. Functions such as explode used like this:

 

explode(";",$string)

This would take a list like this red;green;blue;orange

and put it in an array like this array[0] = red, array[1] = green, etc

[aeroswat]

OK thanks for the suggestions. I kind of suspected that would be the best solution....but I think it also might be the hardest to work with!

 

To then work with the list of friends each user has, would it be best to query the database (i can manage that part) and add the resulting list to an array? If so, how would I go about that.

 

Essentially, I want to be able to display a list of friends (that I can do - I think!) and then use that list of friends to retrieve status updates from another table.

 

Thanks

 

if you need to load the records into an array do something like this

 

$arr = array();
$result = mysql_query("bla bla");

while($row = mysql_fetch_assoc($result)) {
   $arr[] = $row;
}

[ignace]

Take a look at my post http://www.phpfreaks.com/forums/index.php/topic,281823.msg1335964.html#msg1335964 It features tweets and people following people

 

 

if you need to load the records into an array do something like this

 

$arr = array();
$result = mysql_query("bla bla");

while($row = mysql_fetch_assoc($result)) {
   $arr[] = $row;
}

 

You can write that even shorter:

 

$rows = array_map('mysql_fetch_assoc', array_fill(0, mysql_num_rows($result), $result));

 

 

[adambedford]

Thanks for your help.

 

I've not got my query to return all the User_ID's of the people that the user is following. With this list of ID's, I want to retrieve the other information about the user from the table 'business_owner' (i've split the members into two tables based on whether they are a user or a business owner)

 

Can I query against a list of ID's? ie. If I put the list of business ID's that are being followed into an array, can I query against that? If so, how would I go about it. If not, does anyone have any suggestions as to how I achieve something similar?

 

Again, thanks for the help guys!

[aeroswat]

Thanks for your help.

 

I've not got my query to return all the User_ID's of the people that the user is following. With this list of ID's, I want to retrieve the other information about the user from the table 'business_owner' (i've split the members into two tables based on whether they are a user or a business owner)

 

Can I query against a list of ID's? ie. If I put the list of business ID's that are being followed into an array, can I query against that? If so, how would I go about it. If not, does anyone have any suggestions as to how I achieve something similar?

 

Again, thanks for the help guys!

 

SELECT * FROM tbl WHERE id IN ('1','2','3',etc)

[adambedford]

Thanks. Can '1','2','3' etc be an array? Because I won't know the exact values?

[aeroswat]

Thanks. Can '1','2','3' etc be an array? Because I won't know the exact values?

 

No I don't believe so. You will want to add it to your query string. So do something like this

 

$qry="SELECT * FROM tbl WHERE id IN (";

if(count($arr)) {
foreach($arr as $a){
   $qry .= "'" . $a['id'] . "',";
}
$qry = substr($qry,0,-1);
} else{
  $qry .= "''";
}
$qry .= ")";

[adambedford]

Ahhhh this is so confusing!

 

I haven't quite made it to that stage yet unfortunately. I'm stuck trying to add the the results of the first query to the array.

 

$colname_Relationships = "-1";
if (isset($_SESSION['ID'])) {
  $colname_Relationships = $_SESSION['ID'];
}
mysql_select_db($database_links, $links);
$query_Relationships = sprintf("SELECT * FROM relationships WHERE User_ID = %s AND Relationship = 'Following'", GetSQLValueString($colname_Relationships, "int"));
$Relationships = mysql_query($query_Relationships, $links) or die(mysql_error());
$row_Relationships = mysql_fetch_assoc($Relationships);
$totalRows_Relationships = mysql_num_rows($Relationships);

//Add the results of the above query ^^^^^ to an array, $arr

$arr = array();
$result = mysql_query($query_Relationships, $links);

while($row = mysql_fetch_assoc($Relationships)) {
   $arr[] = $row;
}

 

I tried echoing $arr and all it says is 'Array'

 

I'm getting myself rather confused with all this, but hopefully I'll learn for future projects!

[aeroswat]

Ahhhh this is so confusing!

 

I haven't quite made it to that stage yet unfortunately. I'm stuck trying to add the the results of the first query to the array.

 

$colname_Relationships = "-1";
if (isset($_SESSION['ID'])) {
  $colname_Relationships = $_SESSION['ID'];
}
mysql_select_db($database_links, $links);
$query_Relationships = sprintf("SELECT * FROM relationships WHERE User_ID = %s AND Relationship = 'Following'", GetSQLValueString($colname_Relationships, "int"));
$Relationships = mysql_query($query_Relationships, $links) or die(mysql_error());
$row_Relationships = mysql_fetch_assoc($Relationships);
$totalRows_Relationships = mysql_num_rows($Relationships);

//Add the results of the above query ^^^^^ to an array, $arr

$arr = array();
$result = mysql_query($query_Relationships, $links);

while($row = mysql_fetch_assoc($Relationships)) {
   $arr[] = $row;
}

 

I tried echoing $arr and all it says is 'Array'

 

I'm getting myself rather confused with all this, but hopefully I'll learn for future projects!

 

you can't simply echo the array. The way you would access the array is like this...

echo $arr[index]['column_name'];

 

So if you wanted to access the id field of the first record you would do this

 

echo $arr[0]['id'];

 

Also just curious but what's the reason for using sprintf() when creating your query?

[adambedford]

I'm using Dreamweaver CS4 and it automatically adds sprintf to the query...I'm not actually sure why it does that.

 

I've run into another problem now though...I'm getting error messages from MySQL.

 

The code I'm using is this:

 

//Add the results of the above query ^^^^^ to an array, $arr

$arr = array();
$result = mysql_query($query_Relationships, $links);

while($row = mysql_fetch_assoc($result)) {
   $arr[] = $row;
}

mysql_select_db($database_links, $links);
$query_Business_owners="SELECT * FROM business_owners WHERE ID IN (";

if(count($arr)) {
foreach($arr as $a){
   $query_Business_owners .= "'" . $a['ID'] . "',";
}
$query_Business_owners = substr($query_Business_owners,0,-1);
} else{
  $query_Business_owners .= "''";
}
$query_Business_owners .= "))";



$Business_owners = mysql_query($query_Business_owners, $links);
$row_Business_owners = mysql_fetch_assoc($Business_owners);
$totalRows_Business_owners = mysql_num_rows($Business_owners);

 

And the errors I'm seeing on the screen are:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/hiperlynx.com/httpdocs/hl_user/account/index.php  on line 98

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/hiperlynx.com/httpdocs/hl_user/account/index.php on line 99

 

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/hiperlynx.com/httpdocs/hl_user/account/index.php on line 131

 

I'm really not sure why this is happening. Surely there is a simpler way to achieve what I want to do...

 

Essentially I need to get a list of all the businesses that a user is 'following'. (i'm then going to use that to get status updates...but that part comes later). So I've gone about it by retrieveing a list of Business_IDs but then that is no use as I need the actual name from another table, so I'm trying to use this array to retrieve the name.

 

Surely there is a simpler way using a JOIN or something? It's very user specific because I retrieve the list of IDs using the username of the logged in user (which is a session value)

 

Thanks for your advice. Any thoughts?

[aeroswat]

Those errors are basically telling you the way you are defining $result is not correct. Check the syntax of your SQL statement. The easiest way to check exactly what is queried is by echo'ing the qry string right before you define the $result

[adambedford]

They seem to be to do with the final three lines:

$Business_owners = mysql_query($query_Business_owners, $links);
$row_Business_owners = mysql_fetch_assoc($Business_owners);
$totalRows_Business_owners = mysql_num_rows($Business_owners);

 

Because when I comment the lines out, the error goes away but I can't use the data because I've commented some vital parts out.

[aeroswat]

They seem to be to do with the final three lines:

$Business_owners = mysql_query($query_Business_owners, $links);
$row_Business_owners = mysql_fetch_assoc($Business_owners);
$totalRows_Business_owners = mysql_num_rows($Business_owners);

 

Because when I comment the lines out, the error goes away but I can't use the data because I've commented some vital parts out.

 

The reason you no longer get errors when you comment those out is because you are no longer using the result from the query. Trust me. It's a problem with your query that you are running. echo your query and you will find your problem

[Andy-H]

Use implode...

 

//Add the results of the above query ^^^^^ to an array, $arr

$arr = array();
$result = mysql_query($query_Relationships, $links);

while($row = mysql_fetch_assoc($result)) {
   $arr[] = $row['ID'];
}

mysql_select_db($database_links, $links);
$query_Business_owners="SELECT * FROM business_owners WHERE ID IN (" . implode(',', $arr) . ")";

$Business_owners = mysql_query($query_Business_owners, $links);
$row_Business_owners = mysql_fetch_assoc($Business_owners);
$totalRows_Business_owners = mysql_num_rows($Business_owners);

[adambedford]

I've been thinking...do I even need to use an array or can I just use JOINs in the original query to link everything together?

I'm really struggling to get my head around how it's all going to tie together to be honest...