Lil Amy Welcome aboard :-)
Ok... so if iv'e got this right, what your trying to work out is the best way of storing a list of friends in a database table, and you need to reference multiple people in one field?
If i'm on the right track, then the best way to go about it is as follows:
Design your database table like this......
ID : Integer, Primary-Key, Auto-Increment, Unique
Name : Varchar(100)
Password : Varchar(100)
Other fields you need....
Ok the important field is this one...
Friends : Text or Varchar(255) if you think it will be big enough..
The Friends filed needs to be big enough to hold as many integer,integer, entries as you anticipate being friends, example.
We have
1 lil_amy
2 shawty
3 steve
4 jane
5 andrew
6 lisa
If lil_amy was friends with shawty, jane and andrew then the friends field would be set to
2,4,5
in order to find the list of friends, you'd retrieve the field then use php's split function to make a list of numbers, which you would then look up by using
SELECT * FROM people WHERE ID=2 (or 4 or 5) for each person.
To make the string to store in the database, you could then use PHP's join function "join(",",$friendsarray)" and that will join the elements of the array back into "2,4,5" as a string.
Ok thats the theory...... an actual code example will probably help...
DB Table:
username VARCHAR(255) – the user’ username
password VARCHAR(255) – the user’s password
interests VARCHAR(255) – the user’s interests
birthday VARCHAR(255) - the user’s birthday in a format like “June 6 1970”
friends VARCHAR(255) - the friends list
PHP Code:
NOTE: i'm not going to write the Database connect and setup code, as there are tons of examples on the net. I'll just show the SQL statments.
function get_friends_list($person)
{
$query = "SELECT friends FROM people WHERE ID = '".$person."'";
$result = mysql_query($query);
if(!$line = mysql_fetch_row($result))
return ""; // Nothing found, return empty string.
else
return $line[0]; // Otherwise return the string we got from the database
}
function put_friends_list($friends_list,$person)
{
$query = "UPDATE people SET friends='".$friends_list."' WHERE ID = '".$person."'";
$result = mysql_query($query);
}
function friends_to_array($friends_list)
{
return split(",",$friends_list);
}
function array_to_friends($friends_array)
{
return join(",",$friends_array);
}
function get_person($person)
{
$query = "SELECT * FROM people WHERE ID = '".$person."'";
$result = mysql_query($query);
if(!$line = mysql_fetch_row($result))
return null; // Nothing found, return empty string.
else
return $line; // Otherwise return the string we got from the database
}
$friends = get_friends_list($mainperson);
$farray = friends_to_array($friends);
foreach($farray as $person)
{
$person_array = get_person($person);
// Do something with the contents of person array
}
$farray[] = 3; // Add a new friend ID
$friends = array_to_friends($farray);
put_friends_list($friends,$mainperson);
The underlying Idea is to store a string with a common seperator and a list of indexes, then manipulate them rather than full records.
Hope thats of some help.
Cheers
Shawty
