Need to know if php is able to do this

[shenagsandnags]

Hey all, new to the forum and php as well! well sorta.. anyways im working on this new script to list my xbox games and have a simple question.

 

The way im designing the database for this is, im going to have 5 game categories; racing,shooter etc.  each category is going to be a table in the database so theres 5 tables. each table is going to have 2 columns "Title" and "Link (to game info)".  now my goal is once the database is completed i want to create a query it and get this result in mygames.php or whatever i name it:

 

Racing

-----------

Game 1

Game 2

Game 3

 

Shooter

----------

Game 4

Game 5

Game 6

 

etc..

 

Now as you see, in the end result you dont see the link to each title beside it in column 2, as it looks in the database. thats because instead of having the link show beside each title i would rather have the title itself be the link so when the user clicks on it, it would popup in a new window.

 

Hopefully i done a decent job in explaining this so its understandable but i would like to know if this is possible ? im suprised google hasnt billed me for as many searches ive used for this but i just cant get an answer. im not even sure im designing the database to my specific needs to its full potential, hey im learning but i also welcome any tips or ideas just as long as i can get the exact result i need, nothing fancy. thanks everyone!

 

 

 

 

 

 

 

 

[Anti-Moronic]

First, it's possible but the wrong way to do it. You only need 2 tables. One for categories, one for games.

 

You then link the two using a common id. Like in the games table you have 'category_id' and that connects any given row to the category table row with that id. That way you can have infinite categories and infinite games and a far more maintainable structure.

 

For example, with your 5 tables, say you want to add another column to store some extra info about a game - like coop or not, how many players, release date etc etc, you then have to add that column to every single table. With the above, you just add it to the games table. Simple. Same goes for categories. Because each category is a table you don't actually have any variable data on the categories. You only know the name of the category by table name which you have to use during your select.

 

..and moving onto selects, this would be a rediculous way to approach it.

 

To help:

 

http://www.sialnity.com/2010/08/mysql-tutorial-displaying-data-from-multiple-tables/

 

 

[shenagsandnags]

first, thanks for taking the time out and pointing that out to me thats exactly the type of information i was welcoming like i said i knew i wasnt using it to its efficiency im sure i will nail it once i start getting more experience with it. second, so in the games table i will still use 2 columns right, title/link ? assuming im right about that then how exactly do i make the titles become the link in the results ? im not expecting you to type it out to me but maybe at lest point me somewhere ? i dont know if im just not using the right search terms or what but i really cannot find anything specific to the subject. thanks again.

[JD*]

Actually, in your games table you'll probably have a few columns, but at the least you'll have ID, name, category_id and link. Then, in your category table, you'll have ID and category (or whatever you want to call it). Then, your category_id in the games tables will reference the ID in the category table.

 

In your PHP, when you do your select, it would look something like this:

 

$games = mysql_query("SELECT g.name, c.category, g.link FROM games g, categories c WHERE g.category_id = c.ID") or die(mysql_error());

 

The above assumes that your tables are named games and categories. Once you do that, you can loop through the results and print out a link tag around the name of the game.

[laffin]

 

A Simple DB Designer works wonders.

 

Here we just see 2 tables, categories and games.

we define the categories, than in our second table the games, we make a field for the referencial link to the categories table.

 

You wont get categories name automatically from the games table, u do this with JOINs in your sql staement

however working with category_id is a lot simpler than working with the categorie's name.

mysql will perform faster

 

example

SELECT Games.*,Categories.name FROM Games, Categories ON Categories.id = Games.category_id ORDER BY Games.category_id ASC

 

would organize your games by category and retrieve the category name as well

 

Good luck on the project

[shenagsandnags]

Ok so pretty much both of you are saying that my tables should look like:

 

 

 

Games

------

Id  -  Category_id  -      Title      -      URL

----------------------------------------------------

21  |      1      | Racing Game 1    | http://

22  |      2      | Shooter Game 2  | http://

23  |      3      | Fighting Game 3  | http://

 

 

 

 

 

Categories

----------

Id  -  Category_Name

-------------------

1  |  Racing 

2  |  Shooter

3  |  Fighting

 

 

 

 

and then if i used :

 

 

SELECT Games.*,Categories.name FROM Games, Categories ON Categories.id = Games.category_id ORDER BY Games.category_id ASC

 

i would get:

 

 

Racing

-----------

Game 1

Game 2

Game 3

 

Shooter

----------

Game 4

Game 5

Game 6

 

etc..

 

 

 

i got all this correct ?

[laffin]

Yes, thats pretty much it.

as u can see, adding a category and games is made much simpler without the need of creating a new table for each category.

 

but thats a pretty simple design, db's can be very complex if they need to be. but this is a good start to using db and referential links.

[shenagsandnags]

Yes, no doubt. thanks alot to you guys really setting all that our for me. i think i pretty much just about got everything figured out to what i need.  once i get everything up and running im going to be using a form that im working on to update the tables. one thing i do got to get figured out before i start getting my hands dirty is getting the db and tables created.  i already got a site completed for this script to go in, in which i was just going to create a gamelist.php and link it to the left menu on my site and let the list just show up in the right frame. so my last question is do i throw in the "CREATE DATABASE" and "CREATE TABLE" statements in my gamelist.php above all the SELECT statements but below connecting the DB statements or do i need to create a seperate file for that ?  it just seems to simple to have all that in 1 file. or should i just create the databases using the mysql shell or something ?

[Anti-Moronic]

No, you wouldn't generally have the database schema in php files. If the system is to be distributed like with an installer, then you would have CREATE tables etc in a .sql file which your installer would use (along with connection details) to create the structure.

 

However, if that isn't the case then you create the database separately. People commonly use phpmyadmin for database management, or you can use a database designer like:

 

http://www.mysql.com/products/workbench/

 

If you're just starting out however I'd use phpmyadmin so you can get a very bare bones look at the database as you create it.

[shenagsandnags]

Well ive been messing with ms access and through out the tutorials im starting pick up how all of it works. i originally thought that i was just going to be able to write a couple php pages that connected to mysql on a hosted site and work simple. now i see the picture and im just going to use php to retrieve the info from my db to post on my site. im glad i stopped by here though because i imagine i would have spent alot more time getting to where i am now.