Adil Ali
asked on
UPDATE sql name undefined
So I have an sql errror when whenever I update my data
const express = require('express');
const app = express();
const mysql = require('mysql');
const bodyparser = require('body-parser');
app.use(bodyparser.urlencoded({ extended: true }));
app.use(bodyparser.json());
const mysqlConnection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'Adil@123',
database: 'movies'
});
mysqlConnection.connect(err=>{
if (err) {
console.log('It was not successful \n Error:' + JSON.stringify(err,undefined,2));
} else {
console.log('Its a success');
}
});
// Collecting all the movies from the movielist
app.get('/movielist',(req,res)=> {
mysqlConnection.query("SELECT * FROM movielist", (err, rows,fields)=> {
if (!err) {
res.send(rows);
} else {
console.log(err);
}
});
});
// Finding a movie based on the `idmovielist` number
app.get('/movielist',(req,res) => {
mysqlConnection.query("SELECT * FROM movielist WHERE idmovielist = ?",[req.params.id],(err, rows,fields) =>{
if (!err) {
res.send(rows);
} else {
console.log(err);
}
});
});
// Delting a movie
app.delete('/movielist/:id',(req,res) => {
mysqlConnection.query("DELETE FROM movielist WHERE idmovielist = ?",[req.params.id],(err,rows,fields) =>{
if (!err) {
res.send("Movie is deleted");
} else {
console.log(err);
}
});
});
// Inserting a movie
app.post('/movielist/addMovie',(req, res) => {
//console.log("movielist/addMovie : ",req.body);
mysqlConnection.query("INSERT INTO movielist (`idmovielist`,`name`,`thumnail_path`,`description`,`language_released`,`year_released`) VALUES ('"+req.body.idmovielist+"', '"+req.body.name+"','"+req.body.thumnail_path+"', '"+req.body.description+"', '"+req.body.year_released+"', '"+req.body.language_released+"')",
(err,rows) => {
if (!err) {
res.send("Movie is added");
} else {
console.log(err);
}
});
});
app.put('/movielist/updateMovie/:id',(req,res) =>{
let update = req.body;
console.log(update.name);
mysqlConnection.query("UPDATE movielist SET name = ?, thumnail_path = ?, description = ?, year_released = ?, language_released = ? WHERE idmovielist = ?",
[update.name,update.thumnail_path,update.description,update.year_released,update.language_released,req.params.id], function (err, results) {
if (!err) {
res.send("Movie list is updated");
} else {
console.log(err);
}
});
});
// localhost:3000
app.listen(3000,() => {
console.log('We got it running');
});
module.exports = app;
above is my node js and below is my jquerry
$(function() {
$.ajax({
method:"GET",
url: "http://localhost:3000/movielist",
dataType: "json",
success: function (response) {
$.each(response, function(i, movie) {
const rowText = "<tr>" +
"<td>" + movie.idmovielist + "</td>" +
"<td>" + movie.name + "</td>" +
"<td>" + movie.thumbnail_path + "</td>" +
"<td>" + movie.description + "</td>" +
"<td>" + movie.year_released + "</td>" +
"<td>" + movie.language_released + "</td>" +
"<td>" + "<button button id = \"deleteMovie\" type=\"button\" class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Delete</button>" + "</td>" +
"<td>" + "<button button id = \"editMovie\" type=\"button\" class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Edit</button>" + "</td>";
$("#movies").append(rowText);
});
renderMovieList('movies');
function renderMovieList(){
$.ajax({
method:"GET",
url: "http://localhost:3000/movielist",
dataType: "json",
success: function (response) {
$('#movies').empty();
$.each(response, function(i, movie) {
const rowText = "<tr>" +
"<td>" + movie.idmovielist + "</td>" +
"<td>" + movie.name + "</td>" +
"<td>" + movie.thumbnail_path + "</td>" +
"<td>" + movie.description + "</td>" +
"<td>" + movie.year_released + "</td>" +
"<td>" + movie.language_released + "</td>" +
"<td>" + "<button button id = \"deleteMovie\" type=\"button\" class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Delete</button>" + "</td>" +
"<td>" + "<button button id = \"editMovie\" type=\"button\" class=\"btn btn-danger\" data-toggle=\"modal\" data-target=\"#exampleModal2\">Edit</button>" + "</td>";
$("#movies").append(rowText);
});
}
});
}
$("#movieAdded").click(function(a) {
a.preventDefault();
let mydata = {
idmovielist: $($("#newForm")[0].intNum).val(),
name: $($("#newForm")[0].name).val(),
thumnail_path: $($("#newForm")[0].thumnail_path).val(),
description: $($("#newForm")[0].description).val(),
year_released: $($("#newForm")[0].year_released).val(),
language_released: $($("#newForm")[0].language_released).val(),
}
displayMovie(mydata);
$("#newForm").trigger("reset");
$("#newForm").toggle();
});
$("#updateMovie").on("click", function(a) {
a.preventDefault();
let data = {
idmovielist: $($("#updateForm")[0].intNum).val(),
name: $($("#updateForm")[0].name).val(),
thumnail_path: $($("#updateForm")[0].thumnail_path).val(),
description: $($("#updateForm")[0].description).val(),
year_released: $($("#updateForm")[0].year_released).val(),
language_released: $($("#updateForm")[0].language_released).val(),
}
putMovie($($("#updateForm")[0].movieId).val(), data);
$("#updateForm").trigger("reset");
$("#updateForm").toggle();
});
function getOneMovie(id) {
$.ajax({
url: "http://localhost:3000/movielist" + id,
method: 'GET',
dataType: 'json',
success: function(data) {
$($("#updateForm")[0].movieId).val(data._id);
$($("#updateForm")[0].intNum).val(data.intNum);
$($("#updateForm")[0].name).val(data.name);
$($("#updateForm")[0].thumnail_path).val(data.thumnail_path);
$($("#updateForm")[0].description).val(data.description);
$($("#updateForm")[0].year_released).val(data.year_released);
$($("#updateForm")[0].language_released).val(data.language_released);
$("#updateForm").show();
}
});
}
function displayMovie(mydata) {
$.ajax({
method: "POST",
url: "http://localhost:3000/movielist/addMovie",
dataType: "json",
data: mydata,
success: function(data) {
console.log(data);
renderMovieList();
}
});
}
function loadButtons() {
$(".editMovie").click(function (a) {
getOneMovie($($(this)[0]).data("movieId"));
a.preventDefault();
});
$(".deleteMovie").click(function (a) {
deleteMovie($($(this)[0]).data("movieId"));
a.preventDefault();
});
}
loadButtons();
function putMovie(data) {
$.ajax({
url: "http://localhost:3000/movielist/updateMovie/2",
method: 'PUT',
dataType: 'json',
data: data,
success: function(data) {
console.log(data);
getOneMovie();
}
});
}
function deleteMovie(id) {
$.ajax({
url: "http://localhost:3000/movielist/" + id,
method: 'DELETE',
dataType: 'json',
success: function(data) {
console.log(data);
}
});
}
}
})
});
ASKER
Would I also have to do that to the other column names as well such as thumnail_path= null is not valid
ASKER
I am still getting that error 'Column \'name\' cannot be null',
app.put('/movielist/update Movie/:id' ,(req,res) =>{
let update = req.body;
console.log(update.name);
mysqlConnection.query("UPD ATE movielist SET name = null is not valid, language_released = ? WHERE idmovielist = ?",
[update.name,update.langua ge_release d,req.para ms.id], function (err, results) {
if (!err) {
res.send("Movie list is updated");
} else {
console.log(err);
}
});
});
app.put('/movielist/update
let update = req.body;
console.log(update.name);
mysqlConnection.query("UPD
[update.name,update.langua
if (!err) {
res.send("Movie list is updated");
} else {
console.log(err);
}
});
});
this part is wrong:
mysqlConnection.query("UPD ATE movielist SET name = null is not valid, language_released = ?
what I mean is you need to set name field to a non-NULL value, like:
or if the fact this value is nullable, then pls make necessary adjustment at DB Table to change the field to allow nullable values.
mysqlConnection.query("UPD
what I mean is you need to set name field to a non-NULL value, like:
UPDATE movielist SET name = '', language_released = ?
orUPDATE movielist SET name = 'whatever default value', language_released = ?
or if the fact this value is nullable, then pls make necessary adjustment at DB Table to change the field to allow nullable values.
To me, it looks more like an application issue. In the posted screen shot, the update screen is totally blank. I would think the application should be checking to see if there is actually an update before trying to perform one.
However, the description of the error is correct. You are trying to put NULL into a field that doesn't allow NULL.
However, the description of the error is correct. You are trying to put NULL into a field that doesn't allow NULL.
ASKER
@Ryan Chong app.put('/movielist/update Movie/:id' ,(req,res) =>{
let update = req.body;
mysqlConnection.query("UPD ATE movielist SET name = '"+update.name+"', thumnail_path = '"+update.thumnail_path+"' , description = '"+update.description+"', year_released = '"+update.year_released+"' , language_released = '"+update.language_release d+"' WHERE idmovielist = '"+update.idmovielist+"'",
function (err, results) {
if (!err) {
res.send("Movie list is updated");
} else {
console.log(err);
}
});
}); I did it this way is it right
let update = req.body;
mysqlConnection.query("UPD
function (err, results) {
if (!err) {
res.send("Movie list is updated");
} else {
console.log(err);
}
});
}); I did it this way is it right
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
so SET name = null is not valid