Treehouse: Grow your CSS skills. Land your dream job.

PHP/MySQL Question

  • # March 24, 2012 at 8:28 am

    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?

    # March 26, 2012 at 12:55 pm

    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?

    # March 26, 2012 at 1:39 pm

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

    1 Joe 4
    2 Ted 4
    3 Frank 4
    4 Mike 1,5,6
    5 Eduardo 1,6
    6 Sara
    # March 26, 2012 at 1:46 pm

    @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?

    # March 26, 2012 at 1:49 pm

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

    # March 26, 2012 at 1:50 pm

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

    # March 26, 2012 at 2:13 pm

    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;

    # March 27, 2012 at 12:05 pm

    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

    # March 27, 2012 at 12:37 pm

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

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

    # March 27, 2012 at 12:47 pm

    That would work.

    # March 31, 2012 at 10:22 pm

    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`(
    ,`name` VARCHAR( 255 ) NOT NULL COMMENT 'user name'

    CREATE TABLE `relationship`(
    ,`user` INT NOT NULL COMMENT ' of user who is following'
    ,`follows` INT NOT NULL COMMENT ' of user who is being followed'

    Example records might look like this:

    id name
    1 Mark
    2 John
    3 Sally
    4 Jane

    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.).

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.