last_update_id/max() don't work through PHP

[basic0]

Greetings.

 

mysql-standard 5.0.27 macosx powerpc

php 5.2.1

macosx 10.4.9

 

mysql> describe forms;

+------------+---------------------------+------+-----+---------+----------------+

| Field      | Type                      | Null | Key | Default | Extra          |

+------------+---------------------------+------+-----+---------+----------------+

| id        | int(10) unsigned zerofill | NO  | PRI | NULL    | auto_increment |

| formtype  | varchar(50)              | NO  |    |        |                |

| formdata  | int(10) unsigned zerofill | NO  |    |        |                |

| patient_id | int(10) unsigned          | NO  |    |        |                |

| resolved  | enum('Y','N')            | NO  |    |        |                |

+------------+---------------------------+------+-----+---------+----------------+

5 rows in set (0.07 sec)

 

 

mysql> describe emg_formdata;

+------------+---------------------------+------+-----+---------+----------------+

| Field      | Type                      | Null | Key | Default | Extra          |

+------------+---------------------------+------+-----+---------+----------------+

| id        | int(10) unsigned zerofill | NO  | PRI | NULL    | auto_increment |

| date      | varchar(12)              | NO  |    |        |                |

| prev_emg  | enum('Y','N')            | NO  |    |        |                |

| prev_eeg  | enum('Y','N')            | NO  |    |        |                |

| prev_evp  | enum('Y','N')            | NO  |    |        |                |

| prev_loc  | varchar(50)              | YES  |    | NULL    |                |

| diagnosis  | varchar(160)              | YES  |    | NULL    |                |

| medication | varchar(160)              | YES  |    | NULL    |                |

| history    | varchar(160)              | YES  |    | NULL    |                |

| questions  | varchar(160)              | YES  |    | NULL    |                |

| urgent    | enum('Y','N')            | NO  |    |        |                |

| visual    | enum('Y','N')            | NO  |    |        |                |

| brainstem  | enum('Y','N')            | NO  |    |        |                |

| arms      | enum('Y','N')            | NO  |    |        |                |

| legs      | enum('Y','N')            | NO  |    |        |                |

| routine    | enum('Y','N')            | NO  |    |        |                |

| sleep      | enum('Y','N')            | NO  |    |        |                |

| ambulatory | enum('Y','N')            | NO  |    |        |                |

| study1    | enum('Y','N')            | NO  |    |        |                |

| study2    | enum('Y','N')            | NO  |    |        |                |

+------------+---------------------------+------+-----+---------+----------------+

20 rows in set (0.06 sec)

 

I want to run something like this:

 

"INSERT INTO emg_formdata VALUES(NULL, '01/02/03', 'N', 'N', 'N', 'something', 'something else', 'another thing', 'blah', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N')";

 

then this:

 

INSERT INTO forms VALUES(NULL, 'emg', LAST_INSERT_ID(), '$uid', 'N');

 

With me so far? Here's the problem: when I do this through a PHP script, the last insert id never changes, though the data gets inserted as expected. If I type these commands into the mysql monitor, the last insert id number changes as it should. I can add 100 rows through the PHP script and the result of last_insert_id() and "SELECT max(id) FROM emg_formdata" will never change from zero. If I *do* issue these commands to the monitor, it'll insert the data and update the last insert id, say in this case the id number is 4. I can then run the script 100 more times and the last insert id will never change from 4. I've tried both last_insert_id() and max(id). I've tried PHP's mysql functions and PEAR::DB.

 

How can I get the auto generated id number from emg_formdata rows into the formdata column of rows in the forms table?

[paul2463]

try changing it from LAST_INSERT_ID() to mysql_insert_id() see if that makes a difference

[basic0]

// db connection code

...

 

// meat of the matter

$query = "INSERT INTO emg_formdata VALUES(NULL, '01/02/03', 'N', 'N', 'N', 'something', 'something else', 'another thing', 'blah', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N')";

 

mysql_query($query);

 

$last_insert = mysql_insert_id($connection);

 

print $last_insert;

 

-----

 

expected: 13 (next in sequence)

result: 0

[paul2463]

// db connection code

...

 

// meat of the matter

$query = "INSERT INTO emg_formdata VALUES(NULL, '01/02/03', 'N', 'N', 'N', 'something', 'something else', 'another thing', 'blah', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N')";

 

mysql_query($query);

 

$last_insert = mysql_insert_id($connection); 

 

print $last_insert;

 

-----

 

expected: 13 (next in sequence)

result: 0

 

$query = "INSERT INTO table VALUES (whatever they may be as long as they work)";

$result = mysql_query($query) or die ("Error in query" . mysql_error()); //always request error data just in case something is wrong

// if no error and the connection is still valid, mysql_insert_id() is connection specific (close the connection / lose the data)

$last_insert = mysql_insert_id(); // does not require a value to be passed

echo $last_insert; // will print out the last inserted auto incremented id value

 

I think you problem stems from the fact that you are passing NULL for the id value 

 

and on both your printed out tables you have the id value saying no to a NULL value then defaults to NULL anyway

[bubblegum.anarchy]

Like Paul stated - the first insert query is setting the id to NULL and as a result is also setting the last insert id to null to, there is no need to explicitly assign an auto increment value through the insert statement, the id value will be generated automatically... remove the first NULL from the first set of inserted values and use last_insert_id() in the last set.