SELECT after INSERT?

[eMonk]

<?php

include("../includes/connect.php");

$query_1 = "INSERT INTO pets VALUES
  ('', '', dog)";
$result_1 = $db->query($query_1);

$id = mysqli_insert_id($db);

// Here's where I'm having problems fetching the data that was just inserted. 

$query_2 = "SELECT MAX(id) FROM pets";
$result_2 = $db->query($query_2);

$row = $result_2->fetch_assoc();
$pet_id = $row['MAX(id)'];

$query_3 = "SELECT account_id FROM member WHERE pet_id = $id";
$result_3 = $db->query($query_3);

$row_2 = $result_3->fetch_assoc();
$account_id = $row_2['account_id'];

$query_4 = "UPDATE pets SET account_id = $account_id WHERE id = $id";
$result_4 = $db->query($query_4);

?>

 

Any ideas?

[fenway]

Look up mysql_insert_id().

[DavidAM]

  Quote

$query_2 = "SELECT MAX(id) FROM pets";
$result_2 = $db->query($query_2);

$row = $result_2->fetch_assoc();
$pet_id = $row['MAX(id)'];

 

This is a BAD idea. This will NOT work. First, if two users insert records at the same time, they will both get the same value for MAX(id). Second, you already have the ID of the record you just inserted from the call to mysqli_insert_id(), so this is not even necessary.

 

  Quote

$query_3 = "SELECT account_id FROM member WHERE pet_id = $id";
$result_3 = $db->query($query_3);

$row_2 = $result_3->fetch_assoc();
$account_id = $row_2['account_id'];

 

This will not work. You just created the record with pet_id = $id, so the value will most likely not be in the table. If it is already there, you have bigger problems.

 

Why don't you just get the current user's account_id and insert it with the original INSERT?

[eMonk]

I can't use mysqli_insert_id because I have 2 tables to have auto increment so I'm trying to update the pets table with the updated account id data. How can this be done?

 

$query_1 = "INSERT INTO pets VALUES
  ('', '', dog)";
$result_1 = $db->query($query_1);

 

The second value is left blank because it's auto incremented in the member table and where account_id goes.

[fenway]

  Quote

I can't use mysqli_insert_id because I have 2 tables to have auto increment so I'm trying to update the pets table with the updated account id data.

That makes no sense.

[DavidAM]

You can use mysqli_insert_id(), you just have to use it twice. It gets the auto_increment value from the last insert statement on the connection. I'm not as familiar with mysqli so here is an example using mysql. You should be able to make the necessary adjustments.

 

$sql = "INSERT INTO pets(pet_name) VALUES('$pet_name')";
mysql_query($sql);

$pet_id = mysql_insert_id();

$sql = "INSERT INTO members(member_name, pet_id) 
VALUES('$member_name', $pet_id)";
mysql_query($sql);

$member_id = mysql_insert_id();

$sql = "UPDATE pets SET member_id = $member_id WHERE pet_id = $pet_id";
mysql_query($sql);

 

[eMonk]

That logic makes sense DavidAM. I'll try it in a few hours when I wakeup and report back. Thanks!

[eMonk]

Update: That worked DavidAM. I couldn't sleep so had to try it lol. Thanks again!