|
|
This topic comprises 3 pages: 1 2 3
|
|
Author
|
Topic: PHP file upload: place file information into database (HELP)
|
|
|
|
|
|
|
|
|
|
|
|
|
Scott Jentsch
Phenomenal Film Handler
Posts: 1061
From: New Berlin, WI, USA
Registered: Apr 2003
|
posted 07-28-2008 02:58 PM
quote: Andrew McCrea
My table looks like this:
profileFriends (table name)
id, person, friend, fAvatar
When someone adds someone as a friend, the user of the site is placed in "person", the friend's name is placed in "friend", and the friend's avatar path is placed in fAvatar (this is updated by the avatar upload script when the avatar is changed).
There may be a reason why you didn't do so, but it would make more sense to have all the users of your site in a table, and then use a cross-reference table that connected all the friends together.
That would keep the avatar referenced only in the users table, and it would not to be repeated in the friends xref table. Duplication is a no-no in databases and should be avoided whenever possible. It's allowable, but not good design, and it will come back to bite you somewhere down the road (usually when you least expect it).
| IP: Logged
|
|
|
|
Scott Jentsch
Phenomenal Film Handler
Posts: 1061
From: New Berlin, WI, USA
Registered: Apr 2003
|
posted 07-30-2008 11:03 AM
I'll try to illustrate the issue, which is database architecture (not PHP):
Table: users
id
name
avatar
Table: users_xref
user1_id
user2_id
So, take an example where you have three users:
1 - John Smith
2 - Jane Doe
3 - Tom Jones
Tom Jones decides to make Jane Doe and John Smith his friends, so in the users_xref table, you have a record with the following:
user1_id: 3
user2_id: 2
user1_id: 3
user2_id: 1
Now, to find all of Tom Jones' friends, you do a SQL SELECT:
SELECT users.* FROM users, users_xref WHERE users.id = users_xref.user2_id AND users_xref.user1_id = 3
This will result in you getting a list of Tom Jones' friends, their names, and their avatars that you can use to build the listing that you are trying to do.
You will obviously want to build your indexes properly so that you can optimize your queries, and tweak the queries to return the random and/or limited results you are looking for, but the gist of this is that you don't want the avatar reference in multiple tables.
Normalize data whenever possible, and it will keep your databases cleaner and prevent you from pulling your hair out when the inevitable consequences of poor design rear their ugly heads.
Good luck!
| IP: Logged
|
|
|
|
|
|
Mark J. Marshall
Film God
Posts: 3188
From: New Castle, DE, USA
Registered: Aug 2002
|
posted 08-08-2008 03:29 PM
Congrats on launching your site! And Scott makes some very good points, although it could be a little better still...
quote: Scott Jentsch
Table: users
id
name
avatar
Table: users_xref
user1_id
user2_id
So, take an example where you have three users:
1 - John Smith
2 - Jane Doe
3 - Tom Jones
Tom Jones decides to make Jane Doe and John Smith his friends, so in the users_xref table, you have a record with the following:
user1_id: 3
user2_id: 2
user1_id: 3
user2_id: 1
Assuming that when Tom adds Jane and John as friends that John & Jane also want Tom as a friend, and assuming that you don't want to duplicate records by also adding:
user1_id: 2
user2_id: 3
user1_id: 1
user2_id: 3
...the query would be:
SELECT
IF(U1.id = '3', U2.name, U1.name) Name,
IF(U1.id = '3', U2.avatar, U1.avatar) Avatar
FROM
users U1 LEFT JOIN
users_xref X ON U1.id = X.user1_id LEFT JOIN
users U2 ON X.user2_id = U2.id
WHERE
X.user1_id = '3' OR X.user2_id = '3'
ORDER BY RAND()
LIMIT 6
Note the four bold threes in that statement. When creating this query in PHP, those are where you will stick the ID of the person you're looking at in order to pull out six random friends of his. To do this though, you should add a two field primary index on the xref table for the user1_id and user2_id fields to avoid duplicates.
If on the other hand, you want your friend relationships to only work one way, then Scott's example is probably fine.
To make it even more like myspace, you could add a field to the xref table called "approved" as an enum 'y', 'n', 'p' (pending), and have the user1_id always be the person making the request. Then you could also query on friends that are awaiting approval from the other person, and people who are requesting to be the user's friend. But maybe I'm getting ahead of myself.
Cheers!
| IP: Logged
|
|
Andrew McCrea
Jedi Master Film Handler
Posts: 645
From: Winnipeg, Manitoba, Canada
Registered: Nov 2000
|
posted 08-08-2008 11:05 PM
The site works very much like a typical forum like this, facebook, and YouTube all mixed together...
People can start bands, start albums, start songs, and everyone can offer input all to create a finished song track... Its like "open-sourcing" the music industry since they keep churning out garbage that they charge for. This says "Hey, make better music by offering your input here, giving input there, and hey, maybe get some exposure while you're at it".
That's the basic gist of it, but its so subjective that its hard to define. My financial consultant (who also happens to play the axe for my recording band, ie. we don't do shows, that has used a very minimal, skimmed down thrusong, ie. a file listing directory, and the postal system to work for us) thinks this is a million dollar idea after he saw it at launch today, and I think I'll hit him up for an investment in a good/decent server. I want to look into memcached, in particular, and all sorts of open source technologies. I just know that I want to host it myself, and a lot of that is fed by wanting to learn everything I can (I set up the server by myself, I built the entire site from scratch, with help from amazing people like Mark and Scott on some of the PHP stuff that is explained poorly on the internet).
Right now you can just decide to join a song, band or album, or become a fan of a band or artist (a user of the site), so there's no confirmation needed yet, but eventually, that will definitely be needed.
So, its still a little buggy, but most baby software is, and I have lots of little ideas on how to improve the site. My next big thing is turning everything into components (especially so that the transition to making it available in multiple languages is easier), and adding AJAX.
And one major mission of the site's engineering: Make it about the content, not the site... That's one major problem with the new facebook; It feels like it wants it to be more about the brand then what everyone is up to because its too flashy, in a way.
| IP: Logged
|
|
Andrew McCrea
Jedi Master Film Handler
Posts: 645
From: Winnipeg, Manitoba, Canada
Registered: Nov 2000
|
posted 08-14-2008 12:50 AM
So far, so good.
I've started componentizing the site so that all pages are grouped in folders according to "bands", "profile", "home", etc. I think this will keep it more organized and more powerful.
All text is also now filled in as includes according to the selected language. We only have English (Canadian) right now, with English (US) and French to come soon.
Here's a general inquiry.
I'm trying to plan ahead for future expansion, etc. and was wondering how I would handle having several servers and databases.
Like, how could I have two servers, a database on one, and keep track of where files are located in the database?
How does facebook keep track of 10,000+ servers, multiple databases, etc. and keep the site running smoothly? They have something like 1800 MySQL databases...
I'm thinking what I have to start working on is being able to sync two servers so that load balancing can be used to an extent. I also want to have several partitions on each drive and several drives per server , then determine which partition has enough room, keep track of where that file is, etc.
Is there any easy way to get up and going with two servers?
| IP: Logged
|
|
Mark J. Marshall
Film God
Posts: 3188
From: New Castle, DE, USA
Registered: Aug 2002
|
posted 08-14-2008 11:14 AM
If you want to make the move to multiple servers for expandability sake, the first thing I would do is separate Apache and MySQL onto two separate boxes. Those two programs are performing different functions, and it will be helpful to have them separated out later. Consider that down the road you may have response time problems with MySQL, but Apache may be keeping up with the users just fine... then you can expand your MySQL server farm if need be without touching Apache. Also if your site will have music streaming as part of its functionality, you should split that off as its own server/farm as well for the same reasons. You might find that Apache and MySQL run just fine, but your streaming server is getting clobbered. Then you can deal with that without messing with the other two. That's my suggestion, for what it's worth.
However, if you get one good beefy server it should be able to handle Apache and MySQL just fine for a while, and may even be able to do some streaming too without too much difficulty.
A better bet might be to look at a hosting service. It's worth the money to pay someone else to worry about the hardware! Believe me, the bigger you get, the more important uptime becomes. And the more important uptime becomes, the more time and money you end up spending on infrastructure (A/C, UPS, etc.).
Just a thought.
| IP: Logged
|
|
|
|
All times are Central (GMT -6:00)
|
This topic comprises 3 pages: 1 2 3
|
Powered by Infopop Corporation
UBB.classicTM
6.3.1.2
The Film-Tech Forums are designed for various members related to the cinema industry to express their opinions, viewpoints and testimonials on various products, services and events based upon speculation, personal knowledge and factual information through use, therefore all views represented here allow no liability upon the publishers of this web site and the owners of said views assume no liability for any ill will resulting from these postings. The posts made here are for educational as well as entertainment purposes and as such anyone viewing this portion of the website must accept these views as statements of the author of that opinion
and agrees to release the authors from any and all liability.
|
Home
![[Smile]](smile.gif)
Printer-friendly view of this topic