duafagm1066
2012-01-13 00:03 阅读 107
已采纳

mysql_insert_id无效

Apologies for the vague title, here's my problem:

The goal of my code is to insert a new row into a table that has an auto-increment field. After the insert, I want to get the value of the auto-increment field that has just been generated.

Here's my table defintion:

CREATE TABLE `EventComments` (
  `CommentID` int(11) NOT NULL AUTO_INCREMENT,
  `EventID` int(11) NOT NULL,
  `OwnerID` int(11) NOT NULL,
  `Comment` varchar(512) NOT NULL,
  `DateTime` datetime NOT NULL,
  PRIMARY KEY (`CommentID`)
) ENGINE=MyISAM AUTO_INCREMENT=68 DEFAULT CHARSET=latin1;

I'm trying to get the value of the CommentID field.

So, here is the php code that issues the insert query and then attempts to get the CommentID value.

<?php
session_start();
ob_start();
include_once 'lib/functions.php';

if(isset($_SESSION['uid'])) {
    $eventID = $_GET['evid'];
    $ownerID = $_SESSION['uid'];
    $comment = $_GET['comment'];
    $comment = trim($comment);
    $dateTime = date('Y-m-d H:i:s');

    $db_connection = database_connect();

    if($eventID != null && !empty($comment)) {
        $query = "INSERT INTO meetup.EventComments (EventID, OwnerID, Comment,     DateTime) 
        VALUES (" . $eventID . ", " . $ownerID .", '" . $comment . "', '". $dateTime ."')";

        mysqli_query($db_connection, $query) or die(mysqli_error($db_connection));  

        $id = mysql_insert_id();
        $commentHtml = generateCommentFromData($db_connection, $ownerID, $comment, $dateTime,  $id);
        echo $commentHtml;
    }
}

ob_end_flush();
?>

This code issues the following error in the php logs:

mysql_insert_id() [<a href='function.mysql-insert-id'>function.mysql-insert-id</a>]: A link to the server could not be established...

I also tried explicitly passing the database link. But that gives the following error:

mysql_insert_id(): supplied argument is not a valid MySQL-Link resource...

As a final note, the insert query works. It is definitely inserting a new row with the expected data!

Any insight here would be appreciated! Thanks

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    doufei2355 doufei2355 2012-01-13 00:09

    You are using the mysqli extension to connect and run your query, but then you use the mysql (notice the lack of i at the end) extension to get your inserted id, that can't work. While they are both extensions that provide access to mysql, they are also two very different libraries and can't share a connection between each other.

    For the record, mysqli is the one you should be using, mysql is the "old" version that does not support new features of mysql >= 4.1

    In other words, the solution is to use mysqli_insert_id()

    Also, please escape your parameters properly, you can't put the content of $_GET and $_POST variables inside your query unsecured like that. At the very least use mysqli_real_escape_string()

     $query = "INSERT INTO meetup.EventComments (EventID, OwnerID, Comment,     DateTime) 
            VALUES (" . mysqli_real_escape_string($eventID)." [...]
    

    For more infos on this, have a look to the numerous questions on this subject, for example this one: How to properly escape a string via PHP and mysql

    点赞 评论 复制链接分享
  • doukuanyong1939 doukuanyong1939 2012-01-13 00:08

    DATETIME is a Data Type in MySQL that's why INSERT query is not working. Use backtick ` instead.

    $query = "INSERT INTO meetup.EventComments (`EventID`, `OwnerID`, `Comment`, `DateTime`) 
    VALUES (" . $eventID . ", " . $ownerID .", '" . $comment . "', '". $dateTime ."')"; 
    
    点赞 评论 复制链接分享
  • dream_high1026 dream_high1026 2012-01-13 00:10

    You probably want to use the mysqli extension equivalent: mysqli_insert_id()

    It might work as it is by passing the connection resource but even if it did, it's not good to mix methods from two separate connection classes:

        $id = mysql_insert_id($db_connection);
    
    点赞 评论 复制链接分享

相关推荐