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
– Cedric, Commented Jan 7, 2018 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
– jcaron, Commented Jan 7, 2018 at 15:34
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
– astrangeloop, Commented Jan 7, 2018 at 15:38
SQL Injection is possible in your query, take a look at stackoverflow.com/questions/16282103/… and use parameters or escape strings for safety. — SteveB
– SteveB, Commented Jan 7, 2018 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
– astrangeloop, Commented Jan 7, 2018 at 15:49

Cedric · Accepted Answer · 2018-01-07 15:43:14Z

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 7, 2018 at 15:43

Cedric

5,32311 gold badges46 silver badges63 bronze badges

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

1 Comment

duhaime Over a year ago

Yeehaw! Thanks very much @Cedric!

Collectives™ on Stack Overflow

MySQL terminal results != PHP->MySQL results

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related