mysql query works in phpmyadmin but not in node.js

Question

I have a query like this...

SELECT *
FROM `000027`,`000028`
WHERE `000027`.id=(SELECT max(`000027`.id) FROM `000027`)
AND `000028`.id=(SELECT max(`000028`.id) FROM `000028`)

which returns something like this in phpmyadmin...

id time value id time value

However, in react.js it is only returning one of these like this...

id time value

2 questions, Why is it doing this? and, how can I get it to return both instead of one?

my node.js code...

const sqlSelect = "SELECT * FROM `000027`,`000028` WHERE `000027`.id=(SELECT max(`000027`.id) FROM `000027`) AND `000028`.id=(SELECT max(`000028`.id) FROM `000028`)"
dbPlant.query(sqlSelect, (err, result) => {
    console.log(result)
    res.send(result)
    res.end()
})

and it sends this back with only one rowdatapacket when it should be two, or two of each of those values...

[
  RowDataPacket {
    id: 652,
    time: 2021-01-24T17:28:01.000Z,
    value: '262'
  }
]

Could you elaborate on what you mean by However, in react.js it is only returning one of these like this.... Specifically, how are you running this query in React? React is a front-end library and to my knowledge does not allow you work directly with a database. — codemonkey
– codemonkey, Commented Jan 24, 2021 at 19:23
Sorry I meant node.js. Long day. I will put the query in an edit. — Justin Oberle
– Justin Oberle, Commented Jan 24, 2021 at 19:25

Bill Karwin · Accepted Answer · 2021-01-24 19:34:37Z

1

Your two tables have some column names in common. This is okay to have repeated column names in a result set in the mysql client, but some programming interfaces map a rows of a result set into a hash array, where the column names are the keys. So if you have duplicate column names, one naturally overwrites the other.

The remedy is to define column aliases for one or the other of each duplicate, so they are mapped into distinct keys in the result set.

You must do this one column at a time. Sorry, you can't use SELECT * anymore (you shouldn't use SELECT * anyway). There is no "automatic alias all columns" option.

SELECT 
  `000027`.id AS id27,
  `000027`.time AS time27,
  `000027`.value AS value27,
  `000028`.id AS id28,
  `000028`.time AS time28,
  `000028`.value AS value28
FROM `000027`,`000028`
WHERE `000027`.id=(SELECT max(`000027`.id) FROM `000027`)
AND `000028`.id=(SELECT max(`000028`.id) FROM `000028`)

edited Jan 24, 2021 at 19:34

answered Jan 24, 2021 at 19:31

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

Justin Oberle Over a year ago

Thank you!. I will give this a whirl. Makes sense

Collectives™ on Stack Overflow

mysql query works in phpmyadmin but not in node.js

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