Page 2 of 6
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 2:36 pm
by jaoudestudios
Definitely worth post the whole database schema.
Any reason for using CHARSET=hp8 ?
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 2:44 pm
by JAB Creations
HP8 = English correct?
I can't conceive (at my current understanding) of how to do this with two tables (though I have read and would be confident about doing a left-join).
I can only describe how I have been thinking of approaching it though I'm obviously open to suggestions...
Essentially if a game is added and a category's checkbox is checked then a row is inserted of the game's ID in the game list. That's it really the most minimal way I can view doing this.
I've been searching through my WordPress DB for tags because it's essentially the same thing (at least in my mind) of what the category/genres are.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 2:46 pm
by JAB Creations
Here is a quick table dump (no row data yet). I really haven't done much except create a structure...I will probably have to remove the auto-increment (as the id's would be uniquely tied to the ID's in the main games table which would inherit a unique ID to begin with...at least that is what I think now looking at the MySQL). Here it is...
Code: Select all
-- phpMyAdmin SQL Dump-- version 2.11.6-- http://www.phpmyadmin.net---- Host: localhost-- Generation Time: Oct 15, 2008 at 03:44 PM-- Server version: 5.0.27-- PHP Version: 5.2.5 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; ---- Database: `gamer`-- -- -------------------------------------------------------- ---- Table structure for table `cat_action`-- CREATE TABLE IF NOT EXISTS `cat_action` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_action`-- -- -------------------------------------------------------- ---- Table structure for table `cat_adventure`-- CREATE TABLE IF NOT EXISTS `cat_adventure` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_adventure`-- -- -------------------------------------------------------- ---- Table structure for table `cat_arcade`-- CREATE TABLE IF NOT EXISTS `cat_arcade` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_arcade`-- -- -------------------------------------------------------- ---- Table structure for table `cat_family`-- CREATE TABLE IF NOT EXISTS `cat_family` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_family`-- -- -------------------------------------------------------- ---- Table structure for table `cat_puzzle`-- CREATE TABLE IF NOT EXISTS `cat_puzzle` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_puzzle`-- -- -------------------------------------------------------- ---- Table structure for table `cat_rpg`-- CREATE TABLE IF NOT EXISTS `cat_rpg` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_rpg`-- -- -------------------------------------------------------- ---- Table structure for table `cat_sports`-- CREATE TABLE IF NOT EXISTS `cat_sports` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_base` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=hp8 AUTO_INCREMENT=1 ; ---- Dumping data for table `cat_sports`--
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:01 pm
by onion2k
JAB Creations wrote:I've attached what I have right now save for the MySQL connection file...
_0_header_01_mysql.php
Code: Select all
<?php
$truedbuser = "DB_USER";
$truedbpass = "DB_PASSWORD";
$client_uri = explode("?", $_SERVER['REQUEST_URI']);
$truepage = $client_uri[0];
$db = mysql_connect("localhost", $truedbuser, $truedbpass);
if (!$db && !isset($_GET['error'])) {$_SESSION['status'] = '<b class="bad">Could not connect to the database:</b> '.mysql_error(); header("location:".$truepage."?error");}
$db_selected = mysql_select_db("gamer", $db);
//if (!$db_selected) {$_SESSION['status'] == 'Could not select table: '.mysql_error(); header("location:".$error_url[0]."?error");}
if (!$db_selected && !isset($_GET['error'])) {$_SESSION['status'] = '<b class="bad">Could not select table in the database:</b> '.mysql_error(); header("location:".$truepage."?error");}
mysql_query("SET time_zone = '-4:00';");
?>
1. $truedbuser and $truedbpass should be defined as constants. It's not like they're going to change.
2. You're not exit()ing after setting a "Location" header. The script will continue to run despite knowing there's a problem, potentially setting a second "Location" header.
3. You're putting some fairly specific HTML in the session on an error. If, for example, you decided to rename the 'bad' class to something else you'd have to go through loads of code to rename it. You should only pass the minimum required ... in this case that would be the error string. Figure out the display of it in a function or a template when it's displayed.
4. You're using !$db to check if the database connected. The test will only check if $db is set to something other than FALSE or 0, which is fair enough as you're testing the return value of mysql_connect, but if you want to check it's actually a database connection you should use get_resource_type($db)=="mysql link". (That is just me being
really pedantic, mind you.)
5. You've got a closing php tag at the end of the file. Personally, I won't put one in just in case of stray carriage returns sneaking in and potentially killing any header() calls made in subsequent files.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:08 pm
by onion2k
Don't approach this problem using 1 table per category. The whole point of this question is to demonstrate you understand many-to-many relationships using a link table, and that you can join tables together to get the information from them. Learn them if you've not used them before.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:22 pm
by jaoudestudios
HP8 = English correct?
utf8 is the right one - I think. Anyone else?
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:25 pm
by JAB Creations
Can you guys at least give me an idea of how you would use the categories then in a two table setup? I need a visual on the tables and then I'd have a clue of how to approach this. Concurrently I'm not finding an tutorials that relate to the relationship between the data without repeating data.
If I can't use the category as a table then each category as a column in a second table?
If I did that then a row where one game has two categories (out of eight) would have six empty columns in that row. That doesn't make sense to me or is that what you guys are recommending?
I'm not asking for code so much as helping me visualize the concept please.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:36 pm
by arjan.top
table game:
id, name etc.
table category:
id, name etc.
there is many-to-many relationship, so you need another table:
game_has_category:
id, id_game, id_category
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:37 pm
by onion2k
I drew one the other day for a different thread on here...

Technically the third table (category) isn't needed if you just had a static list of categories. House would be your list of games... then house_category_xref would store the game id and whatever categories it's in... if the game is in 5 categories then there'd be 5 rows in there.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:40 pm
by JAB Creations
I presume utf8_general_ci is the desired collation?
I've implemented the exit functions, good point!
Still reading whatever I can find online right now...
Thanks for the help and direction thus far.
I am definitely not going to be able to get this done before 5pm (in a few minutes) as desired though I will send him some other example code and let him know that I'll have this finished sometime tomorrow. I still have a bit of stuff to forward to him so it won't seem like I've lost interest while I continue work on this. I'm going to go ahead and get that stuff together now and get back to reading.
Also both Onion's and Arjan's posts look helpful (you guys just posted when I attempted to) so I will read them indepth once I've sent him the email. Thanks very much for posting!
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:45 pm
by onion2k
JAB Creations wrote:I presume utf8_general_ci is the desired collation?
The collation should match the character encoding of the data stored in the table, and consequently the charset of the table. Charset is
much more important than collation as it controls how the data is stored, collation just controls how it's sorted when you query it. As a rule UTF-8 is a good choice. Make sure that PHP's mysql client is using UTF-8 too ( using mysql_query("SET NAMES utf8") ), and your HTML pages are UTF-8 as well (with header("Content-type: text/html; charset=utf-8;"); if you can't configure the webserver to do it automatically.
To be honest, I don't really see why MySQL lets you store data in one character encoding and sort it in another. I can't think of a single scenario where anyone would need to do that.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 3:56 pm
by jaoudestudios
Database structure and data
# game #
id -> 1
name -> game 1
note -> note 1
# category #
id -> 1
name -> category 1
id -> 2
name -> category 2
id -> 3
name -> category 3
# game_category #
id -> 1
game_id -> 1
category_id -> 1
id -> 1
game_id -> 1
category_id -> 3
So game 1 will belong to category 1 and category 3.
This is the backend stuff, as regards to the frontend you have a few options, keep it simple and use check boxes with a tabular layout.
i.e.
Game | Category 1 | Category 2 | Category 3 |
game 1 1 0 1
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 4:36 pm
by onion2k
You don't need an id field in your game_category table... just game_id and category_id. Personally, I wouldn't call the id field in the game table just "id", and likewise the id field in category "id" too ... when you come to write queries with joins it's much simpler if they're called something like game_id and category_id ... that way you know, for example, category.category_id matches game_category.category_id.
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 4:43 pm
by Christopher
Hmmm ... I usually follow the "<table>_<key>" naming for foreign keys. I actually like to read "ON category.id = game.category_id" where the "category.id" matches the "category_id".
Re: PHP Lead Developer Interview: Your suggestions please!
Posted: Wed Oct 15, 2008 7:36 pm
by JAB Creations
onion2k wrote:I drew one the other day for a different thread on here...

Technically the third table (category) isn't needed if you just had a static list of categories. House would be your list of games... then house_category_xref would store the game id and whatever categories it's in... if the game is in 5 categories then there'd be 5 rows in there.
First and most importantly...
All of everyone's input is greatly appreciated! These folks wouldn't have approached me in the first place if they hadn't seen my website...and I greatly attribute DevNetwork and the people here with helping me with a large portion of the critical back end stuff I've learned and implemented in to it.
Ok I'm going to start with what I
feel I'm solidly grasping which is Onion's post. Granted I had to stare at the picture for a while but as I kept rereading his post it sort of came together.
I'm making this quick post as I've been AFK for two and a half hours and you all gave me the courtesy of replying to my thread to try and help me. Life caught up with me...but I'm back now and I've still got half a Red Bull and a couple solid hours of consciousness left. I'm working on the MySQL structure now...
I think I will reference (not sure if I'm wording this correctly) the game_id and the category_id in the "connector" table (in Onions post the "category_house_xhref I think) to the id in the "game" table and the id in the "category" table. That's how I am conceptualizing my initial approach. I'll post what I have MySQL structure wise in just a wee bit!
