This is what I'm trying to do. I have a field 'users'. I want to put user ids into this field like so: user1~user2~user3~user4 First I need to know the code for adding to this list, for instance how to add user #5. Next I need to know how to get that data into variables that I can use. For instance say I wanna make $vuser1 = user1; $vuser2= user2; etc.. so that I can use their user id to bring up their profile link. http://site.com/?profile=$vuser1 This isn't exactly what I'm doing but it's similar... I'm trying to make a program that tracks referrals down 10 levels, each time a new member joins under someone it will add their username to the corresponding downline field of their upline. I would also like to know if there's a way to count how many users there are in the ~deliminated field -- so that I can have stats. This is my first real program, and everything else seems to be coming along well, this is my biggest hurdle so far..
Well... you would have to get the value from the database, and then add the last user to that value, something akin to the following (pseudocode): $connect_to_database; $query database $users = $query['users']; get the latest user to add to the database, lets use the variable $latest_user; $updated = $users."~".$latest_user $insert into database; users = '$updated' That would take care of the update of the users-field you can explode the users field, like the following: $user = explode("~",$users); $i=1; foreach ($user as $value) { $user{$i} = $value; $i++; } for instance (again, pseudocode, it's just to get you starting).
To update: $q = "UPDATE table SET field = CONCAT(field, '~newuser') WHERE what = this"; PHP: Although I should point out that it's a bad way of doing it ;D Each cell in your tables should have only one value, instead of (like you have) a list of multiple values.
Can you think of a better method of setting up a multi-level referral program? I'm open to suggestions - I want to be able to show the username of downline members down 10 levels, and also a numerical stats for each level. so far my db is like this: Table = Users Fields = ID, username, pass, email, sponsor 1, sponsor 2, sponsor 3, sponsor 4, sponsor 5, sponsor 6, sponsor 7, sponsor 8, sponsor 9, sponsor 10, down 1, down 2, down 3, down 4, down 5, down 6, down 7, down 8, down 9, down 10 Where down1 would be user1~user2 and so on... I was just wondering - would there be another way of doing it, maybe using another table? What if I did something like Table: Level 1, Fields: sponsor, referral I could then pull all level 1 referrals where sponsor = user... I'm trying to visualize if this would work better... I could definitely use suggestions - do you think the first scenario or the second would work better?
you can use concat_ws() to append to a field with a separator. To count the number of elements in that field just create a separate field that gets incremented every time you add an entry.
I've come up w/ a good newbie method of doing this, but as far as db optimization, I'm afraid this might be taxing -- if anyone can suggest a better way - I'd much appreciate it.... Table: Tracking Fields: Sponsor, Ref, Level When a member registers it will create 10 new rows in tracking. E.G.: Row 1: Sponsor 1, Ref1, level 1 Row 2: Sponsor 2, Ref1, level 2 Row 3: Sponsor 3, Ref1, level 3 Row 4: Sponsor 4, Ref1, level 4 Row 5: Sponsor 5, Ref1, level 5 .....etc... Then the next member: Row 6: Sponsor 1, Ref2, Level 1 Row 7: Sponsor 2, Ref2, Level 2 etc.... Then to show the stats I would do mysql like so: $result = mysql_query("SELECT ref FROM tracking WHERE sponsor='sponsor1' AND level='1'") or die(mysql_error()); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo $row['ref']; echo " | "; } Code (markup): Which would return: referral1 | referral 2 I can see this creating a huge number of db entries, and am wondering if there's an easier way that I'm not thinking of.