[p0is0n]

Hi,
I am thinking of redesigning my website.
It is a computer programming portfolio site.

My web programming knowledge is limited so please excuse my ignorance of maybe overlooking things.

What I am planning is having a projects database that contains information on the project like name, language, overview, links to images and source etc...

I want a visitor to be ale to click a link like 'view by language'
This will bring up a page listing all programming languages (obtained by searching through all projects and adding each new occurrence of a language to a list).

When the language is selected the database is searched again this time displaying every project to the page that has that language specified in the language field.

Basically the idea is that the projects can be browsed using a variety of searches like language, api, genre, type (game or app) and the pages containing the results can be automatically generated. Even if it is by using a php include for a pre-defined preview of the project.
I just want a nice easily searchable content driven site. So for example if I added a new entry to the database the site would basically update itself instead of me having to go through and manually edit all links and add to several different pages.

The website will be made using xhtml, css, php, and javascript using mySQL for the database(s)


My questions are :
1. Is this possible?
2. Is this a decent way of accomplishing what I want?
3. Are there any better ways of doing this that I might not be aware of?
4. Comments/Suggestions

Obviously if any more information is required please let me know.
Thanks in advance!

[jrm402]

This is a very possible website. There are sites all over the internet that do things like this.

I think your going along the right track. Using PHP/MySQL to query the database and pull the correct information is how I would do it too. Just make sure you really plan out your database tables because it's not easy to go back and edit your whole site after you decide to modify the table structures.

I would sit down first and think of how many categories you want the viewers to be able to organize by and include those in the database.

Overall you are starting off on the right foot in my eyes.

[p0is0n]

jrm402, on 08 April 2010 - 06:16 AM, said:

This is a very possible website. There are sites all over the internet that do things like this.

I think your going along the right track. Using PHP/MySQL to query the database and pull the correct information is how I would do it too. Just make sure you really plan out your database tables because it's not easy to go back and edit your whole site after you decide to modify the table structures.

I would sit down first and think of how many categories you want the viewers to be able to organize by and include those in the database.

Overall you are starting off on the right foot in my eyes.

Nice to know I'm going in the right direction lol.
And thanks for the tip on planning the database properly first.
I'll probably set up a small test page or two to see if I can get it right before I start properly doing the site.

Thanks again.

[p0is0n]

Hi again.

I'm designing my sql database and was looking for a bit of advice.

I have a project table which will contain :
ProjectID int - the auto incremented project id
Name varchar(50) - the name of the project
Language varchar(25) - the language it is programmed in
Features text - the main features of the project
Screenshots ??? - the screenshot links
Videos ??? - the video links
Downloads ??? - the download links and what they link to (source, app, library)
Notes - text - any important notes about the project (maybe a list of entries)
DateUploaded - date
LastModified - date
Changelog - tiny text - file link

Obviously that is just the fields I have come up with so far.
Is this looking okay at the moment?

How should data that can have multiple entries per item be stored?
For example one project has one or more screen shots (unknown number).
Should they be stored as a list in text, an array (such a thing in mysql?), or another table (potential to generate a lot), in a text file linked from the entry?
Or just set a maximum of 10 and have screen1, screen2 etc... in the project table?
What is the best way to store this type of data?

Thanks again!

This post has been edited by p0is0n: 09 April 2010 - 02:16 AM

[jrm402]

Looking good. Good to see you took the advice . You can always have another table called screenshots with these fields:

screenshot_id - int(11) - the auto_increment primary index
project_id - int(11) - the project id from the related project
sreenshot - text - the link to the screenshot

Then you can insert as many screenshots as possible. Once you know the project id you can pull all the screenshots with that project id. (It's good practice to always have an auto_increment in your tables, even if you never plan on using it).

You can use this same concept for anything you might have more than one of - videos, downloads, etc.

One thing I would recommend though, the DateUploaded and LastModified fields you might want to consider making integer fields and just storing a unix timestamp. A unix timestamp is the number of seconds since Jan 1st, 1970 at midnight. It is just easier to work with instead of an actual date. If you wanted to output a week before you just subtract a weeks worth of seconds from it.

If you ever wanted to output it, you can do it with the date() function:

echo date("M-d-Y", $unix_timestamp_from_db);

Hope all this helps!

[p0is0n]

jrm402, on 09 April 2010 - 06:31 AM, said:

Looking good. Good to see you took the advice . You can always have another table called screenshots with these fields:

screenshot_id - int(11) - the auto_increment primary index
project_id - int(11) - the project id from the related project
sreenshot - text - the link to the screenshot

Then you can insert as many screenshots as possible. Once you know the project id you can pull all the screenshots with that project id. (It's good practice to always have an auto_increment in your tables, even if you never plan on using it).

You can use this same concept for anything you might have more than one of - videos, downloads, etc.

One thing I would recommend though, the DateUploaded and LastModified fields you might want to consider making integer fields and just storing a unix timestamp. A unix timestamp is the number of seconds since Jan 1st, 1970 at midnight. It is just easier to work with instead of an actual date. If you wanted to output a week before you just subtract a weeks worth of seconds from it.

If you ever wanted to output it, you can do it with the date() function:

echo date("M-d-Y", $unix_timestamp_from_db);

Hope all this helps!

Thanks,

I see what you mean about the unix timestamp, that will make searching and ordering by date a lot easier.

Also the extra tables idea and structure looks good.

Thanks again!

[p0is0n]

Ok, I've been looking at the structure and I think I've got it, or made it needlessly complicated lol.

The way I see it :
Project 1 to many :
Screenshots
Videos
Downloads
Changes (changelog)

Many to Many :
Categories (big projects)
Languages (big projects)
Download Types (source, lib, app, etc...)
Types (app, game, utility, web app, etc...) (can be more than one if ported)
Platform (web, windows, linux) (can be more than one if ported)

I have written out mock-up tables for the database below (think I got everything, but let me know if there might be a useful addition I haven't thought of)
I guess I'm just not sure if I'm still going in the right direction or veered drastically off course lol.

If someone could have a quick look at these and let me know if I'm still on track it would be much appreciated.
Sorry for all the questions but I figure if I get the design right now then the programming should be easy
Thanks!
[All tables listed below]

Database :
Projects Table

ID - int
Name - text
Overview - text
Features - text
UserGuide - text
Notes - text
Documentation - text (link)
MoreInfo - text (link)
DateUploaded - int
DateLastModified - int

Screenshots Table

ID - int
Screenshot - text (link)
ProjectID - int

Videos Table

ID - int
Video - text (link)
ProjectID - int

Downloads Table

ID - int
DownloadLink - text (link)
ProjectID

Changelog

ID - int
Change - text
Date - int
ProjectID - int

Languages Table

ID - int
Language - text

DownloadTypes Table

ID - int
Type - text (would be source, application, library, etc...)

Categories Table

ID - int
Category - text

Type Table

ID - int
Type - text

Platform Table

ID - int
Platform - text

Lookup Tables
CatagoryLookup
Language Lookup
DownloadType Lookup
Type Lookup
Platform Lookup

Lookup Template

ID - KEY(ID1,ID2)
ID1 - int
ID2 - int

This post has been edited by p0is0n: 09 April 2010 - 03:12 PM

[jrm402]

Cool, I see you've been doing your homework too!

[p0is0n]

Yeah, I realised my knowledge was more lacking than I thought so spent a couple hours reading up on the subject.
Thanks.

[p0is0n]

Hi again,

I've finally got around to implementing this and just have another quick question.
Got the basic design and admin login sorted.

When I create a new project should the script just add information to the database so the project page is created with the information from the database every time that project is viewed, or add information to the database and generate the webpage so that the webpage is only generated the once, or destroyed and recreated on editing?

I'm guessing that the information should only be added to the database, but am unsure how this would affect searches in google. Would google still find the pages even if they technically don't exist?

Thanks!

[oomlaut]

Search engine strategy is probably best addressed in a different forum, but since it is for a portfolio site my assumption is that you want people to find you based on the services that you offer, not the projects that you worked on. You would be hard-pressed to compete for showing up in search results if you were targeting "Nike" or "Dell". You're better off providing quality, descriptive content on your site and then linking to it from your various other online personas (LinkedIn, Facebook, Dream.in.code, etc...)

There are however advantages to organizing your URL route to look more like

http://nameofsite.com/portfolio/projectname

than

http://nameofsite.com/portfolio.php?project=name

If you want to change your pathing/routing, check out some of the tutorials on using .htaccess and/or MVC

This post has been edited by oomlaut: 21 April 2010 - 07:39 AM