<!-- Collection of code snippets by Arne Vajhøj -->
<!-- (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) -->
<?php

class OrderLine {
    public $itemNo;
    public $itemName;
    public $itemPrice;
    public $quantity;
    public function __construct($itemNo = 0, $itemName = '', $itemPrice = '0.00', $quantity = 1) {
        $this->itemNo = $itemNo;
        $this->itemName = $itemName;
        $this->itemPrice = $itemPrice;
        $this->quantity = $quantity;
    }
    public function getPrice() {
        return bcadd((string)$this->quantity, $this->itemPrice);
    }
    public function __toString() {
        return sprintf('(%d,%s,%s,%d)', $this->itemNo, $this->itemName, $this->itemPrice, $this->quantity);
    }
}

class Order {
    public $orderId;
    public $customer;
    public $items;
    public function __construct($orderId = 0, $customer = '') {
        $this->orderId = $orderId;
        $this->customer = $customer;
        $this->items = array();
    }
    public function getPrice() {
        $res = '0.00';
        foreach($this->items as $ol) {
            $res = bcadd($res, $ol->getPrice());
        }
        return $res;
    }
    public function __toString() {
        return sprintf('(%d,%s,%s)', $this->orderId, $this->customer, implode(',', $this->items));
    }
}

// hack to map read object into the real class
function map($inobj) {
    $outobj = new Order();
    $outobj->orderId = $inobj->orderId;
    $outobj->customer = $inobj->customer;
    foreach($inobj->items as $it) {
        $outobj->items[] = new OrderLine($it->itemNo, $it->itemName, $it->itemPrice, $it->quantity);
    }
    return $outobj;
}

function dump($label, $con, $sel) {
    echo "$label:\r\n";
    mysqli_stmt_execute($sel) or die(mysqli_error(mysqli_error($con)));
    $rs = mysqli_stmt_get_result($sel);
    while($row = mysqli_fetch_array($rs, MYSQLI_ASSOC)) {
        $doc = $row['doc'];
        echo "$doc\r\n";
        $o = map(json_decode($doc));
        echo "$o\r\n";
    }
    mysqli_stmt_close($sel);
}

// open
$con = mysqli_connect('localhost', 'root', 'hemmeligt', 'Test') or die(mysqli_connect_error());
mysqli_query($con, 'CREATE TABLE order_table (id INTEGER NOT NULL, doc JSON, PRIMARY KEY(id))') or die(mysqli_error($con));
// setup
$ins = mysqli_prepare($con, 'INSERT INTO order_table VALUES(?,?)') or die(mysqli_error($con));
$o1 = new Order(1, 'A A');
$o1->items[] = new OrderLine(1, 'A good PHP book', '19.95', 1);
$o1id = 1;
$o1json = json_encode($o1);
mysqli_stmt_bind_param($ins, 'is', $o1id, $o1json);
mysqli_stmt_execute($ins) or die(mysqli_error(mysqli_error($con)));
$o2 = new Order(2, 'B B');
$o2->items[] = new OrderLine(1, 'ASUS MB', '249.00', 1);
$o2->items[] = new OrderLine(2, 'i5 CPU', '299.00', 1);
$o2->items[] = new OrderLine(3, '4 GB kit', '29.00', 4);
$o2id = 2;
$o2json = json_encode($o2);
mysqli_stmt_bind_param($ins, 'is', $o2id, $o2json);
mysqli_stmt_execute($ins) or die(mysqli_error(mysqli_error($con)));
mysqli_stmt_close($ins);
// query
$sel = mysqli_prepare($con, "SELECT doc FROM order_table") or die(mysqli_error($con));
dump("all", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.orderId' = ?") or die(mysqli_error($con));
$id = 2;
mysqli_stmt_bind_param($sel, 'i', $id);
dump("where id=2", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.customer' = ?") or die(mysqli_error($con));
$cust = 'A A';
mysqli_stmt_bind_param($sel, 's', $cust);
dump("where customer = 'A A'", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.customer' LIKE ?") or die(mysqli_error($con));
$cust = 'A%';
mysqli_stmt_bind_param($sel, 's', $cust);
dump("where customer like 'A%'", $con, $sel);
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE JSON_CONTAINS(doc->>'$.items[*].itemName', JSON_QUOTE(?))") or die(mysqli_error($con));
$itm = 'i5 CPU';
mysqli_stmt_bind_param($sel, 's', $itm);
dump("where items.name='i5 CPU'", $con, $sel);
// **** I can't get it working ****  // where items.name like '%PHP%'
$sel = mysqli_prepare($con, "SELECT doc FROM order_table WHERE doc->>'$.orderId' IN ('" . 1 . "','" . 2 . "')") or die(mysqli_error($con)); // prepared statement and variable parameters is a problem
dump("where id in (1,2)", $con, $sel);
// close
mysqli_close($con);

?>