NodeJS - MySQL Query

Question

I'd like to query my MySQL database. I've got audioid 34 in req.body.audioid so I need to select the opid of row 34 which is 33 and return all the columns of row 33.

But my code returns nothing at all. Here's my table;

Here's the code;

    var getMessageReplies = async function (req, res) {
    
    let row_a ;
    const db = makeDb( config );
    const audioid = req.body.audioid;
    
    try {   
    
    row_a = await db.query( `
    SELECT
    m.audiourl, 
    m.receiver, 
    m.audioid, 
    m.sender,
    m.opid, 
    m.timestamp,
    ac.gavatar, 
    ac.name,
    ac.bio, 
    (
      SELECT count(*) FROM messages m WHERE m.opid = m.audioid
    ) as replycount
    FROM messages m 
    LEFT JOIN accounts ac ON ac.id = m.sender  
    WHERE m.opid = ? 
    ORDER BY m.audioid DESC
    `, [audioid]);  

    if (row_a != 0) {   

     console.log('Got conversation');
     res.json({
         row_a
        });             
    } else {
    console.log('None');
    res.json({
         "message" : 'None' 
        }); 
    }
}
  
catch(err) {
    console.log(err.message);
}
finally {
    db.close();
}
}

What am I doing wrong?

Answer 1

I wasn't able it figure it out in a single query so;

try {   
    
    row_a = await db.query( 'SELECT opid FROM messages WHERE audioid = ?', [audioid]);
    const opid = row_a[0].opid; 
    
    row_b = await db.query( `
    SELECT
    m.audiourl, 
    m.receiver, 
    m.audioid, 
    m.sender,
    m.opid, 
    m.timestamp,
    ac.gavatar, 
    ac.name,
    ac.bio, 
    (
      SELECT count(*) FROM messages m WHERE m.opid = m.audioid
    ) as replycount
    FROM messages m 
    LEFT JOIN accounts ac ON ac.id = m.sender  
    WHERE m.audioid = ? 
    ORDER BY m.audioid DESC
    `, [opid]); 

    if (row_b != 0) {   

     console.log('Got conversation');
     res.json({
         row_b
        });             
    } else {
    console.log('None');
    res.json({
         "message" : 'None' 
        }); 
    }
}

Answer 2

It seems you are doing wrong at where condition.

WHERE m.opid = ?

Hear, I think it should be

WHERE m.audioid = ? 

As you are passing audioid and going check opid in where so may be that's why you are not able to get data.