PHPUnit - test the validity of an SQL Query

I strongly agree on the part of not mocking out the PDO. At some point i want to make sure my queries work against a real database. While this might not be a unit test anymore, technically. For me it offers so much more to know that my code that handles data storage really works against the db.

What i tend to do is creating a sort of Data Access class for each Class that needs to talk to the DB and so separating out most of the business logic from the db access code.

That way i can mock out the data access when testing the classes and after that set up a "Test Database" for each "data access class" and see if those work.

@zerkms Answer (+1) already linked http://phpunit.de/manual/current/en/database.html and the only other resource i have found to be of value when it comes to DB-Testing is the Book Real-World Solutions for Developing High-Quality PHP Frameworks and Applications that has a big chapter covering this subject.

Should I separate test requiring the "test database" from the tests that are autonomous ?

Only if your testsuite gets really really big and you have runtime issues that force you to say "hitting even a testing database just takes to long for all my tests so i run those only on a continuous integration server and not while developing.

Yes, it is the common practice.

It is also the special methods for loading fixtures: http://phpunit.de/manual/current/en/database.html

What about second question: no, you should not separate them. Your tests should test the behaviour, not the details of implementation. Join test cases into test classes by logic.