1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQLi INSERT INTO problem

Discussion in 'PHP' started by risoknop, Aug 29, 2008.

  1. #1
    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 :)
     
    risoknop, Aug 29, 2008 IP
  2. JAY6390

    JAY6390 Peon

    Messages:
    918
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #2
    try

    $mysqli->query("INSERT INTO cms_comments (page, meta, author, email, website, content) 
    VALUES ('$id', '$meta', '$author', '$email', '$website', '$comment')");
    PHP:
     
    JAY6390, Aug 29, 2008 IP
  3. chenmin8123

    chenmin8123 Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    It looks like the string cause the problem, use ' ' or \"
     
    chenmin8123, Aug 29, 2008 IP
  4. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #4
    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
     
    EricBruggema, Aug 30, 2008 IP
  5. JAY6390

    JAY6390 Peon

    Messages:
    918
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #5
    ^^ It should really be mysqli_real_escape_string() - http://www.php.net/mysql_real_escape_string
     
    JAY6390, Aug 30, 2008 IP
  6. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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?
     
    risoknop, Aug 30, 2008 IP
  7. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #7
    You need to give more examples and use mysqli_real_escape_string
     
    EricBruggema, Aug 30, 2008 IP
  8. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #8
    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).
     
    risoknop, Aug 30, 2008 IP
  9. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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
     
    risoknop, Aug 30, 2008 IP
  10. JAY6390

    JAY6390 Peon

    Messages:
    918
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #10
    shouldn't
      $id = $_GET['id'];
    //be 
      $id = $_POST['id'];
    PHP:
     
    JAY6390, Aug 30, 2008 IP
  11. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #11
    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...
     
    risoknop, Aug 30, 2008 IP
  12. JAY6390

    JAY6390 Peon

    Messages:
    918
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #12
    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
     
    JAY6390, Aug 30, 2008 IP
    risoknop likes this.
  13. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #13
    This is the output of $mysqli->error; :

     
    risoknop, Aug 30, 2008 IP
  14. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #14
    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):
     
    risoknop, Aug 30, 2008 IP
  15. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #15
    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):
     
    risoknop, Aug 30, 2008 IP
  16. JAY6390

    JAY6390 Peon

    Messages:
    918
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #16
    Cools, glad you got it sorted :)
     
    JAY6390, Aug 30, 2008 IP
  17. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #17
    Yeah. I have been stuck with this for almost two days :D

    Now I can finally move on and start coding other functions of the CMS ;)
     
    risoknop, Aug 30, 2008 IP
  18. JAY6390

    JAY6390 Peon

    Messages:
    918
    Likes Received:
    31
    Best Answers:
    0
    Trophy Points:
    0
    #18
    Two days :eek: better get them fingers workin faster now :D
     
    JAY6390, Aug 30, 2008 IP
  19. matthewrobertbell

    matthewrobertbell Peon

    Messages:
    781
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    0
    #19
    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
     
    matthewrobertbell, Aug 30, 2008 IP
    risoknop likes this.
  20. risoknop

    risoknop Peon

    Messages:
    914
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    0
    #20
    Thanks for the tip. The code is now better organized. Rep added :)
     
    risoknop, Aug 30, 2008 IP