Jump to content

Question

Posted

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?

Share this post


Link to post
Share on other sites

10 answers to this question

  • 0

Posted

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 :)
1 person likes this

Share this post


Link to post
Share on other sites
  • 0

Posted

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! :D

Share this post


Link to post
Share on other sites
  • 0

Posted

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:[CODE]
SELECT Link
FROM Links
WHERE UserID in (SELECT UserID
FROM Login
WHERE Username = "articuno1au")
[/CODE]
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 >.<
1 person likes this

Share this post


Link to post
Share on other sites
  • 0

Posted

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

Share this post


Link to post
Share on other sites
  • 0

Posted

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.tekstenuitleg.net/articles/software/database-design-tutorial/intro.html
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Those seem like decent articles

Good luck!

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='YounGMessiah' timestamp='1362162214' post='595552098']
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!

[url="http://en.tekstenuitleg.net/articles/software/database-design-tutorial/intro.html"]http://en.tekstenuit...rial/intro.html[/url]
[url="http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx"]http://www.deeptrain...baseDesign.aspx[/url]

Those seem like decent articles

Good luck!
[/quote]
Thanks!

Share this post


Link to post
Share on other sites
  • 0

Posted

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.
2 people like this

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='technikal' timestamp='1362204609' post='595553030']
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.
[/quote]
That's great advice, thanks!

Share this post


Link to post
Share on other sites
  • 0

Posted

Teknikal you plan on open sourcing it?

Share this post


Link to post
Share on other sites
  • 0

Posted

[quote name='hjf288' timestamp='1362256101' post='595554008']
Teknikal you plan on open sourcing it?
[/quote]

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.