LAST_INSERT_ID() Returns Zero

LAST_INSERT_ID()
I'm considering using the LAST_INSERT_ID() function to pull the last id for a row inserted by a user. However, I am a little concerned about this and wanted to get a little clarification.

My database will have multiple users logged in, but the connection to the database uses a single username. So when a user logs on, the connect string has a hardcoded username and password. If this is the case, is there any chance that one user will pick up the LAST_INSERT_ID() for another user?

Also, if anyone has any experience, feedback, or advice for using LAST_INSERT_ID(), please feel free to share.

Ideally, I want to hear that it pulls the last ID for the session as opposed to the user if that makes sense.

---

LAST_INSERT_ID
How do I choose the table with this function?

The Query:
$query = "SELECT LAST_INSERT_ID() FROM tablename";

That right? Or it it like...

$query = "SELECT LAST_INSERT_ID(tablename)";

---

LAST_INSERT_ID()
I am writting a DELPHI program that takes data and posts them into MySQL.

Data like name or email address, it gets placed in the "people" table, comments are placed in "comments" table - The problem I'm running into is when I try to get LAST_INSERT_ID() to get UID value (autoincrement) from people table for storing it in comments table - so im later able to join thes two together.

<of course im not creating so stupid program - I used this just for description to make it easyer>

LAST_INSERT_ID() does not works like manual says: "LAST_INSERT_ID() SQL function returns the last last explicit value inserted into the AUTO_INCREMENT column. It is connection-specific, so ist return value is not affected by another connection also doing inserts. "

1. Is returns 0 when used this way: SELECT @@IDENTITY or SELECT LAST_INSERT_ID() or SELECT LAST_INSERT_ID() FROM PEOPLE
2.It returns everythin if used this query: SELECT LAST_INSERT_ID(UID) FROM PEOPLE

It returns 0 even after insert where Im doin mistake?

---

LAST_INSERT_ID
I have an ASP page that is posting to a mysql db. I want to get the Auto Increment 'cusID' of the last record and put it in a URL string.

ASP so far

Set rs2 = Server.CreateObject("ADODB.Recordset")
autoidsql = "SELECT LAST_INSERT_ID() FROM tbl_customers"
rs2.Open autoidsql, conn
tmpid = rs2
Response.Redirect("custviewdeal.asp?key=" & Server.URLEncode(tmpid))

---

Last_insert_id
I am getting very confused trying to get last_insert_id to work:

I have two tables
Country
   with fields:  cid (auto increment primary index)
                 country_name (varchar 40)

Region
   with fields:  rid (auto increment primary index)
                 cid (INT)
                 region_name (varchar 40)

I need to insert a country into the country table then insert a region into the region table aith cid being the same value of the newly created cid inthe country table.

But, everytime I run this it just gives cid in the region table a value of 0, not the new index value that was created in cid. Code:

---

LAST_INSERT_ID()
I am trying to use the LAST_INSERT_ID() function but, always get 0 (zero) as the returned value.
Here are the details as I see it.

Server: Novell Netware 6.0
MySQL: 4.1.10
MySQL Query Browser: 1.1.5

Table name: Animals myisam
Field: ID - mediumint(9) not null auto inc
Name - char(30) not null
Index: ID - Primary btree


COMMANDS:

INSERT INTO animals (ID, name) VALUES (NULL,'rabbit') ;

SELECT Last_Insert_ID() ;

I assume that MySQL Query Browser would only use one connection and when MySQL Administrator is checked there are only two threads and the PID is not changing. What am I missing?

---

Last_Insert_ID()
Does the 'Last_Insert_ID()' return the last ID entered into the table or the last id entered into in a session (this would be a stored procedure).

i.e. In a multi-user system, if I use this to return say an invoice number could I get the wrong number back?

1>User one runs insert SP that Inserts Record
2>>User Two runs insert SP that Inserts a record
3>User One: Script now calls Last_Insert_id()

Does the script at (3) return the ID from the insert at (1) or (2)?

---

Using SET @var = Last_insert_id()
I am using this update, and it is giving me an error:

UPDATE table
SET @var1 := last_insert_id(), name = 'justin'
For some reason this is giving me the following error:
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@var1 := last_insert_id(), name = 'justin' at line 2

I also tried @var = last_insert_id(), but still no go. How can I savfe the last_insert_id to a variable without making a second query?

---

Last_Insert_ID
I'm having problems using the Last_Insert_ID statement on Windows 98 workstations.  For some reason it's either not supported or I am missing a connection.  It's working perfectly on Windows 2000 and XP workstations.  If anyone has any ideas as to why this statement isn't working,

---

LAST_INSERT_ID()
i tried to use LAST_INSERT_ID() so that i insert the correct ID for my table. It works up until id number 10, where the script decides not to put any mopre ideas into the table. anyone know why?

---

Last_insert_id()
I'm having a problem with using last_insert_Id().
This is my query in coldfusion, everything between <cfquery datasource="reviews"></cfquery> is mysql code exept for #Trim(FORM.country_name)# stuff which are form varialbes.
#Trim(FORM.country_name)# = Cuba in form field, you get the idea.
If I am using the wrong syntax as stated in the error message, could you show me the right syntax to use.

here is my error message:
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; SELECT set @country_id = last_insert_id(); INSERT INTO resort(country_id' at line 2

---

How To Reset Last_insert_id();
I use the the following notation to "SELECT last_insert_id();" for getting the next id in my database to do some data testing. The problem now is that if I have about 788 rows of valid data and deleted 200 rows of test data, my "next" id when I do "SELECT last_insert_id();" would not be 788. The id numbers continue to be large as if I didn't get rid of that junk data.

---

Last_insert_id() Problems
I have a stored proc. It creates a user. The two tables are for a customer and a user id. Both tables have auto_increment ids (company_id and user_id).

Problem is through some research that last_insert_id() will only remeber the first id it inserted and thats it. Is there anyway around this??

---

Retrieving LAST_INSERT_ID
"SELECT LAST_INSERT_ID()" returns the last inserted value of an AUTO_INCREMENT column.
But can only be used after an insert (as far as i know).
What should i do to retrieve this value from a specific table?

I've tried:
SELECT LAST_INSERT_ID(table.column)
SELECT LAST_INSERT_ID(table)
SELECT LAST_INSERT_ID(column)

...but didnt work. I dont know if MySQL doesnt support these statements or if the version im using is old (4.0.15). Ill upgrade as soon as i get the time to download the newest version (35mb)

---

References; LAST_INSERT_ID()
I have two table looking like these:

create table tOne (ID int(5) AUTO_INCREMENT, Name CHAR(10), PRIMARY KEY (ID));
create table tTwo (tOneID int(5) references tOne(ID), ...);

Now I want to insert a row into tTwo. I expected an error, when inserting a tOneID not existing in tOne. But nothing happens!
Where is my mistake?
______________________
I know there is that LAST_INSERT_ID() keyword, to insert the last generated ID of tOne into tTwo.
Is there an equivalent keyword to insert the LAST_SELECTED_ID of tOne???
______________________
I'm using mySQL Server 4.0

---

Last_insert_id() Returning 0
I was using a stored function to return the last inserted record id. With the latest version of the database that functions appears to have stopped working. Is there a fix or a work around that I can use for the meantime ?

---

LAST_INSERT_ID(expr)
My question is that if ten users inserts a row simultaneously, which auto_increment row value will be returned by function
LAST_INSERT_ID(expr)

---

Last_insert_id Returning 0
Im trying to something like the following:

INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text');

The first insert goes ok and a record in foo is created, but the id inserted into foo2 is 0 and thats not right.

---

SELECT LAST_INSERT_ID()
i just checked my slow_query_log and the only queries causing me trouble are very simple SELECT LAST_INSERT_ID() that i use heavily. this query takes over 3 seconds on a larger table! that is quite a strain for such a simple request. what strikes me is that it examines all (!) rows in order to retrieve this. anybody know why that is and how this can be optimized? btw, i'm using 4.02.

---

Select Last_insert_id Query...
I have a site with various parts which allow users to enter info via forms
which gets entered into various tables in a MySQL db (I'm using ASP, rather
than PHP).

I have an ecards bit, and when users send a card, their data is entered into
the db via something like:

INSERT vars into ecard_tbl....

I just read that I can use this to get the most recent id from the
autoincrement field in the table:

SELECT LAST_INSERT_ID

However, it doesn't seem to need a table name for that. What if exactly at
the same time another users enters something into my guestbook. That will
submit data to the same MySQL db, so won't the 'SELECT LAST_INSERT_ID' give
me that ID, and not the ID from the card submission?

Am I missing something very obvious here? I checked the MySQL docs but can't
see anything obvious.

---

Creating Variable From LAST_INSERT_ID()
I am trying to declare a variable that I can pass to subsequent tables that I am creating, based on the auto_increment value of a previous insert. here is an attempt. Code:

---

Does Updating Id=LAST_INSERT_ID(id) Cause Writes?
Does MySQL avoid updating id in "id=LAST_INSERT_ID(id)"? Or to avoid an update I need to get id from SELECT, then INSERT if the row doesn't exist (and hope that keys are still cached to avoid disk I/O)?

I'm adding over 100 million rows where there may be more than 10 million inserts a day. There's an entity table that I need to insert new entries into. LOAD DATA may not be possible as data is accessed through a remote database over the network. Code:

---

Auto_increment And Last_insert_id Within A Cursor
I'm having a problem with using AUTO_INCREMENT / LAST_INSERT_ID within a cursor. Specifically, as I loop through my cursor, I insert a row into the table with the auto increment feature. I then get the last insert id, and I use that to update a different table.

However, every time I call LAST_INSERT_ID, it gives me the value used for the first insert (of the loop) into my auto increment table. So if I insert five rows into my table with the auto increment (separate inserts), and I call last_insert_id after each one, I get the value used by the first insert each time.

I expected it to give me the most recent value. Can anyone tell me how to get the value used by the most recent insert? I'll include a simple test case below in case anyone wants to try it (or if my explanation is unclear). Code:

---

How To Use T1's ID With LAST_INSERT_ID() For An Insert ID In T2 ??
I'm trying to use the last_insert_id function in order for me to make a copy of the id in t1 and insert it in t2. By this point I'm not sure if my query is incorrect or if it is something else I should change to have this working. When I execute a submission it's register an auto_increment id as it should in t1.

If I direct after that run a SELECT LAST_INSERT_ID()query in Mysql, the result gives me a 0. Does anyone know what's wrong here? I'm using InnoDB tables with MySQL version 4.1.7 and beneth is my query.

$query= mysql_query("INSERT INTO submissions (NameInsured,..)
VALUES ('$NameInsured',..., )")
or die (mysql_error());

$query= mysql_query("INSERT INTO test SET EvaluationID=SubmissionID WHERE SubmissionID=LAST_INSERT_ID()")

---

LAST_INSERT_ID() In START TRANSACTION
I have a transaction that runs a 3 insert statements.

The first inserts an record into the "patientsmain" table, generating a patient ID key (PATNR).

The second inserts into "patientdetail" table, this has a foreign key (also called PATNR), which I populate using LAST_INSERT_ID().

The 3rd insert goes into "patientpanel" table, it is similar to the 2nd INSERT. This table also has a PATNR foreign key but using LAST_INSERT_ID() here fails as it takes the ID generated by 2nd INSERT rather than the 1st INSERT.

How can I have the 3rd insert (or subsequent inserts) use the ID generated by my first insert? Code:

---

Populating Tables With Last_insert_id() Function
A while back I asked a question about populating foreign key fields in a database, and I was told that people usually use a last_insert_id() function to do this. I understand that this would work if I was populating the database one row at a time (appparently the function only inserts 1 id at a time), but I planned to enter the data all at once using tab delimited .txt files and LOAD DATA INFILE.

I planned to populate all the tables in this way except the linking tables, eg. garment_to_colour etc. where the foreign key problem exists. Is it true to say that it's not possible to use the last_insert_id() function to populate the linking tables if I insert the data all at once as described above?

Just wondered if someone could help solve this problem?

CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`,
`title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

---

LAST_INSERT_ID With Multiple INSERT Statement
i have the following question:

When inserting many entries into a table with 1 auto_increment key-attibute
(say attribute user_id) and one data attribute (say attribute name)

CREATE users(user_id int key auto_increment, name text)

like

INSERT INTO users(name) VALUES ('pete'),('josh'),('carl')

When the inserting is done in a concurrent way (say 5 scripts do inserting operations like the one above) how do I get the autoincremented ids for all the inserted rows ? I know that LAST_INSERT_ID() yields the autoinc id of the first inserted row (of the multi-insert statement) say 1002 for 'pete' in this case and is also concurrent-safe. But is the insert statement atomic that i can assume that the subsequent generated ids for 'josh' and 'carl' are consecutive i.e. 'josh' gets 1003 and 'carl' gets 1004 when many scripts insert into this table ?

I dont want to use a subsequent SELECT statement to fetch the auto_incremented ids.

---

MySQL-LabView8.0 Select Last_insert_id
I'm making a measuring program based on LabView 8.0, the results of measurements are saved into MySQL database.

The problem is when I'm inserting datas into MySQL I want to know into which line the measurement is been saved. So I used the LAST_INSERT_ID, but all what I'm getting back is 0. When I tried this in MySQLCC this worked just fine.

---

LEFT() Returns Nothing
How comes this always return nothing even though i have data in the name field

SELECT Left(joueurs.name,1) AS newName FROM joueurs

---

LIMIT Returns
i have a table with 64K+ rows, a query like this:

select * from mails order by clave desc limit 1;

effectively returns the last record (key:64103) but if i try a query with a hight number as offset like this:

SELECT * FROM mails LIMIT 64000 , 30;

returns nothing, i've tried in the console and from phpmyadmin with the same result, a "select count(*) from mails;" query returns 64096.

does anybody knows what could be wrong? i've noticed mysql stops returning rows after ~21998 records

---

Limit Returns
Is there a way to do something in the WHERE clause that will only pull back entries in the DB from the last 7 days?
Could this work: WHERE field < NOW()-7 or something like that. Or could I do a php $date = date()-7 or seomthing like that?

---

How To Get 5 Random Returns From Mysql?
I have a table that stores username and image_id. Like -

user_name | image_id

user_1 | image_1
user_2 | image_2
user_3 | image_33
user_2 | image_56 etc.

I need to get 5 random image_ids, each from a different user. I was considering using rand(), but google told me that this will slow down the sql server, bigger the database.

PHP

SELECT DISTINCT user_name, image_id FROM tablename ORDER BY RAND() LIMIT 5

---

SQL Select Statement Returns Nothing?
When I view this page, nothing appears in the browser

PHP

while($row = mysql_fetch_array($result)){$Identifier = $row['ID'];$random_thumb_query = mysql_query("    SELECT userid, filename    FROM photo_photoplog_fileuploads    WHERE moderate = 0    AND userid = '$Identifier'    ORDER BY dateline DESC    LIMIT 1") or die(mysql_error());$random_thumb_row = mysql_fetch_array($random_thumb_query);echo $random_thumb_row;$random_thumb_nail = '<img src="/photoplog/images/'.$random_thumb_row['userid'].'/small/'.$random_thumb_row['filename'].'" alt="" border="0" />'


http://miamiasp.com/providers.php
u:luke
x: urtnowski
is the $Identifier variable ok?

---

Getting The Row Where Forumula Returns Smallest Value
i am trying to select one particular row of data from a table which has the least difference between two other columns from another table.
i have been looking at the documentation and keep trying. this is the closest i could get but it is still not right.

select * from photographs group by ( select min(p.rate-p.comments) from photorates as p )
this could sometimes return me multiple rows and if i were to change it to:

select * from photographs group by ( select max(p.rate-p.comments) from photorates as p )
having max instead of min, i get the min and max rows of data. i suppose i should only get one row of data since i need either the least or most difference from the math.

---

LIMIT 5 Only Returns 2 When Using GROUP_CONCAT
I'm trying to limit my results in the following query. When I make this query without the GROUP_CONCAT lines, I get the number of results I specified in LIMIT, when I put those lines back in, I get less than the number of results I specify with LIMIT....

---

Select * Returns No Data
I have a problem where if I issue a select * from against a database it returns no data,

but if I select column from it returns the data.

Why would the * not be working as a wildcard?

---

Troubleshooting Carriage Returns In Csv
I am exporting data from filemakerpro in a csv file and uploading it via cocoamysql/phpmyadmin, from which it is viewable online through dynamic pages. Carriage returns in Filemaker are translating through as small boxes (firefox), small boxes with X's inside (safari); in the csv file they are tranlating as either spaces or nothing (just jams the words together where there should be a return).

Any ideas on how to eliminate this problem? or a direction to look in?

---

Found_rows() Everytime Returns 1
I use this query
SELECT SQL_CALC_FOUND_ROWS * FROM prods LIMIT 100,10;

I'd like to know how many rows this query returns without limit, but
using this
select FOUND_ROWS();
the result is everytime 1

You know why?
No other query are processed before the found_rows().

---

Import CSV With Carriage Returns
How do I use the import function in the phpMyAdmin to import a CSV file that contains carriage returns. Or is there a better format to import data with carriage returns.

---

CONCAT () Returns BLOB
MySQL ver. 3.23.49

SELECT tblHrsClass.classid , CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl FROM tblHrsStudent LEFT JOIN tblHrsClass ON tblHrsStudent . classid =tblHrsClass.classid GROUP BY tblHrsClass . classid HAVING COUNT(tblHrsStudent . classid )<19

The query above is broke. I'm using it to build a list box.

I can run the query without the CONCAT function and pulls the expected data.

When I add CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl,

class_ttl is returned as a BLOB of a certain size.

---

Result Set Returns A Null Value
How do work on a situation when result set returns no records ???when result set returns a value null how do you send it to some page

I have tried </jsp:forward aaa.jsp>  but it does not seem to work .Can any body suggest a remedy to it.

---

Line Returns In Fields
In many of my text fields in my table, there appears to be a line return before the text begins (I can see the line return when the table is displayed in myphpAdmin). But when I EDIT the line, there is no line return... the text is at the TOP of the box.

However, if I delete the line, and re-type it, the line return goes away. I tried the TRIM command and it doesn't work. Its causing a problem because I can't compare these columns to other tables that don't have the line return.

---

Remove Carriage Returns
I have a text file with email addresses that I'm importing into a table and there are carriage returns after each email address which is causing problems. Is it possible to remove the carriage returns using a query?

---

No Carriage Returns In An CFM Script
I have a simple publishing script written in Cold Fusion. When I insert text via the admin web page form the script doesn't take my carriage returns into the database. I have to write html-code <p> wich is very frustrating. Does anyone have a clue how to fix this problem?

I've tried to get a solution via Cold Fusion sources but without success. I don't know if the problem is in the CF scripting or the MySQL database.

I have My SQL 3.23.58 and Cold Fusion 6 running on a Linux Red Hat with Apache.

---

Clause Returns No Results
i have a product table and i execute a simple query but it doesnt seem to give me any results

Select * from product where ProductName = 'Age of Empires III';

the above query doesnt work but this 1 does
Select * from product where ProductName LIKE 'Age%';

---

DATABASES Returns Empty Set
I have mysql installed and many projects working off it. Now when i go into command line and type show databases; it says empty set? I have a database on my pc. why isnt it working threw command line????? Its saying empty set.

---

Query Returns Empty Set
i have this query:
Code:

select p.url,s.tipo,pw.url as wrapper from secciones s
join plantillas p on s.plantilla=p.clave
join plantillas pw on p.wrapper=pw.clave
where s.clave=1

it works fine, the problem is that from time to time the second join condition won't match, because by design the field p.wrapper is optional, if that happens the query returns 'empty set' even if the where condition and the first join condition do match, is it possible to modify that query so it needs to match only the first join and the where clause to return the result set (i.e. make the second join optional)?

---

Group By Returns Invalid Dates
Hi i have one table:

ID, User, Score, Date
1, mac, 1234, 2007-12-09 09:01:07
2,kurca, 3232, 2007-12-05 09:43:57
3, mac, 999999, 2007-11-07 09:44:14

I wan to create a high score list which will represent the best scores but not repeating anyone for example:
1, mac, 999999, 2007-11-07 09:44:14
2,kurca, 3232, 2007-12-05 09:43:57


Now i use:
SELECT ID, User,MAX(Score) as maxScore,Date
FROM `table`
GROUP BY User
ORDER BY maxScore DESC

with this query the score is right but the date is invalid example:
1, mac, 999999,2007-12-09 09:01:07
2,kurca, 3232, 2007-12-05 09:43:57

---

REGEXP That Returns Index Of Match?
Is there any way to use a REGEXP but have it tell me where in the string it occurred?  Or is there a way to do what I want without REGEXP?

I have a field (lyrics of songs) that, in the case of songs sung in Japanese, contains Japanese lyrics followed by the English equivalent.  I want to be able to find the spot where the English starts and either return just the Japanese or return just the English.  There may be sets of square brackets with English characters (guitar chords) inline in either the Japanese or English, so I also have to watch out for that.

To help you understand what I mean, let's hypothetically say that there is a regexp "function" that returns the index to the first occurrence of a match (which MySQL's REGEXP operator does not appear to do).  If that were the case, I would do something like this:

SELECT SUBSTRING(Lyrics,0,REGEXP(Lyrics,'^([[^]]])?[a-zA-Z]')) AS Japanese FROM song...
SELECT SUBSTRING(Lyrics,REGEXP(Lyrics,'^([[^]]])?[a-zA-Z]')) AS English FROM song...

---