Javascript SQL query only returning one row instead of many

Question

SO I have a table with multiple rows of test results. The ID of the test taker is inserted into each row and I have actually inserted more rows into the table using the logged in user so I know its correct. The result is only printing one result to the console

router.get('/tableResults',function(req, res, next) {
  console.log(req.session.passport.user);
  const db = require('./db_connection.js');
  var id = req.session.passport.user;

  db.query('SELECT * FROM testResults where id = ?',[id], function(error, results, fields) {  
    // results are displayed where the user ids match
    if(error) throw error;
    var results = results[0];

    console.log (" 1st " + results);

    var tableData = [ results]; 

    console.log (tableData);

    res.redirect('/home');
  });
});

// error result in putty

Nodemon] starting `node ./bin/www`

24 

Connected!

 1st [object Object]

[ RowDataPacket {

test_id: 1,

username: 'rr',

gradeOne: 1,

gradeTwo: 7,

gradeThree: 0,

id: 24 } ]

GET /tableResults 302 169.552 ms - 54

24

true
GET /home 304 89.895 ms - -
GET /dist/css/bootstrap.min.css 404 10.086 ms - 3199

I actually tried changing this to [1,0] but it had no effect — ct234, May 23 '19 at 02:15
`var results = results[0];` this is horrendous. Don't do this. Why are you creating a new variable with the same name but different type of an existing variable? Since you did do this, you're setting a `results` variable to hold the first row from what was the `results` array returned by the SQL. — Ben, May 23 '19 at 02:20
@ct234 You are taking only one item out of an array (the first result to your db.query(), in this case). If you want all of the items, you need to either leave `results` as it is, or loop over each item in the array, depending on what you're trying to do exactly. — sinaraheneba, May 23 '19 at 02:24

Dave Anderson · Answer 1 · 2019-05-23T02:27:54.703

In your callback function for the query you have three parameters; error, results, fields. Within the scope of the callback function you declare a variable also called results and assign it the first item from the results parameter i.e. results[0].

Remove var results = results[0]; then to access all the rows in the result you should loop over the whole array;

for(var i = 0; i < results.length; i++) {
  console.log (results[i]);
}

You should not repeat variables names like this. Understanding variable scope is challenging; What is the scope of variables in JavaScript?. To help avoid these kinds of problems declare use strict at the top of your code to trigger Strict Mode

score 0 · Accepted Answer · answered May 23 '19 at 02:23

you are setting your 'results' with only the first result (result[0]).

Also it is redundant to do this.

Try this way:

router.get('/tableResults',function(req, res, next) {
  console.log(req.session.passport.user);
  const db = require('./db_connection.js');
  var id = req.session.passport.user;

  db.query('SELECT * FROM testResults where id = ?',[id], function(error, results, fields) {  
    // results are displayed where the user ids match
    if(error) throw error;

    console.log ("1st: ", results[0]);
    console.log ('All: ', results);

//here you can iterate [for loop] trough 'results' to show one row at a time if you need/want

    res.redirect('/home');
  });
});

What I did:

1- Fixed the result being set to the first result (former results = results[0])

2- Removed unnecessary var tableData as 'results' has the same value.

Thank You!! Do any of you know how to clean the results so that the results are just an array of results without the "Raw data packet" part — ct234, May 23 '19 at 02:44
You can use: JSON.parse(JSON.stringify(results)), should work :) — Will Silveira, May 23 '19 at 02:49

Javascript SQL query only returning one row instead of many

2 Answers2