[Conny Westh]

Här kommer mer exempel på hur man kan lägga upp en klass som jobbar mot en specifik tabell.

Just den här tabellen heter Players och har tre kolumner. Först lite DDL-script för databasen.

delimiter $$

CREATE TABLE `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

Och så här kan man bygga den tabellspecifika klassen. Jag har bara gjort denna enkel så den returnerar resultatsetet men man kan givetvis plocka ut de intressanta värdena och returnera dem som andra objekt eller datatyper beroende på vad man vill ha och vad som blir enkelt att använda i sin applikation.

Det här är bara vad jag slängde ihop i går kväll och lite editeringar nu på förmiddagen, så jag har inte ägnat särskilt mycket tid åt det.

<?php
/* 
 Players.PHP
 Allows PHP to connect to your database
*/
class Player
{
	public $id = null;
	public $firstName = "";
	public $lastName = "";

	public function __construct()
	{
	}
		
	public function __toString()
	{
		return "(" . $id . ") " . $firstName . " " . $lastName; 
	}
		
	static function getPlayerById($id) 
	{
	    try
	    { 
			$sql = "SELECT * FROM players WHERE id = :id  ORDER BY firstname,lastname";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->bindParam(':id', $id);
			$dbStatement->execute();
			return $dbStatement->fetchAll(); 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	}
	 
	static function insertPlayer($firstName, $lastName) 
	{
	    try
	    { 
			$sql = "INSERT INTO players(firstname,lastname) VALUES(:firstName, :lastName)";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->bindParam(':firstName', $firstName);
			$dbStatement->bindParam(':lastName', $lastName);
			$dbStatement->execute();
			return null; // $dbStatement->newId; 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	}
	 
	static function updatePlayer($id, $firstName, $lastName) 
	{
	    try
	    { 
			$sql = "UPDATE `players` ";
			$sql = $sql + "SET ";
			$sql = $sql + "`firstname` = :firstName, ";
			$sql = $sql + "`lastname` = :lastName ";
			$sql = $sql + "WHERE id= :id";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->bindParam(':firstName', $firstName);
			$dbStatement->bindParam(':lastName', $lastName);
			$dbStatement->execute();
			return null; // $dbStatement->newId; 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	}
	 
	static function getAllPlayers() 
	{
	    try
	    { 
			$sql = "SELECT * FROM players ORDER BY firstname,lastname";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->execute();
			return $dbStatement->fetchAll(); 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	} 

	static function deletePlayer($id) 
	{
	    try
	    { 
			$sql = "DELETE FROM players WHERE id = :id";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->bindParam(':id', $id);
			$dbStatement->execute();
			return null; // $dbStatement->newId; 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	}
	 
	static function deleteAllPlayers() 
	{
	    try
	    { 
			$sql = "DELETE FROM players;";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->execute();
			return $dbStatement; 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	} 

	static function getCountOfAllPlayers() 
	{
	    try
	    { 
			$sql = "SELECT Count(*) AS Antal FROM players";
			
		    $dbConn = ApplicationDatabaseTest::getConnection();
			$dbStatement = $dbConn->prepare($sql);
			$dbStatement->execute();
			return $dbStatement->fetchAll(); 
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	} 

	static function getAllPlayersWithQuery() 
	{
		try 
		{
		    $dbConn = ApplicationDatabaseTest::getConnection();
		    return $dbConn->query('SELECT * from Players ORDER BY firstname,lastname');
		}
		catch (PDOException $e) 
		{
	    	throw new Exception("Error: " . $e->getMessage() . "",0,$e); 
		    die();
			return null; 
		}
		catch (Exception $e)
		{
	    	throw new Exception('Error: Can not connect to database.',0,$e); 
			return null; 
		}
	}
} 

?>

Testkod kommer i mitt nästa inlägg....