If I want more than just ID (like long and lat) what would I change here…
- User
If I want more than just ID (like long and lat) what would I change here
app.post('/classes/:id', (req, res) => { let data = req.body; const { id } = req.params; if(!data) res.status(400).send("You need to supply a JSON object"); else { db.query(
INSERT INTO user_classes (user_id, classes) VALUES ('${id}', '${JSON.stringify(data)}') ON DUPLICATE KEY UPDATE classes=VALUES(classes), (err, results) => { console.log(err); if(err) res.status(400).send(err); else res.status(200).send(results); }); } }); - Assistant
If you want to add additional information such as latitude (
lat) and longitude (long) to your database alongside theuser_idandclasses, first ensure that your database schema includes columns for these values. Once your table is set to store latitude and longitude, you can adjust the code to include these values in the query. Assuming thatlatandlongare part of the data being sent in the request body, you can parse them fromreq.bodyjust like you did withidfromreq.params.Here's what your code would look like after the modifications:
app.post('/classes/:id', (req, res) => { let data = req.body; const { id } = req.params; const lat = data.lat; const long = data.long; if(!data) { res.status(400).send("You need to supply a JSON object"); } else { db.query(`INSERT INTO user_classes (user_id, classes, latitude, longitude) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE classes=VALUES(classes), latitude=VALUES(latitude), longitude=VALUES(longitude)`, [id, JSON.stringify(data), lat, long], (err, results) => { if(err) { console.error(err); res.status(400).send(err.sqlMessage || err); } else { res.status(200).send(results); } }); } });Important Changes:
- Adding
const lat = data.lat;andconst long = data.long;to parse the latitude and longitude from thereq.body. - Updating the SQL query to include placeholders for
latitudeandlongitudevalues. This assumes your table has column nameslatitudeandlongitude. - Using parameterized queries (
?, ?, ?, ?) rather than string interpolation in the SQL query to prevent SQL injection vulnerabilities. The array[id, JSON.stringify(data), lat, long]contains the corresponding placeholder values that will replace the?marks in your query in a safe manner.
Please make sure to adjust the column names (
latitudeandlongitude) if they are different in your database. Also, note that ifdataexpects to includelatandlong,JSON.stringify(data)will also include the latitude and longitude in theclassesfield, which may not be what you want. If that's the case, removelatandlongfromdatabefore stringifying it.Lastly, do not forget to adjust your database schema to include
latitudeandlongitudecolumns, and confirm that your API consumers are indeed sending these values in the request body.Remember to be cautious when modifying database schema and queries to ensure data integrity and application stability.
- Adding