hi everybody

ive got a problem with a mysql_query and the only reliable way that i can think to get around it is if its possible to obtain and use the mysql_insert_id
of the current query within the query itself.

even if that is possible, im inserting multiple rows with one query, and i need to fill 1 column with a groupId (I.E an identifier for all the rows inserted in the query.) and another containing a copy of the id for the curent row, but placing it inside a JSON string. . .

i dont know if this is even possible, but here's an idea of what i need.

for example

insert into table (auto_inc, groupId, json) values
('',mysql_insert_id(of first row in query), '{\'reqId\':mysql_insert_id(thisRowid)}),
('',mysql_insert_id(of first row in query), '{\'reqId\':mysql_insert_id(thisRowid)}),
('',mysql_insert_id(of first row in query), '{\'reqId\':mysql_insert_id(thisRowid)})

the results that i would anticipate being in the table would be

auto_inc , groupId, json
1 , 1 ,{'reqId':1} 
2 , 1 ,{'reqId':2} 
3 , 1 ,{'reqId':3}

having thought about this for hours now, im begining to think that what i want is only possible with a long and potentially unreliable loop -

say i first insert a number of dead rows into my table, only filling the auto_inc column, then i get the id of the first inserted row of the query with mysql_insert_id(); and i obtain the amount of rows inserted with mysql_affected_rows().

With this information i could create a loop which begins at the mysql_insert_id() and iterates for mysql_affected_rows() as follows-

PHP Code:

$groupId=mysql_insert_id();
for($i=$groupid; $i<($groupId+mysql_affected_rows()); $i++){
mysql_query("update table set groupId=$groupId, json='{\'reqId\':$i}' where auto_inc=$i");
} 


anybody know a better way to achieve what im talking about with using multiple queries. i dont mind using 2 queries, one to insert and one to update, but i definately dont want to put a query inside a loop as above.

thanks for looking

mark

No, it's impossible to do that in 1 pass.
Because, in your model, you would need the id of a value that is to be inserted.

What you need is to insert the rows, with empty group/json data, to have the ids, and then do a db update on the whole block to populate the needed infos.

Or, as you would in an enterprise class db, use a sequence or replace the auto_increment with a custom made counter, to have control over it.

Please let me know the error message.

http://www.webmaster-talk.com/php-fo...increment.html

http://www.webmaster-talk.com/php-fo...n-a-query.html