[RESOLVED] can't insert data to a specific table

ashi-neko-san

i'm currently working on a comment system which should be pretty easy to make - but apparently it wont insert any data to my comment table in the db :mad: however, if i change the table to my news table it has no problem doing it.
so i was wondering if you mabye could explain to me what i do wrong?
heres the codes:
(the values get to here, i have checked. it's just the query that wont work!)

<?php
if($_POST['submit']) {

include "inc/connect.php";

$post_id = $_POST['post_id'];
$by = $_POST['by'];
$comment = $_POST['comment'];
$date = time();

mysql_query("INSERT INTO comment (post_id, by, comment, date) VALUES ('$post_id', '$by', '$comment', '$date')");
mysql_close();
echo "<meta http-equiv='refresh' content='0;url=index.php?page=show_post&id=". $post_id ."'>";
}
?>

comment database:

CREATE TABLE `comment` (
  `id` int(11) NOT NULL auto_increment,
  `post_id` tinytext NOT NULL,
  `by` tinytext NOT NULL,
  `comment` text NOT NULL,
  `date` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

post database (which works when i change it to insert there)

CREATE TABLE `post` (
  `id` int(11) NOT NULL auto_increment,
  `headline` tinytext NOT NULL,
  `text` text NOT NULL,
  `date` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

i hope you can help be, cause i'm getting frustrated with this ><''

liquorvicar

It sounds like you need to check the permissions of your DB user. MySQL has a very granular permissions system, each user can be granted a number of different permissions (SELECT, INSERT, UPDATE etc) on databases, tables or even columns where appropriate. It sounds like the user you have set up has INSERT permissions for one table but not the other.

You can check for errors like this by using the http://php.net/mysql_error function.

ashi-neko-san

thanks liqour!
now i found out what the error is 😃
i've tried to find it on google, but i can't find something useful about it. so i guess i need some help again. it comes up with the following error:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'by, comment, date) VALUES ('5', 'name', 'comment testing', '1199729356')' at line 1

what does that mean, and how to i solve the problem?

liquorvicar

By is a reserved word in MySQL (it's used in ORDER BY and GROUP BY). If you really want to use it as a column name (although I'd suggest using added_by or similar instead) then you must enclose it in backticks when using it in a query like so

mysql_query("INSERT INTO comment (post_id, `by`, comment, date) VALUES ('$post_id', '$by', '$comment', '$date')");

ashi-neko-san

thanks, that worked 😃
awesome