Multiple queries in one recordset/connection

[cseasy]

Guys,

I am doing an insert, for which I want to get the auto-increment value back as a recordet in the same connection, I have the following:

$query = "";
$query = $query . "INSERT INTO ";
$query = $query . " myTable ";
$query = $query . " (name,summary) VALUES ";
$query = $query . " ('" . urlencode($_POST["frmName"]) . "','" . urlencode($_POST["frmSummary"]) . )";
//echo "Query: " . $query;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

I want to add to the query:

$query = $query . "SELECT DISTINCT @@Identity AS 'newId' FROM myTable";

When trying a semicolan seperator, I get an error (this is also true in my query analyser - it doesn't work there either.) Is this even possible, and if so, what am I doing wrong?

(The queries each by themselves do work by the way.)

Thanks,
C.

[CrzySdrs]

If I recall correctly mysql_query() only allows one query per use. I can't seem to find this information in the PHP manual, but I have heard it before.

[oracleguy]

Yeah, I think you're right; the reason it might only allow one query per function call is to prevent against SQL injection attacks.

[cseasy]

Thanks guys. Is this a limitation(/feature) of mySQL as well? I can't seem to get the semicolan seperator working in query analyser either (Aqua Data Studio.)

Thanks,
C.

[CrzySdrs]

I beleive MySQL should be capable of using the semicolon to seperate queries. However, most implementations of things using MySQL use PHP as well, so it's probably not widely used and therefore may not be supported by your query analyser.

[Kid Charming]

As long as the statements are properly separated with a semicolon, MySQL will accept them stacked. Clients may or may not allow it, though.

[raf]

Kid Charming is correct.

SQL-injections by inserting a semicollon and a second query are very much possible through PHP.

cseasy,

check sticky http://www.codingforums.com/showthread.php?t=22879 on how to get the id of the last inserted record.

your select-code wount work for MySQl. @@Identity only works for Jet-db's as far as i know...
<edit>my last statement is incorrect --> "The variable [identity] is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other databases. As of MySQL 3.23.25, you can read its value with SELECT @@IDENTITY"

About your initial question --> the connection remains open during the scriptexecution (unless you explicitely close it) so you can run multiple querys without needing to open a new connection.</edit>.