Anotherfluke's Online Collection Database Journal

[Anonymous]

Taking a nod from Captain Wrong's SuperGun journal, I decided to create a database for my collection, and put it online, while posting my progress for others who are interested in it.

The decision came when my webhost, www.powweb.com decided to allow people to open MySQL databases at no charge. The first thing I did when this happened was updated my website, www.kindstranger.com with a php news engine. This will be my reference material for generating calls and webpages once my collection database is online.

The second thing I needed to do was decide on a database program for my collection. Cart Commander was a good program, but MS Access seemed to have the most import/export capabilities, not to mention infinite customability. I decided on MS Access, and this turned out to be an excellent choice.

I'm not very familiar with Access, but it's pretty user friendly (for a dB program anyways), and I was able to make a couple of tables for games, systems, and peripherals, and forms for inputting data and looking through my collection when I'm at home. Here is a pic of the dB after an hour or so:

http://www.kindstranger.com/Images/vgdb.png

Here's one after I entered some test data and added another table and another form. This is pretty much how it will look from this point on, except I need to add a form for peripherals:

http://www.kindstranger.com/Images/vgdb2.png

I spent a couple hours trying different export options for generating text files, excel documents, xml, etc. to import onto my SQL database, What finally worked was a combination of ODBC and a program called MySQL-Front. MySQL-Front understood ODBC, and allowed me to import my entire .mdb file without issue.

Now, I've got two main goals. First, I need to learn enough PhP to be able to create pages that will allow me to access my collection data online. Second, I need to populate the tables with data. In other words, I need to type in all my games/system/peripherals :roll:

After that is done, I'll make a trade list which will be connected to the main collection db, and will automatically update both databases whenever I add or detract from my collection or trade list. Finally, I'll make a webpage where people can look at my trade list, and search through it by system, game, rarity, etc. Whew!

[hydr0x]

as i said earlier in the other topic, i'm doing exactly the same right now, i'll submit suggestions to this topic if i have some ;)

perhaps i'll also post my progress, but i'm not doing anything right now, so it might take a while until i will

i just looked at your screens at u've done a good job until now, only thing i noticed is that i do have a lot more fields per game (i haven't done systems yet)

what i have and u don't:

Romcenter Name of Dumped Rom (of course not possible with all systems)
Size in Mbit
Copyright Year (i also have release date)
Media Type (Cart, Disc, CD....)
i split up your language field into three fields: Region (Europe,USA,Japan,Australia....); Country (Exact Release Country as stated on package); Language(s) (Available Languages in game)
Serial Nr (like SNS-XX-USA, SLES XXXX....)
Guide Included? Guide Condition
Inlay (like the paper thing in snes games) included?
Plastic Bag included?
Dust Cover included?
Which Extras included (Poster, Warranty-Card....)
Purchased From
Actual Value
Have i finished the game?
Personal High-Score
Personal Rating
Other Ratings

[Anonymous]

Hm those are some good topics. I wanted to keep from getting too specific about the game details since most of that info can be found online or in the guide. I mainly want to keep it related to either the game's value, rarity, or the condition of the game (plus throw in some extra info in case I wanted to see all the Treasure games I had, or something like that).

But I never even considered adding data for personal milestones, that sounds like a really good idea.

As for progress, I just posted my first ever dynamic page:

http://www.kindstranger.com/tips/test.php3

It's a far cry from the final product, but it fills me with a lot of hope for the future :) The game info is actually being pulled from the test database I have on my SQL server. WOOT! :D

Also, for those who are interested, here's the file renamed as a text document. Everything between the { } brackets (including the brackets themselves) are personal information like usernames and column names and whatnot.

http://www.kindstranger.com/tips/test.txt

[Anonymous]

Well I've hit a milestone in getting my collection online!

I spent all of today working on and learning PhP, and my efforts have paid off! Look at what I can do!

http://www.kindstranger.com/tips/searchtest.php3

What you're looking at is a php search that lists all the games I have for a particular system. When you get the list, you can click on the game name, and it will display information about the game. The search engine and list results are in the same php file, and the details are in a second smaller file. The best part (and one of the hardest parts) is that although the system list is only 5 systems long right now, the form is dynamically generated, so as I add systems to my database, they will automatically appear in the search field as an option. Same thing with the games that appear. If you can't tell, I'm really proud of this search engine :D

Of course, it isn't much to look at right now, and eventually I want to add other search options, but just knowing that I have the ability to generate ANY search engine at all is a big milestone for me. Until now I thought the hardest part would be the back-end programming, but now I'm beginning to relax a little, and can start enjoying the design and implementation possibilities. It really feels like a concrete project now, instead of just a 'what if'.

Expect more info later :)

[hydr0x]

not bad not bad, having a good search engine and good add/delete functions is the most important thing. design isn't that important, the only problem with design is the implementation of html into php or php into html, html into php NEVER works on my pc, i do everything right but it doesn't work >:(

[Anonymous]

Is your SQL server on your PC? I have to run all my php stuff off of my webserver, otherwise explorer just treats it like a text file.

And yeah, the search engine is nothing special, but considering when I woke up this morning I didn't know a lick of PhP, I'd say I did pretty good :)

[hydr0x]

i didn't say it wasn't good, i think you did a great job

i have an apache+php+mysql running on my pc for testing purposes, i don't have a flatrate (no dsl or cable possible in my region) . Of course i don't use my pc as my webspace-server ;) i still have to find a free webspace-hoster allowing php though (one without ads)

[hydr0x]

yay that was my 400th post *celebrateshimself*

[Mr-E_MaN]

Thats the greatest idea ever. I always had my database for my NES games on my PC in MS Access. I think adding it to my website is a great idea! Now all I have to do is learn mysql and php :D

One question: are you entering the games you have or every game for all the systems? I have the NES games in my database already if you need a list.

[Trellisaze]

Check out PHPMyAdmin, which is a PHP-based web interface for managing MySQL databases. It's really nice to use, though I must admit, I didn't have a whole lot of luck when I tried to get it installed at work. :D

[Anonymous]

I tried installing phpmyadmin, but I couldn't get it to work. Granted, I didn't try very hard because I found MySQL-Front, a wonderful front end which is ultimately what allowed me to import my Access Database with zero hassle. It's a really good program and I highly reccomend it for first time SQL users because it is so userfriendly.

Right now I'm entering the games I have. I've only entered about 35 games or so and 5 systems so I can test a variety of situations, but the ones that are loaded are games that I have, along with conditions like whether it has a box/manual, and the condition of the box/manual, plut notes like whether it has damage, etc. so I have to go through and enter each one separately. Thanks for the offer, though!

[Mr-E_MaN]

What books/web sites are you using to learn php/mysql?

[Anonymous]

I don't have any books, but I did take one college level class for beginning java, and that has been very useful for undertanding the logic behind it. I've also got a php news site running (one of those download and edit config file sites) that I use as reference. The only two things I've used so far are this:

http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html

and this:

http://www.php.net/manual/en/langref.php

The first is a tuturial for super basic stuff up through a form submit, and the second is a list of syntax and operators.

Today I'll be entering data until I have most if not all of my collection in the database. Before I start that, though, I need to adjust my database to include a coupld of fields suggested by Hydr0x, and to accomodate 'different' games, like hucards and stuff. I'll post again if I finish a better looking table. The only other hurdle I have right now is figuring out a way to sort games. I need to figure out a way to to sort things alphabetically and by number.


I had hoped to get a lot more done on this today, but I spent most of the day tweaking my Database so I wouldn't have to do it again later and then have to rewrite my code. The same search link above works, but I've updated the results to show all the fields in my database. Most of the work I did is behind the scenes, so it will look pretty much the same to everyone else. I also spent a few hours entering over half of my game collection into my database, and I added a peripheral database which will be online too. I expect to begin working on the Sort method tomorrow. Wish me luck!

[Anonymous]

IT WORKS!!!

I finally managed to create a search that alphabetizes the games list for each system! I also added an "All Systems" search which displays every game in my collection alphabetically. Now I have two new goals:

1. Create a more complex search that lists games by rarity or all games that start with a user chosen letter.

2. Create a search for Peripherals and Systems.

Adding on to my current goal of entering the rest of my collection data. Currently I've got almost all of my NES collection online, and all of my PSX collection online. I still need to enter all of my systems, and all of my peripherals, and the rest of my games for the other systems. Once I'm finished with those, I can begin to create the final search engine, and incorporate the list into my website. The final goal is to create a WAP version so that I can check my collection via my phone, so that when I am out at the flea market I can check to see which games I need for which collections. Failing that I will need to create a mini database for my PalmIIIc.

Here is the current search engine which sorts lists Alphabetically by system:

http://www.kindstranger.com/tips/searchtest.php3

[hydr0x]

GREAT!

i've a new future goal for u :p

how about adding scans of the things to your site? O_O

[Anonymous]

You're not the first person to suggest that heh. I may have to do it just based on popular demand ;). i've tested adding hyperlinks already, Lunar: SSSC for the PSX has links to the CD set that I've got, and I know that I can add jpegs to SQL tables, but I don't know if such a thing can be done in Access (or how to do it!). On top of that I don't have a scanner. I may add scans eventually but right now they are at the bottom of the list. I want to fix the little things (like all the games that say I purchased them in 1899 :o ).

I appreciate all the suggestions, and I'll take any you've got.