mysql_insert_id() not returning a value -

Question

I need to retrieve the auto increment field from my database table. I tried the following but $id is always just empty.

The insert works too.

My table is as follows: idint(9) NOT NULL auto_increment,

and id is set as primary

What am I doing wrong? $conn = mysql_connect($host,$username,$password); mysql_select_db($database, $conn) or die( "Unable to select database"); include "update_activity.php"; updateActivity("logged in", "On Break");

    $date = date("m/d/y"); $starttime = time();
    $sesh = $_SESSION['fname']." ".$_SESSION['lname'];
    $q = "INSERT INTO `breaks` (date, starttime, user) VALUES ('".$date."', '".$starttime."', '".$sesh."')";

    $query = mysql_query($q, $conn);
    $id = mysql_insert_id($conn);

    echo var_dump($id); exit;

edited to show my more recent attempts

at least `PDO`, look at the red box http://www.php.net/manual/en/function.mysql-insert-id.php — Unamata Sanatarai, Mar 30 '13 at 12:57
oh my goodness.. i know all about the mysqli thank you for the billionth reminder — I wrestled a bear once., Mar 30 '13 at 12:59
Please, specify resource for `mysql_query` and `mysql_insert_id` functions — Mikhail Chernykh, Mar 30 '13 at 13:01
Print out the SQL you're generating, and run it directly in the database - does it work then? — andrewsi, Mar 30 '13 at 13:02
@adelphia as far as i get the idea there is either a problem in the query that is executed or either because the function is decrepited. — dhpratik, Mar 30 '13 at 13:03
why? it's not required. the entire script is like 10 lines long. why bother with optional fields if it won't fix the problem. — I wrestled a bear once., Mar 30 '13 at 13:03

score 4 · Accepted Answer · answered Mar 30 '13 at 13:36

Have read all comments given and your replies to each. Only one of these is possible:

Either the query works properly OR
You are not getting the generated primary key.

Both of these can never be true.

Define, how you know query is working? Do you know the max PK before and after the running query? Is the insert happening from some other place or thread or even other user? the query is working properly from code or from your mysql client?

To diagnose the problem, we have to go though the normal way.

Dump your generated query before calling mysql_query.

Wrap a error checking system around your query call so php can tell you if the query worked or not. I am sure just by these two steps you will realize the root cause of the problem.

error_reporting(E_ALL);
ini_set('display_errors','on');

echo "before calling: $q\n";
$query = mysql_query($q, $conn);
if(!$query)
{
    echo "Error:" . mysql_error($conn);
    return;
}
echo " generated id:" . mysql_insert_id($conn);

score 1 · Answer 2 · answered Mar 30 '13 at 13:08

1

@adelphia as far as i get the idea there is a problem in the query that is executed. plz check the query properly

answered Mar 30 '13 at 13:08

dhpratik

1,100
4
19
43

the query is fine. it's inserting into the table. – I wrestled a bear once. Mar 30 '13 at 13:12
i mean is there some other way i could improve it. – I wrestled a bear once. Mar 30 '13 at 13:13

score 0 · Answer 3 · answered Mar 30 '13 at 13:06

Borrow a lead from this code extracted from here:

http://php.net/manual/en/function.mysql-insert-id.php

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>

score 0 · Answer 4 · answered Mar 30 '13 at 13:06

The problem with your insert query

$q = "INSERT INTO `breaks` (date, starttime, user) 
      VALUES ('".$date."', 
              '".$starttime."',
              '".$_SESSION['fname'] $_SESSION['lname']."')";

try with this

and main thing you are using most of the deprecated "mysql" things like "mysql_insert_id()"

score 0 · Answer 5 · answered Mar 30 '13 at 13:21

0

store the values that u want to pass into an array or variable and pass it in the insert query. its should work fine then...

answered Mar 30 '13 at 13:21

dhpratik

1,100
4
19
43

1

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/faq#reputation) you will be able to [comment on any post](http://stackoverflow.com/privileges/comment). – pb2q Apr 03 '13 at 03:05
This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](http://stackoverflow.com/questions/ask). You can also [add a bounty](http://stackoverflow.com/privileges/set-bounties) to draw more attention to this question once you have enough [reputation](http://stackoverflow.com/faq#reputation). – Vishal Apr 03 '13 at 03:22

mysql_insert_id() not returning a value -

5 Answers5