Javascript SQL query not returning the expected multiple rows, only returning one instead

Question

Javascript SQL query not returning the expected multiple rows, only returning one instead

I have a table containing multiple rows of test results, each row includes the ID of the test taker. Using the logged-in user information, I've added additional rows to the table to ensure accuracy. However, when printing the result to the console, only one result is displayed.

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) {  
    // Display results where 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

javascript sql express

Answer 1

Answer №1

Make sure to set your 'results' with all results, not just the first one (result[0]).

It is also pointless to perform this action.

Consider trying it 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) {  
    // Display results that match the user ids
    if(error) throw error;

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

    // You can iterate through 'results' with a for loop to display one row at a time if needed

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

Changes made:

1- Ensured 'results' contains all data instead of just the first entry (previous code was results = results[0])

2- Eliminated unnecessary var tableData as it duplicates the value of 'results'.

Answer 2

Make sure to set your 'results' with all results, not just the first one (result[0]).

It is also pointless to perform this action.

Consider trying it 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) {  
    // Display results that match the user ids
    if(error) throw error;

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

    // You can iterate through 'results' with a for loop to display one row at a time if needed

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

Changes made:

1- Ensured 'results' contains all data instead of just the first entry (previous code was results = results[0])

2- Eliminated unnecessary var tableData as it duplicates the value of 'results'.

Answer 3

Answer №2

When handling the callback function for your query, you are given three parameters: error, results, and fields. Inside the callback function, you create a new variable named results and assign it the first item from the results parameter, which is results[0].

To access all the rows in the result without conflicting variable names, remove the line var results = results[0]; and loop through the entire array:

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

Avoid using repetitive variable names to prevent confusion. Understanding variable scope can be challenging; learn more about it here: What is the scope of variables in JavaScript?. To mitigate these issues, consider declaring use strict at the beginning of your code to enable Strict Mode.

Answer 4

When handling the callback function for your query, you are given three parameters: error, results, and fields. Inside the callback function, you create a new variable named results and assign it the first item from the results parameter, which is results[0].

To access all the rows in the result without conflicting variable names, remove the line var results = results[0]; and loop through the entire array:

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

Avoid using repetitive variable names to prevent confusion. Understanding variable scope can be challenging; learn more about it here: What is the scope of variables in JavaScript?. To mitigate these issues, consider declaring use strict at the beginning of your code to enable Strict Mode.

Javascript SQL query not returning the expected multiple rows, only returning one instead

Answer №1

Answer №2

Similar questions

Transform a row into a clickable element

How can you notice when a DOM element is deleted from the page?

Troubleshoot the pattern of Pascal's Triangle

Step-by-step guide for implementing an "on change" event for a select box within a dialog box

VueJS method for making an HTTP GET request

Ways to implement the function provided in the website using javascript

Firebase: Database and Storage - the art of efficiently linking images to posts | Vue.js

Is it possible to iterate through HTML elements without relying on forEach()?

Iterate through the array to import data into QlikView

SQL Challenge: In terms of technicality, how many individuals transitioned from IBM to Google?

Selenium encountered an error when trying to execute the 'querySelector' function on the document. The selector provided, my_selector, is not recognized as a valid selector

Error TS2307: Module '~express/lib/express' could not be located

Is it not possible to access the profile page using the GET method?

How can I retrieve the ultimate value of a JQuery UI Slider and store it in a PHP variable?

What is the best way to prevent a font awesome icon from appearing in a span during data loading

What is the best way to retrieve shared columns from various tables in a Database?

Combining Various Items Retrieved from Fetch Request

Assigning the Style property to an element using a string that includes HTML tags

What is the best way to show a dropdown menu list using my code and JSON response object?

How can I arrange a specific array position in Vuejs according to the Id value?