SQL query INSERT not working inserting values into my DB [duplicate]

This question already has an answer here: Syntax error due to using a reserved word as a table or column name in MySQL (1 answer) Closed 8 years ago.

I'm trying to insert some values into my DB but it's not working, i'm trying to figure out why it's not working but I'm an amateur php coder,

This is the code I'm using:

$insert = mysql_query
    ("
    INSERT INTO news(id,title,body,date,by)
    VALUES ('NULL','".$title."','".$body."','".$date."','".$by."')
    ");
    mysql_close($connect);

And the rows i'm trying to insert into are: id,title,body,date,by

but it's not showing up in the databa开发者_如何转开发se or on my news page.

Can someone please help me?

---

by is a special keyword. Try wrapping the column names in tick marks:

INSERT INTO news(`id`,`title`,`body`,`date`,`by`)

---

I would expect id to be your primary key. It should not allow a null value. If it is auto incrementing you might try this:

$insert = mysql_query
    ("
    INSERT INTO news(title,body,date, by)
    VALUES ('".$title."','".$body."','".$date."','".$by."')
    ");

Others have noted by is a reserved word so you will need to quote it. It is best to avoid naming database objects using reserved words. Consider renaming the by column to author.

You should consider making changing the query into a prepared statement. See How can I prevent SQL injection in PHP?.

---

Try calling mysql_error() to get the error message: http://php.net/manual/en/function.mysql-error.php

---

First of all, don't put the single quotes around NULL or else it will be entered as a string.

Also, I am assuming that you are using mysql_real_escape_string to sanitize $title, $body, etc.

$title = mysql_real_escape_string($title);
$body  = mysql_real_escape_string($body);
$date  = mysql_real_escape_string($date);
$by    = mysql_real_escape_string($by);

As mellamokb pointed out, by and date are special keywords. It's best practice to put ticks around your columns (and table names). (this will fix your query)

$query = "INSERT INTO `news` (`id`, `title`, `body`, `date`, `by`) VALUES
      (NULL, '" . $title . "', '" . $body . "', '" . $date . "', '" . $by . "');";
if ($insert = mysql_query($query, $connect)) {
    // Success!
} else {
    echo 'MySQL Error: ' . mysql_error($connect); // this will tell you whats wrong
}
mysql_close($connect);

---

I recommend using sprintf & mysql_real_escape_string to handle SQL injection attack possibilities:

$insert = sprintf("INSERT INTO news
                    (id, title, body, date, `by`)
                   VALUES 
                     (NULL,'%s','%s','%s','%s')",
                   mysql_real_escape_string($title),
                   mysql_real_escape_string($body),
                   mysql_real_escape_string($date),
                   mysql_real_escape_string($by));

$result = mysql_query($insert) or die(mysql_error());

This will let you know what error is encountered too.

That said, there are numerous potential issues:

• by is a reserved keyword, you need backticks to escape its use in MySQL (see example above). The alternative is to rename the column to something that is not a reserved word -- backticking every column and/or table name hides such issues rather than learning
• NULL should not be inside of single quotes, or it will be interpreted as a string; DEFAULT would be another option if there's a DEFAULT constraint on the column
• the date column should be either a DATE, DATETIME or TIMESTAMP data type rather than string in order to use MySQL Date/Time functionality, and should be using DATE_FORMAT if the value will not be a MySQL standard date format