How to relate 2d PHP array data with a result set from CodeIgniter's query builder by primary key

Question

I have variable array($avail) like this :

$avail = [
    ['item_id' => 1, 'qty_avail' => 44],
    ['item_id' => 4, 'qty_avail' => 33],
    ['item_id' => 6, 'qty_avail' => 50],
];

And I want select with CodeIgniter:

$this->db->select(
    'item_id,'
    . $avail[0]['qty_avail'] . ' as "qty_avail", 
        qty_tocome, qty_togo'
);
$this->db->order_by('item_id', 'asc');
$this->db->where('item_category_id = ', $category_id);
$query = $this->db->get('t_inventory i', $limit, $offset)->result_array();
print_r($query);

The result is all [qty_avail] always $avail[0] :

Array (
[0] => Array
    (
        [item_id] => 1
        [qty_avail] => 44
        [qty_tocome] => 0.00
        [qty_togo] => 0.00
    )

[1] => Array
    (
        [item_id] => 4
        [qty_avail] => 44
        [qty_tocome] => 0.00
        [qty_togo] => 0.00
    )

[2] => Array
    (
        [item_id] => 6
        [qty_avail] => 44
        [qty_tocome] => 0.00
        [qty_togo] => 0.00
    )
);

But I want the qty_avail value which relates to each item_id, like this:

Array (
[0] => Array
    (
        [item_id] => 1
        [qty_avail] => 44
        [qty_tocome] => 0.00
        [qty_togo] => 0.00
    )

[1] => Array
    (
        [item_id] => 4
        [qty_avail] => 33
        [qty_tocome] => 0.00
        [qty_togo] => 0.00
    )

[2] => Array
    (
        [item_id] => 6
        [qty_avail] => 50
        [qty_tocome] => 0.00
        [qty_togo] => 0.00
    )
);

Thamilhan · Accepted Answer · 2016-09-14 06:20:27Z

1

You can use array_replace_recursive like this:

<?php
$arr = [
    ["item_id" => 1,
    "qty_tocome" => 0.00,
    "qty_togo" => 0.00],
    ["item_id" => 4,
    "qty_tocome" => 0.00,
    "qty_togo" => 0.00],
    ["item_id" => 6,
    "qty_tocome" => 0.00,
    "qty_togo" => 0.00]
];

$avail = [
["qty_avail" => 44],
["qty_avail" => 33],
["qty_avail" => 50]
];

$finalArr = array_replace_recursive($arr,$avail);
print_r($finalArr);

So your output will be:

Array
(
    [0] => Array
        (
            [item_id] => 1
            [qty_tocome] => 0
            [qty_togo] => 0
            [qty_avail] => 44
        )

    [1] => Array
        (
            [item_id] => 4
            [qty_tocome] => 0
            [qty_togo] => 0
            [qty_avail] => 33
        )

    [2] => Array
        (
            [item_id] => 6
            [qty_tocome] => 0
            [qty_togo] => 0
            [qty_avail] => 50
        )

)

But make sure that the quantity gets assigned in the right order, since you are using $this->db->order_by('item_id', 'asc');.

answered Sep 14, 2016 at 6:20

Thamilhan

13.3k5 gold badges40 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Oct 16 at 23:52

This is not reliable because there is no guarantee that the results from the query will have item_id values in the exact same order as the PHP array's item_id values.

mickmackusa · Accepted Answer · 2025-10-16 23:42:49Z

You can use a JOIN or LEFT JOIN (depending on your logical requirements) to merge the two payloads explicitly on related item_id values. Depending on your database dialect and version, how you implement the temporary/derived/virtual table may vary. Using UNION or UNION ALL will probably be the most welcoming approach (I'm not totally sure about that claim).

My demo will assume the following variable payloads. These should all be passed in to the model method as arguments.

$category_id = 10;
$limit = 3;
$offset = 0;
$avail = [
    ['item_id' => 1, 'qty_avail' => 44],
    ['item_id' => 4, 'qty_avail' => 33],
    ['item_id' => 6, 'qty_avail' => 50],
];

/**
 * @param int $category_id
 * @param array<int, array{item_id: int, qty_avail: int}> $avail
 * @param ?int $limit
 * @param ?int $offset
 * @return array|array[]
 */
public function getWithAvailability(int $category_id, array $avail, ?int $limit = null, ?int $offset = null): array
{
    if (!$avail) {
        $avail = [['item_id' => null, 'qty_avail' => 0]];
    }
    $parts = [];
    foreach ($avail as $row) {
        $parts[] = vsprintf('SELECT %s item_id, %s qty_avail', $this->db->escape($row));
    }
    $sub = implode(' UNION ALL ', $parts);

    return $this->db
        ->select('i.item_id, v.qty_avail')
        ->select("COALESCE(i.qty_tocome, '0.00') qty_tocome, COALESCE(i.qty_togo, '0.00') qty_togo")
        ->join("($sub) v", 'item_id', 'left')
        ->order_by('i.item_id')
        ->get_where('t_inventory i', ['i.item_category_id' => $category_id], $limit, $offset)
        ->result_array();
}

Rendered SQL (I added some spacing for readability): SQLize Demo

SELECT `i`.`item_id`, `v`.`qty_avail`, COALESCE(i.qty_tocome, '0.00') qty_tocome, COALESCE(i.qty_togo, '0.00') qty_togo
FROM `t_inventory` `i`
LEFT JOIN (
    SELECT 1 item_id, 44 qty_avail
    UNION ALL
    SELECT 4 item_id, 33 qty_avail
    UNION ALL
    SELECT 6 item_id, 50 qty_avail
) v USING (`item_id`)
WHERE `i`.`item_category_id` = 10
ORDER BY `i`.`item_id`
LIMIT 3

mickmackusa · Accepted Answer · 2025-10-16 23:55:22Z

0

try this

$query = $this->db->select(
    'item_id,qty_avail as qty_avail, qty_tocome,qty_togo'
);
->order_by('item_id', 'asc')
->where('item_category_id ', $category_id)
->limit($limit,$offset);
->get('t_inventory i');
$data = $query->result_array();
foreach ($data as $a) {
    $a['qty_avail'] = $data[0]['qty_avail'];
}
print_r($a);

edited Oct 16 at 23:55

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Sep 23, 2016 at 14:05

Arun Sharma

131 silver badge9 bronze badges

1 Comment

mickmackusa Oct 16 at 23:53

These in-loop assignments are not making any permanent change to the $data array. This is an array of arrays, not an array of objects and you are not modifying by reference. This script makes no attempt to relate the item_id values between the two payloads. This answer is riddled with syntax errors - most obviously the failures in chaining query builder methods.

Collectives™ on Stack Overflow

How to relate 2d PHP array data with a result set from CodeIgniter's query builder by primary key

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related