MySQLi INSERT INTO problem

Ok, so this is first time I'm using MySQLi on the Web site and I need a little help. I am connected to database, queries such as SELECT or UPDATE works (tested), I'm just having problem with inserting data to the table:

$mysqli->query("INSERT INTO cms_comments (page, meta, author, email, website, content) 
VALUES ($id, $meta, $author, $email, $website, $comment)");

Code (markup):

Anybody can tell me what's wrong with this query?

Thanks

 

try

$mysqli->query("INSERT INTO cms_comments (page, meta, author, email, website, content) 
VALUES ('$id', '$meta', '$author', '$email', '$website', '$comment')");

PHP:

 

It looks like the string cause the problem, use ' ' or \"

 

even better is

$mysqli->query("INSERT INTO cms_comments (page, meta, author, email, website, content) 
VALUES ('" . mysql_real_escape_string($id) . "', 
'" . mysql_real_escape_string($meta) . "', 
'" . mysql_real_escape_string($author) . "', 
'" . mysql_real_escape_string($email) . "', 
'" . mysql_real_escape_string($website) . "', 
'" . mysql_real_escape_string($comment) . "')");

Code (markup):

use mysql_real_escape_string see details on php.net

 

^^ It should really be mysqli_real_escape_string() - http://www.php.net/mysql_real_escape_string

 

Thanks for help. I have used this:

$mysqli->query("INSERT INTO cms_comments (page, meta, author, email, website, content) VALUES (
'" . mysql_real_escape_string($id) . "', 
'" . mysql_real_escape_string($meta) . "', 
'" . mysql_real_escape_string($author) . "', 
'" . mysql_real_escape_string($email) . "', 
'" . mysql_real_escape_string($website) . "', 
'" . mysql_real_escape_string($comment) . "')
");

Code (markup):

And it works - partially

Let me explain what I am trying to achieve. I am building a simple CMS and this is a part of comment form processing... This line basically (ones all data are checked) inserts the verified comment into database.

The problem is, it works for the first comment, it works for the second - but from third comment it doesn't work...? This is very confusing.

Where could be the problem?

 

You need to give more examples and use mysqli_real_escape_string

 

Ok here is more complete code:

<?php

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

  require_once "library/config.php";
  include_once "library/opendb.php";

  $id = $_GET['id'];
  date_default_timezone_set('UTC'); 
  $meta = date('l jS \of F Y h:i:s A');
  $author = $_POST['author'];
  $email = $_POST['email'];
  $website = $_POST['website'];
  $comment = $_POST['comment'];

  if(empty($author)) {
    echo "<p>Name required.</p>\n";
  }
  else {
    if(empty($email)) {
      echo "<p>Email required.</p>\n";
    }
    else {
      if(empty($comment)) {
        echo "<p>Comment required.</p>\n";
      }
      else {
        $comment = htmlspecialchars($comment);
        
        /* Insert comment to the cms_comments table */
        $mysqli->query("INSERT INTO cms_comments (page, meta, author, email, website, content) VALUES (
        '" . mysql_real_escape_string($id) . "', 
        '" . mysql_real_escape_string($meta) . "', 
        '" . mysql_real_escape_string($author) . "', 
        '" . mysql_real_escape_string($email) . "', 
        '" . mysql_real_escape_string($website) . "', 
        '" . mysql_real_escape_string($comment) . "')
        ");
        
        /* This adds 1 to the comments_count in cms_pages table */
        $result = $mysqli->query("SELECT * FROM cms_pages WHERE id = $id");
        $row = $result->fetch_array();
        $comments_count = $row['comments_count'];
        $comments_count++;
        $mysqli->query("UPDATE cms_pages SET comments_count = $comments_count WHERE id = $id");
        
        echo "<p>Comment added.</p>\n"; 
      }
    }
  }

  include_once "library/closedb.php";

}

?>

Code (markup):

Basically, first it checks whether any of the data is empty... If everything is filled, it proceeds and inserts the comment to the table cms_comments.

Then, one more thing it does, it increases the comments_count value in the cms_pages table (for later use to show number of comments per page).

 

From the third comment it does only this:

1) increases the comments_count
2) but fail to insert comment to the table... no error displayed

 

shouldn't

  $id = $_GET['id'];
//be 
  $id = $_POST['id'];

PHP:

 

Nope... the $_GET['id] is the id of the page and it is passed in URL...

Something like:

index.php?id=1

Code (markup):

The others are $_POST because they are data from the comment form, which looks like this:

<h3>Add comment</h3>
<form name="addcomment" id="addcomment" method="post" action="addcomment.php?id=<?php echo $id; ?>">
<p><label for="author"><small>Author (required):</small></label></p>
<p><input type="text" name="author" id="author" /></p>
<p><label for="email"><small>Email (will not be published) (required):</small></label></p>
<p><input type="text" name="email" id="email" /></p>
<p><label for="website"><small>Website:</label></small></p>
<p><input type="text" name="website" id="website" /></p>
<p><textarea name="comment" id="comment"></textarea></p>
<p><input type="submit" name="submitcomment" id="submitcomment" /></p>
</form>

Code (markup):

And if the problem was there, it wouldn't work for first two comments, too. But now it works for 1st and 2nd comment, but doesn't work from third comment on the page and higher...

 

well i suggest you make the query its own variable and echo it before running the query so you can debug and see if there is anything out of the ordinary. also do an

echo mysqli->error;

PHP:

after the query

 

This is the output of $mysqli->error; :

 

It seems like the problem is in the database structure. This it how it looks:

CREATE TABLE `cms_comments` (
`id` INT( 3 ) NOT NULL DEFAULT '0',
`page` INT( 6 ) NULL ,
`meta` VARCHAR( 127 ) NULL ,
`author` VARCHAR( 127 ) NULL ,
`email` VARCHAR( 127 ) NULL ,
`website` VARCHAR( 127 ) NULL ,
`content` BLOB( 1000 ) NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = MYISAM ;

Code (markup):

 

Ok, I have tracked down the problem. Thanks for your help JAY6390 Rep added.

The problem was in the table structure, column id had default value set to 0, which caused duplicate values for new rows... That's why it failed - because id is a primary key of the table.

This is the new, working structure of the table:

CREATE TABLE `cms_comments` (
`id` INT( 3 ) NOT NULL AUTO_INCREMENT,
`page` INT( 6 ) NULL ,
`meta` VARCHAR( 127 ) NULL ,
`author` VARCHAR( 127 ) NULL ,
`email` VARCHAR( 127 ) NULL ,
`website` VARCHAR( 127 ) NULL ,
`content` BLOB( 1000 ) NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = MYISAM ;

Code (markup):

 

Cools, glad you got it sorted

 

Yeah. I have been stuck with this for almost two days

Now I can finally move on and start coding other functions of the CMS

 

Two days better get them fingers workin faster now

 

It would be better to use the $mysqli->real_escape_string function instead of mysql_real_escape_string, because then it will escape for the proper character set

 

Thanks for the tip. The code is now better organized. Rep added