Forums

schart

How can I output and input arrays like ('this', 'this', 'and this') into a database, like say I want some kind of a following system, and I have "Following" and "Followers" in the database of every user. I could go "Following->Somename, Some other name, Some other name", and than get those in a list when needed?

Schmotty

I may not be the best to advise on this, but from what I have seen before in database designs, I would do something like creating a table with two columns. The first would be follower and the second would be followee:

Follower		Followee
joe	->	mike
ted	->	mike
frank	->	mike
mike	->	ted
mike	->	sara
mike	->	eduardo
eduardo	->	sara
eduardo	->	joe

then when you pull it from the db you need to order by follower and use a nested loop to apply your code to all the followees of that follower before moving on to the next follower.

Does that make sense?

karlpcrowley

Each row needs a unique ID so use that to your advantage

ID	NAME	FOLLOWING
1 	Joe	4	
2 	Ted	4
3 	Frank	4
4 	Mike	1,5,6
5 	Eduardo	1,6
6	Sara

schart

@Schmotty - I kind of see it, like a whole table where I can pull out "where followed = '1'", and then several can be following him? Nice idea.

@karlpcrowley - Yes of course an unique ID, I get that but how would I get 1,5,6 in an array and then get each induvidual?

karlpcrowley

You could use the explode() function with the comma as a delimiter, this will turn it into an array

schart

Example with $string as "1,5,6", please? :)

karlpcrowley

It all depends on how you are going to use the data

Lets say you are using a while loop to get each of the rows from the database
Inside the while loop you will need to do the explode

Lets says $row[0] = 4; $row[1] = "Mike"; $row[2] = 1,5,6;
You can do

$following = explode(',', $row[2]);

then $following[0] = 1; $following[1] = 5; $following[2] = 6;

Schmotty

In this case it may be better to use the id from each person profile (if you are creating one), then you can reference everything about that person from their id (name, location, who they are following, etc).

then your following table would just be



ID	 Following
1	 4
2	 4
3	 4
4	 1,5,6
5	 1,6

Notice that it excludes anyone who isn't following someone

schart

@karlpcrowley & @Schmotty - I understand. So basically I will insert like $oldfollow = **

$newfollow = $oldfollow + ",1";
if the id is 1.

Schmotty

That would work.

traq

this sort of thing:

4 	Mike		1,5,6

is a bad database design. You shouldn't store multiple pieces of information (i.e., lists or arrays) in a single DB field: it defeats the purpose of using a database. It also makes reading/maintaining the info more difficult - and therefore, more error-prone.

Make tables for your users and relationships (note each relationship is one-to-one)


CREATE TABLE `user`(
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'unique user id'
 ,`name` VARCHAR( 255 ) NOT NULL COMMENT 'user name'
);

CREATE TABLE `relationship`(
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'row id (index)'
 ,`user` INT NOT NULL COMMENT 'user.id of user who is following'
 ,`follows` INT NOT NULL COMMENT 'user.id of user who is being followed'
);

Example records might look like this:


user
id      name
1       Mark
2       John
3       Sally
4       Jane

relationship
id      user    follows
1       1       2
2       1       4
3       2       4
4       3       1
5       3       4

And you can find relationships like this:

<?php

$userID = 1;
$SQL = "SELECT `u`.name` 
    FROM `user` `u`
    JOIN `relationship` `r`
    WHERE `r`.`user` = $userID
    AND `u`.`id` = `r`.`follows`";

// assuming mysqli $DB
$result = $DB->query( $SQL );
if( $result && $result->num_rows >0 ){
    while( $name = $result->fetch_row() ){
        $MarkFollows[] = $name;
    }
    print "Mark follows: ".implode( ", ",$MarkFollows );
// prints:
//    Mark follows: John, Jane
}

No need to explode; all the data is returned from the DB in the form it is needed. This structure also allows your script (now or in the future) to search the data in almost any other way (for example, finding "folowers" is exactly as easy as finding "folowees" - you could even find who is followed most, least, who has the most shared followers, etc.).

treehouse : what would you like to learn today?

Forums

PHP/MySQL Question