mysql_insert_id() echos 0

Question

I am not the first to have this problem but the solutions of the others don't work. This script always returns 0 for mysql_insert_id(); I am using multiple primary keys.

$sql = "INSERT INTO Produkte (Name,Price,Description,User)
VALUES ('".$_POST['name']."','".$_POST['price']."','".$_POST['description']."','".$_SESSION['user']."');";
$result = $GLOBALS['DB']->query($sql);
echo mysql_insert_id();
echo '<div class="saving">';
if($result){
echo "Saved!";
} else{
echo("Saving failed");
} echo '</div>';
}

I already tried mysql_insert_id($link), where I linked $link to a mysql_connect() and mysql_insert_id($GLOBALS['DB']->MySQLiObj)

My $GLOBAL['DB']

if(!isset($GLOBALS['DB']))$DB = new \System\Database\MySQL(MYSQL_HOST,MYSQL_BENUTZER,MYSQL_KENNWORT,MYSQL_DATENBANK,MYSQL_PORT);

My MySQL class:

public $MySQLiObj = null;

function __construct($server, $user, $password, $db, $port = '3306')
{

    $this->MySQLiObj = new \mysqli($server, $user, $password, $db, $port);

    if (mysqli_connect_errno())
    {
        echo "Keine Verbindung zum MySQL-Server möglich.";
        trigger_error("MySQL-Connection-Error", E_USER_ERROR);
        die();
    }

    $this->query("SET NAMES utf8");
}

@JohnConde it doesn't say anything `mysql_error();` (`error_reporting(E_ALL);`) — Deproblemify, Nov 18 '13 at 14:20
`mysql_insert_id()` is deprecated as of PHP 5.5.0, use `mysqli` features instead — NoobEditor, Nov 18 '13 at 14:23
What database abstraction layer are you using ($GLOBALS['DB']) ? — Amine Matmati, Nov 18 '13 at 14:19
yes...no harm in keeping your code upto-date on standards and performance!! :) — NoobEditor, Nov 18 '13 at 14:40
use `die()` command to print your `$sql` and try manually executing the statement...that might help to understand the prob! — NoobEditor, Nov 18 '13 at 14:54
@CasperAndréCasse `INSERT INTO Produkte (Name,Preis,Beschreibung,Benutzer) VALUES ('Test','0','Test','1');` — Deproblemify, Nov 18 '13 at 14:54
@user2972107 @CasperAndréCasse Inserting into the DB works fine, but I only get 0 form the `mysql_insert_id()` statement, maybe because I have multiple primary keys? — Deproblemify, Nov 18 '13 at 14:56
Maybe `PersistentConnections` is disabled in your phpMyAdmin config file. See my answer below. — Casper André Casse, Nov 19 '13 at 08:56

score 3 · Answer 1 · edited May 23 '17 at 10:28

Your query is failing because you are using the reserved keyword desc without ticks. As a result the INSERT fails and thus no ID is returned.

$sql = "INSERT INTO Produkte (Name,Price,Desc,User)
VALUES ('".$_POST['name']."','".$_POST['price']."','".$_POST['description']."','".$_SESSION['user']."');";

should be

$sql = "INSERT INTO Produkte (Name,Price,`Desc`,User)
VALUES ('".$_POST['name']."','".$_POST['price']."','".$_POST['description']."','".$_SESSION['user']."');";

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

You are also wide open to SQL injections

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

The documentation for mysql_insert_id() explains this very well:

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.

It is possible that your table does not have any column set to AUTO_INCREMENT. mysql_insert_id() will return 0 in such cases.

^{Usual disclaimer: mysql_ functions are deprecated and are soon to be removed. Stop using them and switch to MySQLi / PDO instead.}

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 18 '13 at 14:19

Amal Murali

75,622
18
128
150

@JSHelp: Add `echo mysql_error();` after the `mysql_insert_id()` statement. What does it output? – Amal Murali Nov 18 '13 at 14:36
1

@JSHelp: What happens when you try to execute the MySQL query manually? (using phpMyAdmin or similar) – Amal Murali Nov 18 '13 at 14:41

score 1 · Answer 3 · answered Nov 18 '13 at 15:08

1

The core problem here is that you're using mysqli for your query() call, but then trying to use the mysql_insert_id() function.

Please note that the mysqli and mysql functions are two entirely separate libraries, and are incompatible with each other.

If you're using mysqli in one part of your code, you must use mysqli for all other DB-related code as well, otherwise they will not work together.

So you must use the mysqli::$insert_id property instead.

You mentioned in the question that you can access your connection object as $GLOBALS['DB']->connection. In that case, you should be able to write your code as follows:

$newId = $GLOBALS['DB']->connection->insert_id;

I hope that helps.

PS: While I'm here, I'll add that you should avoid putting $_POST variables directly into your query string like that. It's very poor practice and has some dangerous security implications. You should look into using mysqli->prepare() and mysqli->bind_param() to add the parameters for your queries instead.

answered Nov 18 '13 at 15:08

Spudley

166,037
39
233
307

Thanks for the advice with the POST, I would have (and I will) change it later, after the sql connection and all that works. – Deproblemify Nov 18 '13 at 15:18
Also note that I cannot access `$GLOBALS['DB']->connection`, where I actually meant `$GLOBALS['DB']->MySQLiObj` (see my question). I also tried `mysqli_insert_id()` and `$GLOBALS['DB']->MySQLiObj->insert_id` (see comments) they all don't work. – Deproblemify Nov 18 '13 at 15:20
You say "they all didn't work", but please elaborate on that: what errors are you getting from them? – Spudley Nov 18 '13 at 15:25
1

Just to be clear, the first part of my answer remains true regardless: even if `mysqli->insert_id` doesn't work exactly as I stated in the answer or per your tests, this **is** the direction you need to go to find the answer. You *cannot* use the old `mysql_insert_id()` and expect to get a value from it if you've used `mysqli` to do the insert. – Spudley Nov 18 '13 at 15:26

score 1 · Answer 4 · answered Nov 19 '13 at 08:55

1

If you are using phpMyAdmin, maybe PersistentConnections is disabled in the config file (config.inc.php).

If this is the case, change it to:

$cfg['PersistentConnections'] = TRUE;

answered Nov 19 '13 at 08:55

Casper André Casse

573
2
8
20

score 0 · Answer 5 · answered Nov 18 '13 at 14:20

0

desc is a reserved word in mysql, so you need escape it with ``

INSERT INTO Produkte (Name,Price,`Desc`,User)

list of reserved words

answered Nov 18 '13 at 14:20

rray

2,518
1
28
38

I changed that, didn't fix it – Deproblemify Nov 18 '13 at 14:35

NoobEditor · Answer 6 · 2013-11-18T14:29:56.800

0

mutliple errors i guess :

mysqli_insert_id($con) //$con is connection obj

Sql query : extra semi-colon in sql-query end and reserved keyword DESC used

 "INSERT INTO Produkte (Name,Price,`Desc`,User)
VALUES ('".$_POST['name']."','".$_POST['price']."','".$_POST['description']."','".$_SESSION['user']."')";

Refer manual : http://us3.php.net/manual/en/mysqli.insert-id.php

edited Nov 18 '13 at 14:29

answered Nov 18 '13 at 14:22

NoobEditor

15,563
19
81
112

score 0 · Accepted Answer · answered Dec 05 '13 at 17:56

0

OK I finally made it! In my Class I added the function insert_id() and then added the insert_id variable locally.

public function insert_id()
{
    $result = $this->MySQLiObj->insert_id;
    return $result;
}

answered Dec 05 '13 at 17:56

Deproblemify

3,340
5
26
41

mysql_insert_id() echos 0

7 Answers7