10 replies to this topic

[thatguyandrew1992]

Hey all,
I recently started learning some PHP and MySQL and I need a guide for good database setups, I suppose.

What I want to do is add the ability to have users register on my site, and have them save links on my website. They could save thousands of links.

I could make a database with their usernames, passwords and salts, then I was thinking I could have another database with their usernames and possibly another area in the database that could save the links, but how would they be saved?

[+articuno1au]

Login
--
int[UserID]
string[Username]
string[Password]
string[email]
...

Links
--
int[GUID]
int[UserID]
string[Link]

Then link the tables by UserID. You need the GUID in case a user adds the same link twice and because it's a neater MasterKey than UserID|Link.

That ought do the trick

Also, just for the sake of all of us speaking the same language, you store the info in Tables inside of a Database. You generally don't have more than one database for a project of this kind >.< Not bitching you out or anything, just thought it might help to mention

[thatguyandrew1992]

So each link is in the table. So it could be like (User1 - Link1) -> (User2 - Link 1) -> (User1 - Link 2) -> (User1 - Link 3) -> (UserX - Link Y)?
Then when a user loaded their link page, it would go through the table and list all the links have match with the user?

Thanks for the database tip!

[+articuno1au]

Yeah, so example data:
Login
--
1,articuno1au,password,sexypants@hotmail.com
2,thatguyandrew,lolcats,codepants@gmail.com

Links
--
1,1,http://www.hotmail.com
2,1,http://www.wocserver.org
3,2,http://www.shayconcepts.com
4,1,http://www.forums.wocserver.org
5,2,http://www.facebook.com

Then:

SELECT Link
FROM Links
WHERE UserID in (SELECT UserID
FROM Login
WHERE Username = "articuno1au")

Would return:
Hotmail, wocserver, forums.wocserver

Putting your username in would return:
shayconcepts, facebook

In the links table, the information is collected by UserID not GUID (Which is there just to serve as a Primary Key )

Sound good?

Just as a side note, for performance reasons, you are better off using JOIN to link the tables when querying. MySQL doesn't like embedded/subquery queries >.<

[thatguyandrew1992]

This is great! Thanks! Hopefully after exams I can get some time to implement this.

[YounGMessiah]

FYI stackoverflow is a good resource
When you start getting more comfortable you will start finding ways to make the tables optimal for each application use
Practice always helps!
actually looking at others applications/programs/scripts or however you wish to pass it on as helps tons!

http://en.tekstenuit...rial/intro.html
http://www.deeptrain...baseDesign.aspx

Those seem like decent articles

Good luck!

[thatguyandrew1992]

YounGMessiah, on 01 March 2013 - 18:23, said:

FYI stackoverflow is a good resource
When you start getting more comfortable you will start finding ways to make the tables optimal for each application use
Practice always helps!
actually looking at others applications/programs/scripts or however you wish to pass it on as helps tons!

http://en.tekstenuit...rial/intro.html
http://www.deeptrain...baseDesign.aspx

Those seem like decent articles

Good luck!

Thanks!

[AWKM]

The bit that requires special attention is securing the scripts that are running.

Having logins and submitting new links equals web forms and the use of GET and POST so you'll be wanting to do a bit of work to make sure your DB queries and inserts are secured from iffy SQL injection or other dodgy user inputs. Don't just think mysqli_real_escape_string will do the job...it'll likely not even work unless you've covered the configuration of the host in advance.

Working for a web design/development company I've seen a few rather farcical examples of "content management systems" that so called developers (created by others outside our company) have written in PHP that miss or just ignore these risks.

I'm in the middle of writing my own (super simple) blog and even though it's literally bare-bones (has no comments system yet and probably wont) and a few pages to list the articles/archive it's something I have to pay attention to as well.

[thatguyandrew1992]

technikal, on 02 March 2013 - 06:10, said:

The bit that requires special attention is securing the scripts that are running.

Having logins and submitting new links equals web forms and the use of GET and POST so you'll be wanting to do a bit of work to make sure your DB queries and inserts are secured from iffy SQL injection or other dodgy user inputs. Don't just think mysqli_real_escape_string will do the job...it'll likely not even work unless you've covered the configuration of the host in advance.

Working for a web design/development company I've seen a few rather farcical examples of "content management systems" that so called developers (created by others outside our company) have written in PHP that miss or just ignore these risks.

I'm in the middle of writing my own (super simple) blog and even though it's literally bare-bones (has no comments system yet and probably wont) and a few pages to list the articles/archive it's something I have to pay attention to as well.

That's great advice, thanks!

[hjf288]

Teknikal you plan on open sourcing it?

[AWKM]

hjf288, on 02 March 2013 - 20:28, said:

Teknikal you plan on open sourcing it?

well it's nothing fancy. literally 1 db table. just suits my own needs. i don't like using things like wordpress as they are too bloated with all sorts of features i don't need. with my own approach I can keep it minimal, fast and I can focus on the content and SEO for titles, descriptions etc. more of a learning exercise really. i find when you use other tools people tend to neglect the important stuff.

all i need is a list of recent articles on the index, a full archive of all my articles and a page that displays each individual article when you click on it and not much else.

i might get round to doing a basic commenting system but it might be easier just to paste in something like "disqus" and let it do it. still have to do a web form to allow me to make posts but I might not even bother with that as it's just me...I can just do it directly in phpmyadmin and achieve the same result. i don't imagine a very basic search function will be difficult but I'm not really sure how best to do that yet.

also have to figure out some URL rewriting as entry.php?id=1 isn't much use. i decided to include a "slug" column in my database so I might use "my-super-cool-blog-post-title" or something to that effect and use it in the URL rather than the ID.

when it's done I can explain how i made it and what it does I guess.