Help Improve DB model/strategy, for use in refactoring SQL-ridden legacy code

Question:

Which design pattern do I use to refactor legacy SQL code into separate classes?

Details:

I am looking for a good model to help me refactor out DB code in a large legacy php project. I thought at first to use TableModule, but seeing how my SQL is complex, I think DomainModel may be better. But still I do not know how to implement it as description is too abstract. Maybe Service Layer is what I need... but to be honest, I am kind of lost.

Sample SQL that I deal with (one of many):

        SELECT
            db1.item.quantity,
            db1.sheet.serial,
            db2.work.id AS work,
            db3.part.id AS partid,
            db3.part.number,
            db3.part.description,
            db3.part.current_inv
        FROM db1.item
            LEFT JOIN db1.partial ON db1.partial.id = db1.item.db1_partial_id
            LEFT JOIN db1.sheet ON db1.sheet.id = db1.partial.db1_sheet_id
            LEFT JOIN db2.work ON db2.work.id = db1.sheet.db2_work_id
            LEFT JOIN db3.part ON db3.part.id = db1.item.db3_part_id
        WHERE
            db1.sheet.status != 'VOID' AND
            db1.item.id = "$itemid" AND
            db1.item.record = 0

It ties up 3 databases and multiple tables. Considering that I want to decouple SQL from my app, and decouple app from data handling, and having complex SQL queries like above, what do you suggest I do?

My current sample plan is

• find next available stray SQL inside legacy code


• determine what it is trying to do - i.e. save/update what data on what object or model.


• create or add to the class that best encompasses SQL. For example, above SQL checks if inventory has been used up for an item. So I can place it into some class called CheckInventoryData, and be happy with that.. until maybe I learn something that makes me want to change the name of the class, for example. i.e. HandleItem, and put that class in some directory structure for database handling, i.e. /db_handling_code


• have a function in that class saying isInventoryUsedUpForItem($item), or the like


• repeat....

While I think this will work, I do not feel too confident about my plan. I am hoping to find solidification of it in a more modern/industry accepted practice. And finding that plan is my question -- is there a better plan available. -- is there a model I should aspire towards?