Fetching Objects from the Database

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $6.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

Getting our Ship objects is easy: create a ShipLoader and call getShips() on it. We don't care how ShipLoader is getting these - that's its problem.

Hardcoding is so 1990, let's load objects from the database! We need to get these ships to their battlestations!

Database Setup

At the root of your project, open up a resources directory. Copy init_db.php out of there to the root of your project and open it up:

57 lines init_db.php
<?php
/*
* SETTINGS!
*/
$databaseName = 'oo_battle';
$databaseUser = 'root';
$databasePassword = '';
/*
* CREATE THE DATABASE
*/
$pdoDatabase = new PDO('mysql:host=localhost', $databaseUser, $databasePassword);
$pdoDatabase->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdoDatabase->exec('CREATE DATABASE IF NOT EXISTS oo_battle');
... lines 16 - 57

Tip

In order for this to work, make sure that you have MySQL installed and running on your machine. There are various ways to install MySQL in different environments - if you have any questions, let us know in the comments!

This script will create a database and add a ship table with columns for id, name, weapon_power, jedi_factor, strength and is_under_repair:

57 lines init_db.php
... lines 1 - 25
$pdo->exec('CREATE TABLE `ship` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`weapon_power` int(4) NOT NULL,
`jedi_factor` int(4) NOT NULL,
`strength` int(4) NOT NULL,
`is_under_repair` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci');
... lines 35 - 57

At the bottom, it inserts 4 rows into that table for the 4 ships we have hardcoded right now:

57 lines init_db.php
... lines 1 - 38
$pdo->exec('INSERT INTO ship
(name, weapon_power, jedi_factor, strength, is_under_repair) VALUES
("Jedi Starfighter", 5, 15, 30, 0)'
);
$pdo->exec('INSERT INTO ship
(name, weapon_power, jedi_factor, strength, is_under_repair) VALUES
("CloakShape Fighter", 2, 2, 70, 0)'
);
$pdo->exec('INSERT INTO ship
(name, weapon_power, jedi_factor, strength, is_under_repair) VALUES
("Super Star Destroyer", 70, 0, 500, 0)'
);
$pdo->exec('INSERT INTO ship
(name, weapon_power, jedi_factor, strength, is_under_repair) VALUES
("RZ-1 A-wing interceptor", 4, 4, 50, 0)'
);
... lines 55 - 57

If we run this file, it should get everything powered up. Head to your browser and run it there:

http://localhost:8000/init_db.php

If you see - Ding! - you know it worked. If you see a terrible error, check the database credentials at the top - make sure the user can create a new database.

If you want to check the database with something like phpMyAdmin, you'll see one ship table with 4 rows.

Querying for Ships

You look ready to query, copy the two lines that create the PDO object in init_db and head into ShipLoader. Keep things simple: getShips() needs to make a query. So for now, paste the PDO lines right here. Update the database name to be oo_battle and I'll fill in root as the user with no password:

... lines 1 - 2
class ShipLoader
{
public function getShips()
{
$pdo = new PDO('mysql:host=localhost;dbname=oo_battle', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
... lines 9 - 41
}
}

Ok, query time! Create a $statement variable and set it to $pdo->prepare() with the query inside - SELECT * FROM ship:

... lines 1 - 4
public function getShips()
{
$pdo = new PDO('mysql:host=localhost;dbname=oo_battle', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$statement = $pdo->prepare('SELECT * FROM ship');
... lines 10 - 41
}
... lines 43 - 44

If PDO or prepared statements are new to you, don't worry - they're pretty easy. And besides, using PDO is another chance to play with objects!

Run $statement->execute() to send the query into hyperdrive and create a new $shipsArray that's set to $statement->fetchAll() with an argument: PDO::FETCH_ASSOC. var_dump this variable:

... lines 1 - 4
public function getShips()
{
$pdo = new PDO('mysql:host=localhost;dbname=oo_battle', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$statement = $pdo->prepare('SELECT * FROM ship');
$statement->execute();
$shipsArray = $statement->fetchAll(PDO::FETCH_ASSOC);
var_dump($shipsArray);die;
... lines 13 - 41
}
... lines 43 - 44

This queries for every row and returns an associative array. The PDO::FETCH_ASSOC part is a class constant - a nice little feature of classes we'll talk about later.

Let's see what this looks like! Head back to the homepage and refresh! AND... I was not expecting an error: "Unknown database oo_battles". The database should be called oo_battle - silly me! Refresh again!

Ok! 4 rows of data.

Private Functions are Awesome

Of course, what we need are objects, not arrays. But first, a quick piece of organization. Copy all this good PDO stuff and at the bottom, create a new private function queryForShips(). Paste here and return that $shipsArray:

... lines 1 - 2
class ShipLoader
{
... lines 5 - 39
private function queryForShips()
{
$pdo = new PDO('mysql:host=localhost;dbname=oo_battle', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$statement = $pdo->prepare('SELECT * FROM ship');
$statement->execute();
$shipsArray = $statement->fetchAll(PDO::FETCH_ASSOC);
return $shipsArray;
}
}

Head back up, call this method, then remove the original code:

... lines 1 - 2
class ShipLoader
{
public function getShips()
{
$ships = array();
$shipsData = $this->queryForShips();
var_dump($shipsData);die;
... lines 11 - 37
}
... lines 39 - 49
}

Make sure things still work - cool! Now, why did we do this? Well, we had a chunk of code that did something - it made a query. Moving it into its own function has two advantages. First, we can re-use it later if we need to. But more importantly, it gives the code a name: queryForShips(). Now it's easy to see what it does - a lot easier than when this was stuck in the middle of other code.

So, creating private functions to help split code into small chunks is awesome.

Give me Objects!

Back to the ship factory to create ship objects from the array we have now.

In getShips(), I'll rename the variable to $shipsData - it sounds cool to me. Now, loop over $shipsData as $shipData. Each time we loop, we'll create a Ship object: $ship = new Ship() and pass $shipData['name'] as the only argument:

... lines 1 - 4
public function getShips()
{
$ships = array();
$shipsData = $this->queryForShips();
foreach ($shipsData as $shipData) {
$ship = new Ship($shipData['name']);
... lines 13 - 17
}
... lines 19 - 20
}
... lines 22 - 33

Next, we can use the public functions to set the other data: $ship->setWeaponPower() and pass it $shipData['weapon_power']. Do the same for the jedi_factor and strength columns: $ship->setJediFactor() from the jedi_factor key and $ship->setStrength() from the strength key. The last column - is_under_repair we'll save that one for later. Can't have all the fun stuff at once! Finish the loop by putting $ship into the $ships array:

... lines 1 - 4
public function getShips()
{
$ships = array();
$shipsData = $this->queryForShips();
foreach ($shipsData as $shipData) {
$ship = new Ship($shipData['name']);
$ship->setWeaponPower($shipData['weapon_power']);
$ship->setJediFactor($shipData['jedi_factor']);
$ship->setStrength($shipData['strength']);
$ships[] = $ship;
}
return $ships;
}
... lines 22 - 33

Wasn't that easy? Now get rid of all of the hardcoded Ship objects. We have less code than we started. That's always my preference.

We've only changed this one file, but we're ready! Refresh! Welcome to our dynamic application in under 10 minutes. Ship it!

Leave a comment!

  • 2020-05-04 Victor Bocharsky

    Hey Steve,

    Thanks for this hint about unix_socket MySQL setup!

    Cheers!

  • 2020-05-01 Steve Linberg

    If you happen to be using a Mac and running Acquia DevDesktop for mysql, it uses a socket instead of a port. So the connection strings read (for my setup at least, you might need to locate your active socket if it is somewhere else):

    Line 13:

    $pdoDatabase = new PDO('mysql:unix_socket=/Applications/DevDesktop/mysql/data/mysql.sock', $databaseUser, $databasePassword);

    and line 20:

    $pdo = new PDO('mysql:unix_socket=/Applications/DevDesktop/mysql/data/mysql.sock;dbname='.$databaseName, $databaseUser, $databasePassword);

    (But yes, it is a pretty giant assumption to make for this video that the viewer's machine is running mysql and accepting connections with no root password, without any prior configuration...)

  • 2020-04-23 Victor Bocharsky

    Hey ThingyBob,

    Ah, yeah, I see... that 306 is PhpMyAdmin port, that's another tool (server) that is separate, not the same as MySQL server, so those serves should have different ports. If you want PhpMyAdmin - you would need to install it too, it does not come with MySQL installation. But you should point PDO DB credentials to MySQL port, not PhpMyAdmin port.

    Cheers!

  • 2020-04-23 ThingyBob

    OK gotcha so, initial mysql setup required. That's what I meant in my original comment. Thanks for that, will get that installed. I was referring to port 306 because that's what used in the video (skip to 1:12) so I didn't know if the project ran somehow ran something to install mysql and set it to listen on that port, or if it somehow comes with the built in PHP web server.

    Thanks for the assistance :)

  • 2020-04-23 Victor Bocharsky

    Hey ThingyBob,

    Are you on Mac? IIRC there's no MySQL installed on Mac out of the box, so if you didn't install it - you have to do it first :) I'm on Mac also, and installed MySQL via Homebrew, I'd recommend you to do the same.

    Also, why are you saying 306 port? The default MySQL port should be 3306 :)

    And nope, you don't need to run PHP built-in webserver in order to get access to MySQL = MySQL has its own server. You need to run a web server only when you want to open a PHP file in browser. If you work in console - you don't need that webserver at all, you will see the output in console in any case.

    I hope it clarifies things for you :)

    Cheers!

  • 2020-04-23 ThingyBob

    Thanks for getting back to me. How is the mysqld service running if no set up is required and nothing has been installed prior? Are you saying this would work on a new Mac with no setup? Does the PHP built in server need to be running? Is it part of that? Doesn't the fact that I can't access localhost:306 like in the tutorial mean that the service isn't running?

  • 2020-04-22 Victor Bocharsky

    Hey ThingyBob,

    Hm, no, I don't see any required setup. Well, the only thing you need to check is your DB credentials. As you can see from the error, it failed on line 13. That's exactly where we create new PDO object with proper credentials. Please, check your credentials and probably tweak them to match yours and it should work.

    Cheers!

  • 2020-04-22 ThingyBob

    Is there some setup required for the db outside of this course? When I move the init_db.php file into my project root and try to access it in the browser I get the following error:

    Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2002] No such file or directory in /Users/thingybob/code/oo/init_db.php:13 Stack trace: #0 /Users/thingybob/code/oo/init_db.php(13): PDO->__construct('mysql:host=loca...', 'root', '') #1 {main} thrown in /Users/thingybob/code/oo/init_db.php on line 13

    And I can't just access localhost:306 to get to PMA like in the video

  • 2016-05-04 weaverryan

    Awesome!

  • 2016-05-03 That one guy

    nevermind.... I saw that this is handled in the next video....

  • 2016-05-03 That one guy

    For some reason I am getting this error if I try using the Jedi Fighter. I am inputting a value for the number of ships for both/ I have filled in the entire form correctly.
    ~~~~~~~~~~~~~~~~
    Don't forget to select some ships to battle!
    ~~~~~~~~~~~~~~~~

    It works without error for all other ships and this problem did not show up until the database connection was added to the program.
    All ships are showing up correctly in the ship selection area of the main page.

  • 2015-08-18 weaverryan

    Great catch Tim! I've fixed this at sha: https://github.com/knpunive... and sha: https://github.com/knpunive... - and just deployed those updates.

    Thanks!

  • 2015-08-16 Tim Whitehead

    Lines 13 & 20 are explicitly using the string 'root' instead of referencing $databaseUser in the init_db.php file.