CODEWITHSUNDEEP
mysql
antonpuz
antonpuz

Reputation: 3316

getting data directly from insert query after running it

i have a database i insert item into using php. i want to get the ID which is auto increment column, i read the manual and it says that the mysql_query returns a resource variable rather than boolean, is there any way to retreive data from that resouce? 

echo $query_ins = "INSERT INTO `house` VALUES ('' , '$city', '$street', 'num');
$res = mysql_query($query_ins);

the first field is the ID field i am interested in.
i tried mysql_fetch_assoc and mysql_result function to convert the $res into array or retrieve data directly like i would do with SELECT query but no succes. any suggestions?

Upvotes: 1

Views: 157

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562691

See the manual: mysql_insert_id — Get the ID generated in the last query

This is really something you should have been able to find on your own easily, without posting to StackOverflow. It's to your advantage to review the documentation of any API you're using.

Re your comment:

SELECT * FROM house WHERE id = LAST_INSERT_ID()

This supposes you have an auto-incrementing primary key.

If you generate a nondeterministic primary key value on insert, for example using RAND() or UUID(), then there is effectively no way to retrieve the value generated. You should generate the value before doing the INSERT, so you have a literal value you can use to look up the row.

SET @r := RAND();
INSERT INTO house (id, ...) VALUES (@r, ...);
SELECT * FROM house WHERE id = @r;

MD5 is deterministic, so supposing you have an original key string that you hash, you should be able to retrieve the row using that MD5 expression and the original string.

INSERT INTO house (id, ...) VALUES (MD5('xyzzy'), ...);
SELECT * FROM house WHERE id = MD5('xyzzy');

Upvotes: 1

Related Questions