How do I write object classes effectively when dealing with table joins?

Question

I should start by saying I'm not now, nor do I have any delusions I'll ever be a professional programmer so most of my skills have been learned from experience very much as a hobby.

I learned PHP as it seemed a good simple introduction in certain areas and it allowed me to design simple web applications.

When I learned about objects, classes etc the tutor's basic examnples covered the idea that as a rule of thumb each database table should have its own class. While that worked well for the photo gallery project we wrote, as it had very simple mysql queries, it's not working so well now my projects are getting more complex. If I require data from two separate tables which require a table join I've instead been ignoring the class altogether and handling it on a case by case basis, OR, even worse been combining some of the data into the class and the rest as a separate entity and doing two queries, which to me seems inefficient.

As an example, when viewing content on a forum I wrote, if you view a thread, I retrieve data from the threads table, the posts table and the user table. The queries from the user and posts table are retrieved via a join and not instantiated as an object, whereas the thread data is called using my Threads class.

So how do I get from my current state of affairs to something a little less 'stupid', for want of a better word. Right now I have a DB class that deals with connection and escaping values etc, a parent db query class that deals with the common queries and methods, and all of the other classes (Thread, Upload, Session, Photo and ones thats aren't used Post, User etc ) are children of that.

Do I make a big posts class that has the relevant extra attributes that I retrieve from the users (and potentially threads) table?
Do I have separate classes that populate each of their relevant attributes with a single query? If so how do I do that?
Because of the way my classes are written, based on what I was taught, my db update row method, or insert method both just take the attributes as an array and update all of that, if I have extra attributes from other db tables in each class then how do I rewrite those methods as obbiously updating automatically like that would result in errors?

In short I think my understanding is limited right now and I'd like some pointers when it comes to the fundamentals of how to write more complex classes.

Edit:

Thanks for the answers so far they've given me lots of pointers and thoughts and a lot of reading material. What I would like though is maybe an idea of how different people have decided to handle a simple table join with any amount of classes? Did you add attributes to the classes? Query from outside the class then pass the results into each class? Something else?

Answer 1

Many of your worries can be answered by inspecting the existing solutions found in well-tested frameworks such as CakePHP, symfony and Zend Framework. Examining their approaches and peeking under the hood should shed light on your questions. Who knows? You may even decide to write future projects using them!

They've spent years putting their heads together to tackle these problems. Take advantage!

Answer 2

Checkout Doctrine:

Here is an example of a forum application using Doctrine.

http://www.doctrine-project.org/documentation/manual/1_2/en/real-world-examples#forum-application

Answer 3

This is too broad to be answered without going into epic length.

Basically, there is four prominent Data Source Architectural Patterns from Patterns of Enterprise Architecture: Table Data Gateway, Row Data Gateway, Active Record and Data Mapper. These can be found implemented in the common php frameworks in some variation. These are easy to grasp and implement.

Where it gets difficult is when you start to tackle the impedance mismatch between the database and the business objects in your application. To do so, there are a number of Object-Relational Behavioral, Structural and Metadata Mapping Patterns, like Identity Maps, Lazy Loading, Query Objects, Repositories, etc. Explaining these is beyond scope. They cover almost 200 pages in PoEAA.

What you can look at is Doctrine or Propel - the two most well known PHP ORM - that implement most of these patterns and which you could use in your application to replace your current database access handling.

Answer 4

Entire books have been written about how to design a set of classes to fit a database schema.

Long story short: there is no one-size-fits-all way to do it, you have to make a lot of design decisions about the trade offs you want to make on an application-by-application basis.

You can find a library or framework to help, keywords: ActiveRecord, ORM (Object Relational Mapper)

P.S. You have no idea the potential for soul-killing analysis paralysis and over designing you can get into. Do the simplest thing that can possibly work for your app.

Code sample for my (below) comment:

$post = new PublishedPost($data);
$edit = $post->setTitle($newTitle);
$edit->save();