0

After doing some research I think I need the Output clause. Essentially I am taking the below SQL and inserting into the specified table when I receive the location of a file I am uploading to the server. When I upload into the table the ID is an auto increment field and the primary key.

$conn = mysqli_connect($DBHOSTmy, $DBuser, $DBpass, $DBmy) or die("An error occurred connecting to the database " . mysqli_error($conn));

$query = "INSERT INTO ebwf (src,loc,iq,wq,pq) OUTPUT Inserted.id, Inserted.src, Inserted.loc, Inserted.iq, Inserted.wq, Inserted.pq VALUES ('" . $source . "','" . $finalPdf . "','y',0,0);";

echo $query;

$result = $conn->query($query); 

echo $result->num_rows;


$conn->close();

When this runs I get a return of INSERT INTO ebwf (src,loc,iq,wq,pq) OUTPUT Inserted.src, Inserted.loc, Inserted.iq, Inserted.wq, Inserted.pq VALUES ('m','scan/WF_153_140812113520.pdf','y',0,0);, but I get no return of number rows.

I really just need the ID right this minute of the inserted row, but if we can get all of these fields that would be awesome.

I pretty much copied the usage of the OUTPUT clause from a few different places, but I don't see what I'm doing wrong to get no return...

I'm trying to do some research while writing this as I have not had good response rates because people think I'm lacking it so I also found: How do I use an INSERT statement's OUTPUT clause to get the identity value?... I changed my query only to:

$query = "DECLARE @OutputTbl TABLE (id INT, src VARCHAR, loc VARCHAR, iq INT, wq INT, pq INT);

INSERT INTO ebwf (src,loc,iq,wq,pq) OUTPUT Inserted.id, Inserted.src, Inserted.loc, Inserted.iq, Inserted.wq, Inserted.pq INTO @OutputTbl(id, src, loc, iq, wq, pq) VALUES ('" . $source . "','" . $finalPdf . "','y',0,0);";

I sadly still get nothing.. Hopefully this will give enough info as to what I should do next.

Community
  • 1
  • 1
Cayce K
  • 2,288
  • 1
  • 22
  • 35
  • 2
    `$last_insert_id = $conn->insert_id;` OUTPUT is not used in MySQL in this manner – cmorrissey Aug 12 '14 at 15:57
  • @cmorrissey First I've ever seen this and wouldn't even have thought that was possible. Thank you! Can you add an answer so that I can set it for this? – Cayce K Aug 12 '14 at 16:52
  • also.. do you know where OUTPUT is used and why I saw it used for MySQL?.. – Cayce K Aug 12 '14 at 17:01

1 Answers1

2

@cmorrissey provided a great solution...

$last_insert_id = $conn->insert_id;

This returns the last inserted id (I think primary key that is auto incremented is the most correct explanation.) This is better to use than OUTPUT. I'm not sure why OUTPUT is incorrect to use as I have seen it so many places and this only once from the user.

Cayce K
  • 2,288
  • 1
  • 22
  • 35