How to get LAST_INSERT_ID() from a mysqli_multi_query() function with SET and INSERT?

Asked

Viewed 1,061 times

0

this is my 'Betting Signup Method' on my betting system.

In this way, I register data in two tables, a call BET and another BET_MATCHES, the system is controlled by time, so in all entries I need to set the TIME_ZONE = '-03:00' to make sure there will be no error at the time, and right after that run the query that registers the header and generates the bet ID.

Only when I rescue the last id generated in this multi_query the code returns me = 0... what can I do ?

public function setBet($punter_name, $punter_phone, $value, $colab_id, $array_matches)
{
    // Post Header and Catch Bet ID
    $query =    "SET TIME_ZONE = '-03:00';";
    $query .= "INSERT INTO bet SET id ='NULL',
        punter_name ='$punter_name',
        punter_phone ='$punter_phone',
        value ='$value',
        colab_id ='$colab_id',
        date_time = now()";
    $data = mysqli_multi_query(Conexao::conectar(), $query) or die(Conexao::conectar());
    // For if data True, registered Header
    if ($data){
        // Get the id what we want
        $query = "SELECT LAST_INSERT_ID() FROM bet";
        $data = mysqli_query(Conexao::conectar(), $query);
        $line = mysqli_fetch_assoc($data);
        var_dump($line);
    }
    else
        echo "There was an error when registering the bet header";  
}
php mysql sql mysqli

  • You really need the multi_query()?

  • @rray accurate because of the SET TIME_ZONE = '-03:00'; before the Insert it needs to have the exact time of Brazil so that there is no error in the betting conference

  • you can play this in the server configuration if you want.

  • @rray is that I am hosting the system in a shared server which I do not have Super User power in the database, and let’s say that because it is a system that moves values in betting based on schedules, I can say that the more sure the better ?

  • 1

    PHP is not mine, but when you call "Connected::connect()" are you not creating a session with the database? If so, then your INSERT and SELECT are not in different sessions and so you don’t get the feedback? In this case, you should create the connection before INSERT and use it for SELECT as well, then close.

  • @lpacheco I will test and put here if it works ! but I liked your answer !

Show 1 more comment

1 answer

0


So the solution I found was based on the answer from @Miguel and @lpaczech

See the code

...
$conn = Conexao::conectar(); 
$query = "SET TIME_ZONE = '-03:00';";
$conn->query($query) or die('Erro na definição da timezone, tente mais tarde');
$query = "INSERT INTO bet (id, punter_name, punter_phone, value, colab_id, date_time, possible_prize) values(NULL, '$punter_name', '$punter_phone', '$value', '$colab_id', now(), 0);";
$data = $conn->query($query) or die('Erro na inserção, tente mais tarde');
$lastId = $conn->insert_id; // aqui guarda este e só este id, desta conecção que inseriu o novo dado

$query = "SELECT * FROM bet WHERE id=" .$lastId;
$data = mysqli_query($conn, $query);
...

store the connection in a variable (this will cause every query you call to run in the same mysql session) then just call one at a time, without the need for multi_query.

so you can get the id of the last Insert.

  • 1

    Do not use high box without need, do not write "reply:", By the time you click on the "Post Your Answer" button you are already making a response. Understand how constructive criticism [edited]

Browser other questions tagged php mysql sql mysqli

You are not signed in. Login or sign up in order to post.