How do I seperate my SQL and Business Logic in this example?

I have been looking for help online on how to design my php classes to separate my business logic and my data layers. I had started to design a class I thought was pretty cool but then discovered PDO and ADODB and had a nice facepalm moment realizing I was recreating the wheel. My problem now is I still don't quite understand how to separate my logic and all my SQL queries.

I stripped out mostly everything from my DB schema and put down these two tables because I think they'd be pretty easy to understand. Let's say I have Files with the path where they are saved on my server which are in Directories (these Directories can be in other Directories). Let's say I'll need basic features like fetching the root Directory from one of my files or get a list of Directories inside the current Directory.

+--------------------+ +----------------+
| Files              | | Directories    |
+--------------------+ +----------------+
| id                 | | id             |
| name               | | name           |
| path               | | directory_id   |
| directory_id       | +----------------+
+--------------------+

Would a well designed class look like this:

class Files {
    public function __construct( $file_id ) {}
    public function getDirectory() {}
    public function getRootDirectory() {}
    public function getPath() {}
    public function move( $directory_id ) {}
}

class Directories {
    public function __construct( $directory_id ) {}
    public function getRootDirectory() {}
    public function move( $directory_id ) {}
    public function listContent() {}
}

Where I'd fetch all the data for my objects in the constructor using the ID passed through the constructor? Should I pass a PDO object in the constructor as well or am I missing some valuable design pattern? Should all the SQL be hardcoded in here? One thing I get with PDO is that I could switch from MySQL to MSSQL very easily but both have differences in the syntax of their SQL so wouldn't still cause me problems?

I know these are more theoretical questions without ONE good开发者_开发技巧 answer but I lack work colleagues to discuss this with (I'm not kidding when I say they don't even know what a design pattern is) so I find myself turning to the web. If my question is too vague feel free to suggest a good discussion type place I could ask this kind of stuff and I'd be extremely grateful :)

---

It's hard to tell just how much this would apply to your real-world situation, but you should probably look up what an ORM (Object-Relational Mapping) can do for you. There are many, many very useful ORM solutions out there, that can make this stuff much simpler. They're not all right for every solution, of course, but ORMs can help you implement your logic in the middle tier, where it belongs (usually).

---

As readed from your comment if you want to keep your SQL working with all DMBS I think you can use some SQL Abstraction Layer.

I suggest you Zend_DB_Select http://framework.zend.com/manual/en/zend.db.select.html

If you read its feature it's stated:

• Database-independent abstraction of some parts of the SQL query;