PHP PDO查询类构造函数

Question

I'm still getting to grips with OOP, but am trying to create an 'Order' object using data obtained by a singleton database connection object (#1).

I'm achieving this by getting an array of order numbers and then iterating over them (#2), creating Order objects, passing the order number to the class, from where I want the Order class to query the database using the singleton database connection to obtain further information about each order and populate it's properties (#3). My thinking is that the Order class will be easier to maintain and very flexible if it is 'self contained' beyond the order identifier.

I'm aware I could just get the data from getOrders() and pass it into the class, but I don't want to have to amend 50 different queries if I decide I need another field!

This works to a point, but if I have 10 orders, only the last order object is populated with data from the database. I'm assuming that the database connection is being reused before it has time to complete and populate it's properties?

Here's some code...

class Example {
    protected $dBCon;

    #1
    //class constructor
    public function __construct() {
    //connect to SQL database
        $this->dBCon = DB::getInstance();
    }

    #2
    public function getOrders() {
        $orders = array();
        $sql="SELECT ORDNUMBER FROM ORDERS";
        $result = $this->dBCon->executeCommand($sql);
        foreach ($result as $row) {
            $order = new Order($row['ORDNUMBER']);
            $orders[] = $order;
        }
        return $orders;
        }

}

class Order extends Example {
    public $order_number;
    public $customer;
    public $price_list; 

    #3
    public function __construct($order_number) {        
    if ($order_number) {
        //create Order from existing order
        //connect to SQL database
        $this->dBCon = DB::getInstance();
        $sql = "SELECT * FROM ORDERS WHERE ORDNUMBER = ?";
        $result = $this->dBCon->executePreparedStatement($sql, array($order_number));
        $this->order_number = $order_number;
        foreach ($result as $row) {             
            $this->customer = new Customer($row['CUSTOMER']);
            $this->price_list = $row['PRICELIST'];
        }
    }
}

So calling getOrders() gives me, for example, 10 order objects, however only the last one contains any data. You can also see that I want to then do something similar with a Customer object for each order.

I have also tried creating a new instance of the database connection, and this does get data for each object created, however I'm aware that I'm potentially creating alot of database connections!

Is this the right way to go about this or have I completely got the wrong end of the OOP stick!?

Answer 1

I would make the Order really simple first:

class Order
{
    public $order_number;
    public $customer;
    public $price_list; 
}

It knows nothing about SQL or any form of database. Then, you have the choice of loading these objects in one or two steps; let's do it in a single step for now.

class OrderGateway
{
    public static function getOrders(PDO $db)
    {
        $orders = array();

        $stmt = $db->query('SELECT * FROM `orders`');
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $order = new Order();
            $order->order_number = $row['ORDNUMBER'];
            $order->customer = new Customer($row['CUSTOMER']);
            $order->price_list = $row['PRICELIST'];

            $orders[] = $order;
        }

        return $orders;
    }
}

$orders = OrderFactory::getOrders($db);

This can be further improved by introducing a data mapper class that sits between the gateway and the data classes. You can read about this when you search for "data mapper pattern".