Jump to content

last_update_id/max() don't work through PHP


basic0

Recommended Posts

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?

Link to comment
Share on other sites

// 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

Link to comment
Share on other sites

// 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.