Why is my PHP code returning incorrect results from a MySQL query?

Question

FreeBSD 11.2-RELEASE
Apache 2.4.43
PHP 7.4.4
MySQL 5.7.29

The application I'm writing code in (OpensourcePOS) uses the CodeIgniter framework (3.1.11), and I noticed it giving me incorrect query results using the CI QueryBuilder, so I handwrote the query bypassing CI altogether but even then the results are not correct.

This MySQL query run from phpMyAdmin and the CLI returns proper results for 'category' (VARCHAR(255))

SELECT * FROM ospos_items items
LEFT JOIN ospos_suppliers suppliers
ON suppliers.person_id = items.supplier_id
JOIN ospos_item_quantities qty
ON qty.item_id = items.item_id
WHERE items.deleted = 0
ORDER BY items.name ASC
LIMIT 1 OFFSET 0

the field 'category' is reported as 'Books' like it's supposed to be. The problem is that, I wasn't seeing the data I was expecting for 'Category' in the context of my application, So I ran the following debug code:

public function get_all($stock_location_id = -1, $rows = 0, $limit_from = 0)
{
    $this->db->from('items');
    $this->db->join('suppliers', 'suppliers.person_id = items.supplier_id', 'left');

    if($stock_location_id > -1)
    {
        $this->db->join('item_quantities', 'item_quantities.item_id = items.item_id');
        $this->db->where('location_id', $stock_location_id);
    }

    $this->db->where('items.deleted', 0);

    $this->db->order_by('items.name', 'asc');

    if($rows > 0)
    {
        $this->db->limit($rows, $limit_from);
    }

    $result_items = $this->db->get();

//DEBUG CODE
ob_start();
var_dump($result_items->result_array());
$res = ob_get_clean();
log_message('Error',"get_all results: $res");
//END DEBUG CODE
    return $result_items;
}

Gives me the following results. You can see that 'category' has a value 0. If there is no supplier_id (INT(11)), then 'category' shows up as null.

ERROR - 2020-04-10 16:21:34 --> get_all results: <pre class='xdebug-var-dump' dir='ltr'>
<small>/usr/local/www/public_html/pos_clcdesq/application/models/Item.php:278:</small>
<b>array</b> <i>(size=1)</i>
  0 <font color='#888a85'>=&gt;</font> 
    <b>array</b> <i>(size=26)</i>
      ...
      'category' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'0'</font> <i>(length=1)</i>
      'supplier_id' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'362'</font> <i>(length=3)</i>
      ...
</pre>

If I comment out $this->db->join('suppliers', 'suppliers.person_id = items.supplier_id', 'left'); the result is correct, but of course missing the data brought in by the left join on the suppliers database (which is needed). So naturally, I thought this was a CI Querybuilder bug so I added in a 2nd query to give me a basic select of everything in the items table that matches my ID

$query = $this->db->get_where('items',array('item_id'=>12203));
ob_start();
var_dump($query->result_array());
$res2 = ob_get_clean();
log_message('Error',"Custom query results: $res2");

and that result is correct, but doesn't give me the joins that I needed.

ERROR - 2020-04-10 16:21:34 --> Custom query results: <pre class='xdebug-var-dump' dir='ltr'>
<small>/usr/local/www/public_html/pos_clcdesq/application/models/Item.php:284:</small>
<b>array</b> <i>(size=1)</i>
  0 <font color='#888a85'>=&gt;</font> 
    <b>array</b> <i>(size=21)</i>
      ...
      'category' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'Books'</font> <i>(length=5)</i>
      'supplier_id' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'362'</font> <i>(length=3)</i>
      ...
</pre>

From there I skipped CI QueryBuilder and wrote the query out, but still using the CI framework to handle the connection.

$query2 = $this->db->query("SELECT * FROM `ospos_items` items LEFT JOIN `ospos_suppliers` suppliers ON suppliers.person_id = items.supplier_id JOIN ospos_item_quantities qty ON qty.item_id = items.item_id WHERE items.deleted = 0 ORDER BY items.name ASC LIMIT 1 OFFSET 0");
ob_start();
var_dump($query2->result_array());
$res3 = ob_get_clean();
log_message('Error',"HANDWRITTEN query results: $res3");

... and I got the same incorrect data for 'category'

ERROR - 2020-04-10 16:21:34 --> HANDWRITTEN query results: <pre class='xdebug-var-dump' dir='ltr'>
<small>/usr/local/www/public_html/pos_clcdesq/application/models/Item.php:290:</small>
<b>array</b> <i>(size=1)</i>
  0 <font color='#888a85'>=&gt;</font> 
    <b>array</b> <i>(size=28)</i>
      ...
      'category' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'0'</font> <i>(length=1)</i>
      'supplier_id' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'362'</font> <i>(length=3)</i>
      ...
</pre>

So I thought, for sure this is a CI bug! I bypassed the CI framework altogether and wrote the query out by hand, using PHP to handle everything

$username = "[REDACTED]";
$password = "[REDACTED]";
$database = "[REDACTED]";
$mysqli = new mysqli("localhost", $username, $password, $database);
$query3="SELECT * FROM `ospos_items` items LEFT JOIN `ospos_suppliers` suppliers ON suppliers.person_id = items.supplier_id JOIN ospos_item_quantities qty ON qty.item_id = items.item_id WHERE items.deleted = 0 ORDER BY items.name ASC LIMIT 1 OFFSET 0";
$result3 = $mysqli->query("$query3");
$mysqli->close();
ob_start();
var_dump($result3->fetch_assoc());
$res4 = ob_get_clean();
log_message('Error',"noCI query results: $res4");

annnndd... same incorrect 'category' data:

ERROR - 2020-04-10 16:21:34 --> noCI query results: <pre class='xdebug-var-dump' dir='ltr'>
<small>/usr/local/www/public_html/pos_clcdesq/application/models/Item.php:302:</small>
<b>array</b> <i>(size=28)</i>
  ...
  'category' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'0'</font> <i>(length=1)</i>
  'supplier_id' <font color='#888a85'>=&gt;</font> <small>string</small> <font color='#cc0000'>'362'</font> <i>(length=3)</i>
  ...
</pre>

Does anyone have any idea why the query in phpMyAdmin and CLI works, the query without the left join on suppliers works from my code, but adding in the left join doesn't work in straight PHP or CI?

What data do you get when doing this in PHPMYadmin? Can you supply with a screenshot? — bestprogrammerintheworld
– bestprogrammerintheworld, Commented Apr 13, 2020 at 8:02
I think your question just revealed the problem. When I went to grab the screenshot, I saw that phpMyAdmin was pulling two 'category' fields. After I investigated, I saw that the suppliers table has a 'category' column! It appears that the last 'category' is overwriting my result in the array. I guess now the question becomes, how can I either get the results to return unique values (items_category and supplier_category) or just select the category in the item table but still have the left join in the query. — objecttothis
– objecttothis, Commented Apr 13, 2020 at 8:18
Sorry I wasn't clearer. the PHP code as it's currently written gets a result from the ospos_items and ospos_suppliers tables. From the resulting array I can see that it's likely pulling in ospos_items.category into the associative array and then subsequently overwriting that with the value from ospos_suppliers.category. The one I want for my purposes is ospos_items.category, but it may be possible that someone in the future wants to see ospos_suppliers.category as well. — objecttothis
– objecttothis, Commented Apr 13, 2020 at 8:56
No problem. I have supplied with an answer. I wrote the answer before you answered in this comment, but I hope you understand what I mean? I actually just guessed which tables :-) — bestprogrammerintheworld
– bestprogrammerintheworld, Commented Apr 13, 2020 at 9:01

bestprogrammerintheworld · Accepted Answer · 2020-04-13 08:58:17Z

You have this query:

SELECT * FROM ospos_items items
LEFT JOIN ospos_suppliers suppliers
ON suppliers.person_id = items.supplier_id
JOIN ospos_item_quantities qty
ON qty.item_id = items.item_id
WHERE items.deleted = 0
ORDER BY items.name ASC
LIMIT 1 OFFSET 0

Based on your comment you retrieve two columns that have Category in your PHPMyAdmin:

Instead of using * you should use aliases in your SQL and define what columns you want included.

Something like: (I don't know where your categories are included but I hope you understand the concept)

SELECT items.category as category1, suppliers.category as category2, {other columns}
FROM ospos_items items
LEFT JOIN ospos_suppliers suppliers
ON suppliers.person_id = items.supplier_id
JOIN ospos_item_quantities qty
ON qty.item_id = items.item_id
WHERE items.deleted = 0
ORDER BY items.name ASC
LIMIT 1 OFFSET 0

Then when you fetch values in your code, you get category1 (items category) or category2 (suppliers category).

Collectives™ on Stack Overflow

Why is my PHP code returning incorrect results from a MySQL query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related