mysql_insert_id returns 0, connections issue?

[dflow]

after inserting data i get 0 sometimes

i created a mysql_connect for it

 

 

<?php
$maincon = mysql_connect('local', "root", "root");
mysql_select_db("root");


then

//insert and

$success = mysql_query($query);
echo $ref_id = mysql_insert_id($maincon);
?>

$ref_id is returned as 0, although the connection is there

var_dumping the query returns the values

[PFMaBiSmAd]

Is the last query an INSERT query?

[dflow]

Is the last query an INSERT query?

yes

[PFMaBiSmAd]

Does that table have an auto-increment index?

[dflow]

Does that table have an auto-increment index?

yes

ID int NULL AI

[PFMaBiSmAd]

Are you sure the INSERT query executed without any error?

[dflow]

[quote author=PFMaBiSmAd link=topic=353420.msg1669129#msg1669129 date=1328702687]
Are you sure the INSERT query executed without any error?
[/quote]
[code}
var_dump($query);

	returns the data no error
var_dump(mysql_error());

returns string(0) "" 
		var_dump($_POST);

returns array(20)

 

the weird thing is that it sometimes works and sometimes doesnt, no connection errors

how could i force an id  insert ?

[litebearer]

old bleary-eyed man question - could the AI id field being NULL rather than NOT NULL be an issue?

[PFMaBiSmAd]

var_dump($query); doesn't mean anything as far as if the query executed or not. That would just show the sql query statement itself.

 

var_dump(mysql_error()); doesn't mean anything if you have multiple connections, which your $maincon name implies. You would need to use mysql_error($maincon);

 

Also, if you do have more than one database connection, you would need to use the correct connection link in the mysql_query statement.

[dflow]

var_dump($query); doesn't mean anything as far as if the query executed or not. That would just show the sql query statement itself.

 

var_dump(mysql_error()); doesn't mean anything if you have multiple connections, which your $maincon name implies. You would need to use mysql_error($maincon);

 

Also, if you do have more than one database connection, you would need to use the correct connection link in the mysql_query statement.

mysql_error($maincon);

returns no error

 

what do mean by this " correct connection link in the mysql_query statement."?

[PFMaBiSmAd]

Do you actually have more than one database connection?

[dflow]

Do you actually have more than one database connection?

no to the same one

this is a page that works on a per switch case for example:

 

i have a general mysql_connect same db(root)

require_once(db1.php);

 

then to make sure the mysql_insert_id() gets a connection

i created $maincon like this

switch($page) {
case 'add':
require_once('dbinsert.php');
//insert query and mysql_insert

// dbinsert.php is  :
$maincon = mysql_connect('local', "root", "root");
mysql_select_db("root");


//then
$success = mysql_query($query);
$ref_id = mysql_insert_id($maincon);

 

 

 

 

[DavidAM]

You are only providing snippets of code, so it is impossible for us to find the problem. For instance, is there any other database interaction between executing the INSERT and retrieving the ID? If so, that could be causing a problem.

 

mysql_insert_id

Return Values

 

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.

 

Also, you have to check every step of the way for any errors.

 

$maincon = mysql_connect('local', "root", "root");
if ($maincon === false) {
  print "Unable to connect to server. I don't know what to do so ...";
  die();
}

if (mysql_select_db("root") === false) {
  print "Unable to select database. So ...";
  die();
}

$query = "INSERT ...";
$success = mysql_query($query);
if ($success === false) {
  print "The query failed: " . $query . " with error: " . mysql_error();
  die("so");
}
$ref_id = mysql_insert_id($maincon);

 

Note: The die() function is only suggested here as a debugging tool. The script should do something intelligent when an error condition prevents future action. Calling the die() function will cause the script to exit, leaving your user with a blank white screen. Not very user friendly.

 

[dflow]

You are only providing snippets of code, so it is impossible for us to find the problem. For instance, is there any other database interaction between executing the INSERT and retrieving the ID? If so, that could be causing a problem.

 

mysql_insert_id

Return Values

 

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.

 

Also, you have to check every step of the way for any errors.

 

$maincon = mysql_connect('local', "root", "root");
if ($maincon === false) {
  print "Unable to connect to server. I don't know what to do so ...";
  die();
}

if (mysql_select_db("root") === false) {
  print "Unable to select database. So ...";
  die();
}

$query = "INSERT ...";
$success = mysql_query($query);
if ($success === false) {
  print "The query failed: " . $query . " with error: " . mysql_error();
  die("so");
}
$ref_id = mysql_insert_id($maincon);

 

Note: The die() function is only suggested here as a debugging tool. The script should do something intelligent when an error condition prevents future action. Calling the die() function will cause the script to exit, leaving your user with a blank white screen. Not very user friendly.

 

ok guys,

thanks for the feedbacks.

it seems that i wasn't QAing systematically as it was a long form.

apparently i had 2 INT fields which if left blank triggered the mysql_error and caused the $query not be executed.

 

that's a different issue, i set them as NULL in the db but the error was still recurring why is that?

 

so i solved it by checking if the fields are:

if(isset($_POST['field']))

 

Conclusion find someone to QA

and I should

[PFMaBiSmAd]

i set them as NULL in the db but the error was still recurring why is that?

 

If you list the field in the query statement, you must supply a value in the query that is appropriate for the field definition (even if you must use the NULL or DEFAULT keyword.) For a numerical field, where you would not have any quotes around the value in the query, supplying nothing in a php variable would result in an sql syntax error.