Full code for: http://someguyjeremy.com/blog/database-testing-with-phpunit Place fixtures in a `fixture` folder.

FixtureTestCase.php

<?php
// we're loading the Database TestCase here
require 'PHPUnit' . DIRECTORY_SEPARATOR . 'Extensions' .
DIRECTORY_SEPARATOR . 'Database'  . DIRECTORY_SEPARATOR .
'TestCase.php';

class FixtureTestCase extends PHPUnit_Extensions_Database_TestCase {

  public $fixtures = array(
		'posts',
		'postmeta',
		'options'
	);

	private $conn = null;

	public function setUp() {
		$conn = $this->getConnection();
		$pdo = $conn->getConnection();

		// set up tables
		$fixtureDataSet = $this->getDataSet($this->fixtures);
		foreach ($fixtureDataSet->getTableNames() as $table) {
			// drop table
			$pdo->exec("DROP TABLE IF EXISTS `$table`;");
			// recreate table
			$meta = $fixtureDataSet->getTableMetaData($table);
			$create = "CREATE TABLE IF NOT EXISTS `$table` ";
			$cols = array();
			foreach ($meta->getColumns() as $col) {
				$cols[] = "`$col` VARCHAR(200)";
			}
			$create .= '('.implode(',', $cols).');';
			$pdo->exec($create);
		}

		parent::setUp();
	}

	public function tearDown() {
		$allTables =
		$this->getDataSet($this->fixtures)->getTableNames();
		foreach ($allTables as $table) {
			// drop table
			$conn = $this->getConnection();
			$pdo = $conn->getConnection();
			$pdo->exec("DROP TABLE IF EXISTS `$table`;");
		}

		parent::tearDown();
	}

	public function getConnection() {
		if ($this->conn === null) {
			try {
				$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
				$this->conn = $this->createDefaultDBConnection($pdo, 'test');
			} catch (PDOException $e) {
				echo $e->getMessage();
			}
		}
		return $this->conn;
	}

	public function getDataSet($fixtures = array()) {
		if (empty($fixtures)) {
			$fixtures = $this->fixtures;
		}
		$compositeDs = new
		PHPUnit_Extensions_Database_DataSet_CompositeDataSet(array());
		$fixturePath = dirname(__FILE__) . DIRECTORY_SEPARATOR . 'fixtures';

		foreach ($fixtures as $fixture) {
			$path =  $fixturePath . DIRECTORY_SEPARATOR . "$fixture.xml";
			$ds = $this->createMySQLXMLDataSet($path);
			$compositeDs->addDataSet($ds);
		}
		return $compositeDs;
	}

	public function loadDataSet($dataSet) {
		// set the new dataset
		$this->getDatabaseTester()->setDataSet($dataSet);
		// call setUp whateverhich adds the rows
		$this->getDatabaseTester()->onSetUp();
	}
}

MyTestCase.php

<?php
require 'FixtureTestCase.php';

class MyTestCase extends FixtureTestCase {

  public $fixtures = array(
		'posts',
		'postmeta',
		'options'
	);

	function testReadDatabase() {
		$conn = $this->getConnection()->getConnection();
		
		// fixtures auto loaded, let's read some data
		$query = $conn->query('SELECT * FROM posts');
		$results = $query->fetchAll(PDO::FETCH_COLUMN);
		$this->assertEquals(2, count($results));

		// now delete them
		$conn->query('TRUNCATE posts');

		$query = $conn->query('SELECT * FROM posts');
		$results = $query->fetchAll(PDO::FETCH_COLUMN);
		$this->assertEquals(0, count($results));

		// now reload them
		$ds = $this->getDataSet(array('posts'));
		$this->loadDataSet($ds);

		$query = $conn->query('SELECT * FROM posts');
		$results = $query->fetchAll(PDO::FETCH_COLUMN);
		$this->assertEquals(2, count($results));
	}

}

options.xml

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
  <table_data name="prefix_options">
		<row>
			<field name="option_id">1</field>
			<field name="option_name">siteurl</field>
			<field name="option_value">http://wordpress.local</field>
		</row>
		<row>
			<field name="option_id">2</field>
			<field name="option_name">home</field>
			<field name="option_value">http://wordpress.local</field>
		</row>
	</table_data>
</database>
</mysqldump>

postmeta.xml

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
  <table_data name="postmeta">
		<row>
			<field name="meta_id">1</field>
			<field name="post_id">1</field>
			<field name="meta_key">Something</field>
			<field name="meta_value">For Nothing</field>
		</row>
		<row>
			<field name="meta_id">2</field>
			<field name="post_id">2</field>
			<field name="meta_key">MetaKey</field>
			<field name="meta_value">MetaValue</field>
		</row>
		<row>
			<field name="meta_id">3</field>
			<field name="post_id">1000</field>
			<field name="meta_key">More</field>
			<field name="meta_value">Value</field>
		</row>
		<row>
			<field name="meta_id">4</field>
			<field name="post_id">1001</field>
			<field name="meta_key">Even More</field>
			<field name="meta_value">Value</field>
		</row>
	</table_data>
</database>
</mysqldump>

posts.xml

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
  <table_data name="posts">
		<row>
			<field name="ID">1</field>
			<field name="post_content">Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!</field>
			<field name="post_title">Hello world!</field>
			<field name="post_name">hello-world</field>
			<field name="guid">http://wordpress.local/?p=1</field>
		</row>
		<row>
			<field name="ID">2</field>
			<field name="post_content">This is an example of a WordPress page, you could edit this to put information about yourself or your site so readers know where you are coming from. You can create as many pages like this one or sub-pages as you like and manage all of your content inside of WordPress.</field>
			<field name="post_title">About</field>
			<field name="post_name">about</field>
			<field name="guid">http://wordpress.local/?page_id=2</field>
		</row>
	</table_data>
</database>
</mysqldump>

Thank you very much i've been looking for something like that for age's, but when i tried to run the test i got this error:
PHP Fatal error: Call to a member function getConnection() on a non-object in /home/al/adserver/adserver/test/dbTest/FixtureTestCase.php on line 13

why is that? i looked for mocking the getConnection but couldn't really find something...thx

Could you provide the code you used to dump the mysql to xml?

Edit: Nevermind this seems to work mysqldump -u root -p testdb --xml -t

Thank you very much it really help too much but what i want to know is did you load your actual database(real database) then imported to fixture or you are creating dump tables for testing then deleting it and how doesn't effect my real database?
lastly you can tell me **

mysqldump -u root -p testdb --xml -t

** this code what is?