Page 3 of 6

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Wed Oct 15, 2008 8:37 pm
by JAB Creations
I should clarify that jaoudestudios's and Arjan's text based layout posts were also very helpful. All these bits helped create this conceptual layout in my head.

So here is my current MySQL export; I've seeded some data manually so when I regain consciousness in the morning I won't have much trouble kick-starting on implementing this.

Thoughts? Opinions? If nothing overly critical then I'll post a second zip file in the morning once I feel it's solid enough.

I feel very confident about this now. If I land this job I look forward to working in a professional environment where my learning will be accelerated by necessity and not just by my pure personal internal motivation.

Any way here is the MySQL export...I'm going to go ensure where I fall over is springy and covered with lots of cloth or cloth like material. :mrgreen: Again thanks to all of you for posting.

* Edit *: I thought I had the game_category table's id column set to auto-increment? Those shouldn't be all zeroes...I'll fix it in the morning when I reread my post and any new replies.

Code: Select all

-- phpMyAdmin SQL Dump-- version 2.11.6-- http://www.phpmyadmin.net---- Host: localhost-- Generation Time: Oct 15, 2008 at 09:29 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 `category`-- CREATE TABLE IF NOT EXISTS `category` (  `id` INT(10) NOT NULL AUTO_INCREMENT,  `name` VARCHAR(128) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=hp8 AUTO_INCREMENT=9 ; ---- Dumping data for table `category`-- INSERT INTO `category` (`id`, `name`) VALUES(1, 'Action'),(2, 'Adventure'),(3, 'Arcade'),(4, 'Family'),(5, 'Puzzle'),(6, 'RPG'),(7, 'Sports'),(8, 'Turned Based'); -- -------------------------------------------------------- ---- Table structure for table `games`-- CREATE TABLE IF NOT EXISTS `games` (  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `name` VARCHAR(128) NOT NULL,  `name_base` VARCHAR(128) NOT NULL,  `author_name` VARCHAR(128) NOT NULL,  `author_comments` VARCHAR(8192) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=hp8 AUTO_INCREMENT=34 ; ---- Dumping data for table `games`-- INSERT INTO `games` (`id`, `name`, `name_base`, `author_name`, `author_comments`) VALUES(5, 'Football Fanatics 2009', 'football_fanatics_2009', 'John A. Bilicki III', 'A football game for couch potatoes!'),(3, 'Angry Gopher Golfers', 'angry_gopher_golfers', 'John A. Bilicki III', 'They''re gophers, they''re angry, and they''re out to tear the greens up!'),(4, 'Destroy All Humans', 'destroy_all_humans', 'John A. Bilicki III', 'Your goal is to steal the human''s money, DNA, and cattle!'),(2, 'Heroes of Might & Magic III', 'heroes_of_might_and_magic_iii', 'John A. Bilicki III', 'Turned based strategy at the height of sprite based graphics.'),(1, 'Diablo II', 'diablo_ii', 'John A. Bilicki III', 'Addictive though game play is a bit repetitive.'); -- -------------------------------------------------------- ---- Table structure for table `game_category`-- CREATE TABLE IF NOT EXISTS `game_category` (  `id` INT(10) NOT NULL,  `category_id` VARCHAR(128) NOT NULL,  `game_id` VARCHAR(128) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=hp8; ---- Dumping data for table `game_category`-- INSERT INTO `game_category` (`id`, `category_id`, `game_id`) VALUES(0, '7', '5'),(0, '7', '3'),(0, '2', '3'),(0, '1', '4'),(0, '2', '4'),(0, '5', '4'),(0, '2', '2'),(0, '8', '2'),(0, '6', '1'),(0, '2', '1'),(0, '1', '1'); 

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 1:58 am
by jaoudestudios
[quote=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.[/quote]
I disagree. I use to do it that way. But in-fact it does not make sense. Think of objects. In the queries where you join tables together, you always have to specify the table first then the column. So if two tables have the column id, it is fine as it would be like onion2k said table.column. This is pretty obvious what you are referring to when you write the query. It becomes too long and redundant to write category.category_id where if you think of objects category.id makes much more sense. Foreign keys I would use the underscore i.e. in another table specifying the category id as a foreign key I would use table.category_id exactly like arborint said.

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 2:50 am
by onion2k
jaoudestudios wrote:I disagree. I use to do it that way. But in-fact it does not make sense. Think of objects. In the queries where you join tables together, you always have to specify the table first then the column. So if two tables have the column id, it is fine as it would be like onion2k said table.column. This is pretty obvious what you are referring to when you write the query. It becomes too long and redundant to write category.category_id where if you think of objects category.id makes much more sense. Foreign keys I would use the underscore i.e. in another table specifying the category id as a foreign key I would use table.category_id exactly like arborint said.
What you're saying does make sense if you think of database tables in terms of objects.

But I don't. They're not objects. They're database tables.

:D

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 3:42 am
by Paul Arnold
JAB Creations wrote:I should clarify that jaoudestudios's and Arjan's text based layout posts were also very helpful. All these bits helped create this conceptual layout in my head.

So here is my current MySQL export; I've seeded some data manually so when I regain consciousness in the morning I won't have much trouble kick-starting on implementing this.

Thoughts? Opinions? If nothing overly critical then I'll post a second zip file in the morning once I feel it's solid enough.

I feel very confident about this now. If I land this job I look forward to working in a professional environment where my learning will be accelerated by necessity and not just by my pure personal internal motivation.

Any way here is the MySQL export...I'm going to go ensure where I fall over is springy and covered with lots of cloth or cloth like material. :mrgreen: Again thanks to all of you for posting.

* Edit *: I thought I had the game_category table's id column set to auto-increment? Those shouldn't be all zeroes...I'll fix it in the morning when I reread my post and any new replies.

Code: Select all

-- phpMyAdmin SQL Dump-- version 2.11.6-- http://www.phpmyadmin.net---- Host: localhost-- Generation Time: Oct 15, 2008 at 09:29 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 `category`-- CREATE TABLE IF NOT EXISTS `category` (  `id` INT(10) NOT NULL AUTO_INCREMENT,  `name` VARCHAR(128) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=hp8 AUTO_INCREMENT=9 ; ---- Dumping data for table `category`-- INSERT INTO `category` (`id`, `name`) VALUES(1, 'Action'),(2, 'Adventure'),(3, 'Arcade'),(4, 'Family'),(5, 'Puzzle'),(6, 'RPG'),(7, 'Sports'),(8, 'Turned Based'); -- -------------------------------------------------------- ---- Table structure for table `games`-- CREATE TABLE IF NOT EXISTS `games` (  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `name` VARCHAR(128) NOT NULL,  `name_base` VARCHAR(128) NOT NULL,  `author_name` VARCHAR(128) NOT NULL,  `author_comments` VARCHAR(8192) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=hp8 AUTO_INCREMENT=34 ; ---- Dumping data for table `games`-- INSERT INTO `games` (`id`, `name`, `name_base`, `author_name`, `author_comments`) VALUES(5, 'Football Fanatics 2009', 'football_fanatics_2009', 'John A. Bilicki III', 'A football game for couch potatoes!'),(3, 'Angry Gopher Golfers', 'angry_gopher_golfers', 'John A. Bilicki III', 'They''re gophers, they''re angry, and they''re out to tear the greens up!'),(4, 'Destroy All Humans', 'destroy_all_humans', 'John A. Bilicki III', 'Your goal is to steal the human''s money, DNA, and cattle!'),(2, 'Heroes of Might & Magic III', 'heroes_of_might_and_magic_iii', 'John A. Bilicki III', 'Turned based strategy at the height of sprite based graphics.'),(1, 'Diablo II', 'diablo_ii', 'John A. Bilicki III', 'Addictive though game play is a bit repetitive.'); -- -------------------------------------------------------- ---- Table structure for table `game_category`-- CREATE TABLE IF NOT EXISTS `game_category` (  `id` INT(10) NOT NULL,  `category_id` VARCHAR(128) NOT NULL,  `game_id` VARCHAR(128) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=hp8; ---- Dumping data for table `game_category`-- INSERT INTO `game_category` (`id`, `category_id`, `game_id`) VALUES(0, '7', '5'),(0, '7', '3'),(0, '2', '3'),(0, '1', '4'),(0, '2', '4'),(0, '5', '4'),(0, '2', '2'),(0, '8', '2'),(0, '6', '1'),(0, '2', '1'),(0, '1', '1'); 
This looks like you fully understand the concept now.

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 6:53 am
by JAB Creations
Woke up at 6:30am, heh on an early cycle right now. :|

Any way one thing I'm thinking about just before I start cracking on making this work with last night's breakthrough is what would be better to use for the name values? The category id, name, or something unrelated? I want to show that extra level of thought if for example it could make it more difficult for a hacker to attack the site. By default I'll stick to the name I think for now. I'll post what I have maybe in an hour or so; I want to get this done by late am the latest.

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 7:41 am
by JAB Creations
The person I interviewed and received this project from was showing me a recursive function which now that I think of it ...should be part of the project.

He wants to be able to add as many tags per game...and there is the ability to check however many tags (genres) when adding a new game...

So (as I've not yet done recursion) I'm trying to think of how to approach it...

In my head at least at this moment is that I need to first somehow count how many of those checkboxes are checked (how many $_POST vars are set)...

So is there a way to see an array of all the post data?

If so then I (might be able to on my own) figure a way to determine which are tags, count them, and pass that number to a function. Every time the function executes I subtract one from the function and call it again until it hits zero with maybe a cap to prevent time-outs or excessive server load?

That ultimately mean any one using this would not have to manually add a new tag/genre to PHP...it would simply handle it dynamically. Ok...time to find some sort of $_POST dump...suggestions are welcomed. :mrgreen:

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 7:52 am
by JAB Creations
I could simply change the name values of the checkboxes to match the id of the category rows...

Then I could simply count the rows in the category table...

...and use that number to check for checkbox $_POST's.

In example I might $_POST cat_1, cat_4, and cat_8.

In a for loop I could check isset 'cat_'.$row['id']...

Working on it now... :mrgreen:

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 8:05 am
by panic!
jaoudestudios wrote:JAB are you proposing this...
# game table #
id
name
name_base
genre_1
genre_2
genre_3...

That is definitely NOT the way to do it as it would limit you to the amount of genres available to a game. The description specifically asks for...
I would also like an open-ended categorization scheme to allow for any number of categories to be applied to any one game entry
I would do it the way Paul Arnold suggested. It is the best, it allows the most flexibility.

Post your database schema when you have it.
4.) methods to prevent duplicate games
Also prevent the game being added to the same category twice. This can easily be achieved with a sub query.
3.) plenty of reusable functions
Good time to use a class. Especially for the SELECTs he asks for.
I agree, another reason it's a bad idea to have genre_1,2,3 is if you want to select all the games with the genre 'first person shooter' (or hopefully 'first person shooter's ID) you have to search 5 columns for 'first person shooter' if you want to pull out all of the first person shooter games.

Whereas you can use a lookup/link/pivot table and left join the game table rows onto it.

if 69 is the id of first person shooters:

"select g.* from games_genres gg left join games g on gg.game_id on g.id where gg.id='69'"

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 8:32 am
by JAB Creations
A good/critical question came to mind: recursion or iteration/a for loop?

I'm working on a function that takes a $count parameter which is determined by the number of rows in the category table (so I have the exact number of categories)...

I create a variable to dynamically check every possible isset($_POST)...

Code: Select all

function content_game_add_tags($count)
{
 // Create possible checkbox $_POST to determine if isset
 $cat = 'cat_'.$count;
 
 // Is the post set? Then let's insert to the table!
 if (isset($_POST[$cat])
 {
//MySQL INSERT here
}
...I could technically do a for loop or call the function again...

So recursion or iteration? Any load difference? What bare bones examples best fit each approach? For an employer looking to see me flex my skills if he asked me why I choose one over the other...I would like to understand what I'm saying rather then simply parroting something I randomly read...so opinions please?

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 8:46 am
by onion2k
There's no call for recursion in a project like that.

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 10:20 am
by JAB Creations
For my error pages I'm having trouble with a bare bones class. I think it's pulling from an existing variable...so I renamed the variables (replacing i with 1) and it's still spitting out 'gamer/index.php' when I want it to spit out 'index.php'. I'd like to demonstrate some OOP with the error page header redirects, nothing extravagant though I'm stuck on this one part. I'll use non-OOP code for the moment to keep on working...I may have the tag system working in about twenty minutes. My goal is to get this done by noon (48 minutes from this post). If I don't iron this out I've already sent him my site's class file and a couple XHTML templates that use the OOP code...still it'd be nice. :mrgreen:

Code: Select all

<?php
class settings {
 private $truepage;
 public function set($name,$value) {$this->$name = $value;}
 public function get($name){return $this->$name;}
}
 
$client_ur1 = explode("?", $_SERVER['REQUEST_URI']);
$truepage1 = $client_ur1[0];
 
$settings = new settings();
$settings->set('truepage',$truepage1); //$settings->get('truepage');
?>
Also a lot of good ideas are popping up in my mind especially being able to use this to add tag support when I write my own in house blog to replace my current WordPress blog which was difficult to integrate and even more difficult to update. That's OT though... I'll post what I have when I get the tags to work in a little bit...

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 1:29 pm
by JAB Creations
SECOND BUILD

I've attached the second build; the MySQL is included (and you'll have to write in your own user/pass).

This build now allows you to add tags. More importantly it uses a for loop and is programmed to dynamically handled any given number of tags which is dependent on the number of rows in the tag table (err category though you get what I mean).

All that is left is to actually make a view page that spits out the information...so MySQL SELECT queries is all. I'll have that posted in my third build. I'm of course still open to suggestions. I was stuck on the for loop because (duh) I was returning on a single success when I was....trying to do a for loop! Isn't that special?! :mrgreen:

Any way here is the second build...

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 4:37 pm
by JAB Creations
I just got off the phone with the person I interviewed with. He received my email and the attachment and it seems I sent him (he was merely looking at code, not setup with the DB/in a browser) a bit more then he expected/required which seemed to be a positive note.

Additionally he clarified that he is not looking for a lead developer (since he is the lead developer). *huge sigh of relief* Not that I'll make any less of an effort than I am now it's just that I feel I have a bit more of a shot at this than I initially thought.

Another very positive thing I got out of our exchange is that if they don't take me onboard he said he'd be happy to let me know what I'm missing that he was looking for so that I can learn relevant material. So even if I don't get the job I score two points: missing knowledge I might strike out on so I know what to concentrate on and the code I already have and will keep writing as part of the tests he has requested. Again if I don't get the job it won't devastate me in the least though it'll be sweet if I do. :mrgreen:

His next request is essentially a ten line function as he described, I'll quote him in the email and he said it's a lot simpler then what you initially think is it...
For #4: This is based on a technology that I have already built and implemented for a client. Basically, we had all the forms in place on a live site. The client then requested that all the form data be pushed (in real time) through a 'GET' URL on a third-party server that would then index the data into a call center database. The basic url scheme would look like this:
http://remoteserver.com/process_data.ph ... domain.com (etc...)
I want you to specifically write the function that takes the host and url vars and does the 'push' of that query data to the remote server.
I'll start a new thread either later on tonight or early tomorrow morning in the PHP code forums (sorry about turning this in to a code thread in the General Discussion forum btw!)

For now I'm going to have some dinner, work on a client's site (to give my brain a rest) and probably end up doing some PC gaming before I crash (likely Oblivion or UT2004). Thanks for the continued support!

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Thu Oct 16, 2008 5:56 pm
by onion2k
That's incredibly simple. I reckon it's less than 10 lines to be honest...

What I would do (copy and paste it somewhere to read it.. wouldn't want to spoil the fun for other people): Just pass in $_POST and the url value, convert $_POST it to an array of urlencode()'d "name=value" strings with a foreach, implode that array with "&"s, build the URL by using $url."?".$value_string, then use file_get_contents() (or anything that works with URL wrappers) to fire it out the call to the remote server. Job done.

Re: PHP Lead Developer Interview: Your suggestions please!

Posted: Fri Oct 17, 2008 5:06 am
by VladSun
onion2k wrote:But I don't. They're not objects. They're database tables.

:D
http://www.postgresql.org/docs/8.1/stat ... herit.html

I think they are object-like :)

I agree with jaoudestudios that using "table-name-based namespace" is better than a "suffix-based" one. It's more readable.