mysql_insert_id();成功插入行后未返回值 [英] mysql_insert_id(); not returning value after successful row insert

问题描述

我发誓我已经在这个站点以及其他站点上的所有其他类似问题上倾注了很多……但我认为我只是缺少了一些东西.希望有人可以指出一个愚蠢的错误，那就是我的大脑对我隐藏了. :)

I swear I have poured and poured over every other similar question on this site and others... but I think I'm just missing something. Hopefully someone can point out a silly mistake that my brain is hiding from me. :)

我的脚本将表单中的值插入名为"notes"的表中

My script inserts values from a form into a table called "notes"

这时，它通过称为newRelationship的函数在名为"relationships"的表中创建两个条目.

At that point it creates two entries in a table called "relationships" through a function called newRelationship.

变量"$ note_id"的值通过我的mysql_insert_id()填充；并传递给上面的函数

The value of the variable "$note_id" is populated via my mysql_insert_id(); and passed into the above function.

执行代码时，该条目已成功添加到注释"，并且为"id"列提供了具有auto_increment的适当值.

When the code is executed, the entry is successfully added to "notes" and the "id" column is given a proper value with auto_increment.

接下来，将两个条目添加到关系"表中(它们应该如此).

Next, two entries are added to the "relationship" table (as they should).

但是，我的mysql_insert_id()总是将值"0"而不是新的行ID排除在外.

HOWEVER, my mysql_insert_id() keeps kicking out a value of "0" instead of the new row id.

对于我的一生，我无法弄清楚自己在做错什么.我什至尝试从头开始创建具有相同结果的新表.最重要的是，我在项目的其他文件中使用了几乎相同的代码，没有任何问题.有人看到我在做什么错吗?

For the life of me I cant figure out what I'm doing wrong. I even tried creating a new table from scratch with the same results. The kicker is that I use pretty much identical code in other files of my project with no problems. Anyone see what I'm doing wrong?

相关代码

    if ($user->is_loaded())
    {

    if($_POST['project_id']) {
    $project_id = $_post['project_id'];
    $long_note = $_POST['long_note'];
    $created_by = $_POST['created_by'];
    $note_sql="INSERT INTO notes (`long_note`, `added`, `created_by`) VALUES ('$long_note', '$timenow', '$created_by')";
    if (!mysql_query($note_sql,$con))
    {
    die('Error: ' . mysql_error($note_sql));
    }
    else {
    echo "note created Creating relationship ";
    $note_id = mysql_insert_id();

    echo $note_id;
    newRelationship($project_id, "project", $note_id, "note");
    newRelationship($client_id, "client", $note_id, "note");
    echo "note added successfuly";

    }

还有我的功能

function newRelationship($parent_id, $parent_type, $child_id, $child_type)
{

global $sql, $con, $timenow;

$relationship_sql="INSERT INTO `relationships` (`parent_id`, `parent_type`, `child_id`, `child_type`) VALUES ('$parent_id', '$parent_type', '$child_id', '$child_type');";
    if (!mysql_query($relationship_sql,$con))
    {
     die('Error: ' . mysql_error($relationship_sql));
    }
    else {
    echo $parent_type." ".$parent_id." realationship with ".$child_type." ".$child_id." successful ";
}

}

每个@jack的建议是我的便笺表的sql

Per @jack's suggestion here is the sql of my notes table

CREATE TABLE `notes` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `contact_id` int(10) NOT NULL,
  `client_id` int(10) NOT NULL,
  `status` text NOT NULL,
  `long_note` text NOT NULL,
  `added` int(11) NOT NULL,
  `modified` int(11) NOT NULL,
  `edit_by` int(10) NOT NULL, 
  `short_note` text NOT NULL,
  `created_by` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=295 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

推荐答案

根据文档:

由上一个查询为AUTO_INCREMENT列生成的ID 成功，如果先前查询未生成AUTO_INCREMENT，则为 0 值，如果未建立MySQL连接，则为FALSE.

The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

文档指出，如果上次执行的查询不生成AUTO_INCREMENT值，则它只能返回0，这应该表示您的PRIMARY KEY列auto_increment中的notes中的设置不正确.我建议您再次检查notes 中的PRIMARY KEY列是否已通过auto_increment进行设置(再也不用担心再次检查！).

The documentation states that it can only return 0 if the query last executed does not generate an AUTO_INCREMENT value, which should mean that your PRIMARY KEY column in notes is not properly setup with auto_increment. I would recommend double-checking that the PRIMARY KEY column in notes is in-fact setup with auto_increment (never hurts to check again!).

查看示例代码，插入后立即调用mysql_insert_id()，因此之间应该没有任何冲突的查询，以使结果偏斜.

Viewing your sample code, you do call mysql_insert_id() immediately after insertion, so there shouldn't be any conflicting queries in between to skew the result.

我看到的唯一可能引起问题的是，您正在将MySQL资源传递给mysql_query()，而不是传递给mysql_insert_id():

The only thing I see that may cause an issue is that you're passing the MySQL resource to mysql_query(), but not to mysql_insert_id():

if (!mysql_query($note_sql,$con))
...
$note_id = mysql_insert_id();

由于此原因，资源可能存在冲突.尝试将您的呼叫更新为:

There may be a conflict in the resources due to this. Try updating your call to:

$note_id = mysql_insert_id($con);

这篇关于mysql_insert_id();成功插入行后未返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！