MySQL terminal results != PHP->MySQL results

Question

I'm trying to figure out why results returned via a MySQL terminal query don't match those returned by a MySQL query executed by PHP.

Here's the MySQL used in the shell:

SELECT * FROM SANKEY_NODE AS n
  LEFT OUTER JOIN TYPE_DETAIL as td
    ON n.TYPE_DETAIL_ID = td.TYPE_DETAIL_ID
  LEFT OUTER JOIN GRAPH_TYPE as t
    ON td.GRAPH_TYPE_ID = t.GRAPH_TYPE_ID
WHERE CHART_ID = 39;

Here's the PHP:

function get_nodes_by_chart_id($con, $chart_id) {
  $sql = 'SELECT * FROM SANKEY_NODE AS n
            LEFT OUTER JOIN TYPE_DETAIL as td
              ON n.TYPE_DETAIL_ID = td.TYPE_DETAIL_ID
            LEFT OUTER JOIN GRAPH_TYPE as t
              ON td.GRAPH_TYPE_ID = t.GRAPH_TYPE_ID
          WHERE CHART_ID = '.$chart_id.';';
  $result = mysqli_query($con, $sql);
  return results_to_array($result);
}

function results_to_array($results) {
  $rows = array();
  while($row = mysqli_fetch_assoc($results)) {
    $rows[] = $row;
  }
  return $rows;
}

Both queries return a dozen results, however, they differ in their representation of three results. Those three results are special because they have a TYPE_DETAIL_ID value in the SANKEY_NODE table that is not present in the TYPE_DETAIL table.

In the MySQL shell, the TYPE_DETAIL_ID value for the three affected nodes is displayed, whereas in the results returned by PHP, the TYPE_DETAIL_ID value is null. Does anyone know what might cause this difference? I'd be very grateful for any insight others can offer!

Table Structures

mysql> describe SANKEY_NODE;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| NODE_NAME      | varchar(100) | NO   |     | NULL    |       |
| NODE_PARENT    | varchar(20)  | YES  |     | NULL    |       |
| CHART_ID       | int(11)      | NO   | PRI | NULL    |       |
| NODE_TYPE      | varchar(100) | NO   |     | NULL    |       |
| TYPE_DETAIL_ID | varchar(20)  | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

mysql> describe TYPE_DETAIL;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| COMPANY_ID       | int(11)      | NO   |     | NULL    |       |
| GRAPH_TYPE_ID    | int(11)      | NO   | PRI | NULL    |       |
| TYPE_DETAIL_CD   | varchar(20)  | NO   | PRI | NULL    |       |
| TYPE_DETAIL_NAME | varchar(100) | NO   |     | NULL    |       |
| TYPE_DETAIL_DESC | varchar(200) | YES  |     | NULL    |       |
| TYPE_DETAIL_ID   | int(11)      | NO   |     | NULL    |       |
| TYPE_IMAGE_ID    | int(11)      | YES  |     | NULL    |       |
| ACTIVE_FLAG      | bit(1)       | NO   |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

mysql> describe GRAPH_TYPE;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| COMPANY_ID           | int(11)      | NO   | PRI | NULL    |                |
| GRAPH_TYPE_ID        | int(11)      | NO   | UNI | NULL    | auto_increment |
| TYPE_CD              | varchar(20)  | NO   | PRI | NULL    |                |
| TYPE_NAME            | varchar(100) | NO   |     | NULL    |                |
| TYPE_COLOR           | varchar(50)  | NO   |     | NULL    |                |
| TYPE_HIGHLIGHT_COLOR | varchar(50)  | NO   |     | NULL    |                |
| ACTIVE_FLAG          | bit(1)       | NO   |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

Comment Responses

@Cedric, running the query with just the first JOIN yields the same results. The three values whose TYPE_DETAIL_ID is present in SANKEY_NODE but not in TYPE_DETAIL_ID have a defined TYPE_DETAIL_ID in the shell results but not in the results via PHP (see results below). As for syntax, I'm a fan of the philosophy that "explicit is better than implicit".

@jcaron, the TYPE_DETAIL_ID values are either sequences of integers or ascii strings, e.g.:

mysql> SELECT * FROM SANKEY_NODE AS n   LEFT OUTER JOIN TYPE_DETAIL as td     ON n.TYPE_DETAIL_ID = td.TYPE_DETAIL_ID WHERE CHART_ID = 3;
+--------------------------+-------------+----------+-------------+----------------+------------+---------------+----------------+--------------------+--------------------+----------------+---------------+-------------+
| NODE_NAME                | NODE_PARENT | CHART_ID | NODE_TYPE   | TYPE_DETAIL_ID | COMPANY_ID | GRAPH_TYPE_ID | TYPE_DETAIL_CD | TYPE_DETAIL_NAME   | TYPE_DETAIL_DESC   | TYPE_DETAIL_ID | TYPE_IMAGE_ID | ACTIVE_FLAG |
+--------------------------+-------------+----------+-------------+----------------+------------+---------------+----------------+--------------------+--------------------+----------------+---------------+-------------+
| CRD                      | SYS         |        3 | System      | 101004         |       7777 |             1 | CRD            | Charles River      | Charles River      |         101004 |          NULL |            |
| FactSet                  | SYS         |        3 | System      | 101012         |       7777 |             1 | FACTSET        | Factset            | Factset            |         101012 |          NULL |            |
| MSCI                     | SYS         |        3 | System      | 101016         |       7777 |             1 | RISKMETRICS    | MSCI RiskWorld     | MSCI RiskWorld     |         101016 |          NULL |            |
| Trade Execution          | FUN         |        3 | Function    | 109007         |       7777 |             9 | TE             | Trade Execution    | Trade Execution    |         109007 |          NULL |            |
| Portfolio Mgmt           | FUN         |        3 | Function    | 109003         |       7777 |             9 | PM             | Portfolio Mgmt     | Portfolio Mgmt     |         109003 |          NULL |            |
| Performance & Risk       | FUN         |        3 | Function    | 109002         |       7777 |             9 | PMR            | Performance & Risk | Performance & Risk |         109002 |          NULL |            |
| Operations               | FUN         |        3 | Function    | 109006         |       7777 |             9 | OPS            | Operations         | Operations         |         109006 |          NULL |            |
| Decision Making          | FUN         |        3 | Function    | 109001         |       7777 |             9 | DM             | Decision Making    | Decision Making    |         109001 |          NULL |            |
| Compliance               | FUN         |        3 | Function    | 109005         |       7777 |             9 | COMP           | Compliance         | Compliance         |         109005 |          NULL |            |
| Portfolio Rebalance      | SFUN        |        3 | SubFunction | 201091         |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| Position Reconciliation  | SFUN        |        3 | SubFunction | 201092         |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| PreTrade Compliance      | SFUN        |        3 | SubFunction | 201096         |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| Step-outs                | SFUN        |        3 | SubFunction | 201109         |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| Trade Matching           | SFUN        |        3 | SubFunction | 201125         |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| Trade Settlement         | SFUN        |        3 | SubFunction | 201129         |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| Functions                |             |        3 | Function    | FUN            |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| SubFunction              |             |        3 | SubFunction | SFUN           |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
| Systems                  |             |        3 | System      | SYS            |       NULL |          NULL | NULL           | NULL               | NULL               |           NULL |          NULL | NULL        |
+--------------------------+-------------+----------+-------------+----------------+------------+---------------+----------------+--------------------+--------------------+----------------+---------------+-------------+
18 rows in set, 225 warnings (0.00 sec)

What if you remove the "OUTER" keywords ? It should give exactly the same result, so shorter is sweeter. — Cedric, Jan 07 '18 at 15:31
What do those ids look like? Since you’re using varchar, you may be using special characters (accented characters etc) and use different locales in the two clients which would give different results. Please provide the relevant data. — jcaron, Jan 07 '18 at 15:34
Oh, actually hadn’t noticed you have varchar in one table and int in the other, so that’s probably not the reason. Nevertheless, please provide the values which are interpreted differently. — jcaron, Jan 07 '18 at 15:35
There are two columns both called TYPE_DETAIL_ID, so PHP is probably giving you only one of them. You could try naming exactly which columns you want in your SELECT statement, and if you need both of those columns, you may need to alias one of them to a different name — astrangeloop, Jan 07 '18 at 15:38
@astrangeloop I have a hunch you nailed it, but why would the results differ if this were the case? I would expect the interpretation to be consistent in the shell and via PHP. — duhaime, Jan 07 '18 at 15:40
SQL Injection is possible in your query, take a look at https://stackoverflow.com/questions/16282103/php-mysqli-prevent-sql-injection and use parameters or escape strings for safety. — SteveB, Jan 07 '18 at 15:49
@duhaime I’m not in front of a computer so I can’t check, but I think that php will give you the rightmost column with the same name, whereas command line mysql gives you both columns. Duplicate column names are handled by the client, so behaviour can differ between clients. — astrangeloop, Jan 07 '18 at 15:49

score 2 · Accepted Answer · answered Jan 07 '18 at 15:43

2

Here is an easy workaround, specify the table name for each column :

SELECT SANKEY_NODE.TYPE_DETAIL_ID FROM SANKEY_NODE AS n

That way, you will be sure to have the data that you expect

answered Jan 07 '18 at 15:43

Cedric

5,135
11
42
61

1

Yeehaw! Thanks very much @Cedric! – duhaime Jan 07 '18 at 15:58

MySQL terminal results != PHP->MySQL results

1 Answers1