0

I'm trying to use the answer given at : How to get ID of the last updated row in MySQL?

I want the ID of the row which was updated. But the following doesn't work giving the error Fatal error: Call to a member function fetch_assoc() on a non-object. 

$query = "
  SET @update_id := 0;
  UPDATE locations SET owner_player_id='$player_id', id=(SELECT @update_id := id)
   WHERE game_id='$game_id' LIMIT 1;
  SELECT @update_id;
  ";

$result = $mysqli->query($query);
$row = $result->fetch_assoc();
$location_id = $row['update_id'];

Thanks

Edit:

$mysqli->error gives 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 'UPDATE locations SET owner_player_id='5', id=(SELECT @update_id := id) WHERE gam' at line 1

Community
  • 1
  • 1
  • 1
    This indicates that the query is apparently failing .. you need to do some kind of error checking – Explosion Pills Apr 14 '13 at 21:52
  • is your id column is an autoincrement ? – Manoj Purohit Apr 14 '13 at 21:58
  • yes `id` as in `locations.id` is auto-incremented, but it is not used in this query I don't think. –  Apr 14 '13 at 22:00
  • have you tried `$location_id = $row['@update_id'];` and try using `$mysqli->multi_query` – Manoj Purohit Apr 14 '13 at 22:20
  • @legendinmaking just read your comment, you are correct but have just posted the answer below before reading, thanks anyway, also had to use `mysqli::next_result` and `mysqli::store_result` to get the right data. –  Apr 14 '13 at 22:42

2 Answers2

0

Your SQL has failed, that is why your subsequent fetch_assoc() method is also failing.

You will want to look into running your query without using the variables (@update_id) as you are intending, and look at using mysqli::$insert_id.

Without knowing what it is you are trying to do exactly, it's hard to be more specific.

  • The query `UPDATE locations SET owner_player_id='$player_id' WHERE game_id='$game_id' LIMIT 1` appears to work, it's the just use of the code from the stackexchange answer in the link given above which stumps me because I'm not a MySQL guru, also `$location_id = $mysqli->insert_id;` does not work because `$mysqli->insert_id` only works with the AUTO_INCREMENT attribute, see php.net/manual/en/mysqli.insert-id.php I've already fell victim to that :p –  Apr 14 '13 at 21:58
  • If you are wanting to get the `id` of the item you've just updated, you will want to select again. Is there a reason your `id` is not AUTO_INCREMENT? –  Apr 14 '13 at 22:05
  • `locations.id` is auto-incremented, but is the auto-incrementation being used within this query? or does that not matter? –  Apr 14 '13 at 22:09
  • The `insert_id` *should* return the `locations.id` value corresponding to the row you updated. –  Apr 14 '13 at 22:11
  • Can you see why this doesn't work then? `$query = "UPDATE locations SET owner_player_id='$player_id' WHERE game_id='$game_id' LIMIT 1"; $mysqli->query($query); $location_id = $mysqli->insert_id;` Thanks –  Apr 14 '13 at 22:14
  • And you are sure that a row is being updated? What's `$mysqli->affected_rows` have? –  Apr 14 '13 at 22:18
  • the row is being updated, i checked it in phpmyadmin, and `$mysqli->affected_rows` gives the right answer. should i be using that instead? –  Apr 14 '13 at 22:22
  • sorry `$mysqli->affected_rows` gives 1, which is the correct number, (i got confused because i flushed the database and the ID i was expecting was also 1) –  Apr 14 '13 at 22:24
0

Have to use mysqli::multi_query not mysqli::query. Also then have to use mysqli::next_result and mysqli::store_result to get the correct data. 

$query = "SET @update_id := 0; UPDATE locations SET owner_player_id='$player_id', id = ( SELECT @update_id := id ) WHERE game_id='$game_id' LIMIT 1; SELECT @update_id;";
$mysqli->multi_query($query) or die($mysqli->error);
$mysqli->next_result();
$mysqli->next_result();
$result = $mysqli->store_result();
$row = $result->fetch_assoc();
$location_id = $row['@update_id'];