How to avoid the confusion of querys executed at the same time with Php and Mysql?

Question

How to avoid the confusion of querys executed at the same time with Php and Mysql?

Navigation

#1 by (3 votes)

1

I have this php function (setBet) that registers the bets in my system based on BetFair, the fact is that I am wanting to expand the business and probably some bettor will make one bet at the same time as another. The function below First register the bet header in the "bet" table and with the sleep function (1) I wait 1 second to insert the query shortly after, with the function SELECT MAX (id) I select the id that I just registered to use in the foreignkey of the "bet_matches" table which registers which match the customer bet, and his bet id.

MY PROBLEM: By using the sleep (1) function and MAX (id) I'm sure I'll have some problem in the future, blending bets or something like that .. so how can I improve this function?

public function setBet($punter_name, $punter_phone, $value, $colab_id, $array_match_and_bet)
{
    // Cadastrar Cabeçalho e Pegar ID Aposta
    $query =    "SET TIME_ZONE = '-03:00';";
    $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 = mysqli_multi_query(Conexao::conectar(), $query) or die(Conexao::conectar());

    // Se Dados For Verdadeiro, Cabeçalho cadastrado
    if ($data){
        // Espera um segundo e Pega o id que acabamos de gerar
        sleep(1);
        $query = "SELECT MAX(id) FROM bet";
        $data = mysqli_query(Conexao::conectar(), $query);
        $line = mysqli_fetch_assoc($data);
        $last_id = $line["MAX(id)"];
        // Hora de cadastrar as partidas selecionadas Bitch
        sort($array_match_and_bet);
        $count = count($array_match_and_bet);
        for ($i=0; $i < $count ; $i++) { 
            $match_and_bet = explode('-', $array_match_and_bet[$i]);
            // var_dump($match_and_bet);
            $id_match = $match_and_bet[0];
            $type_bet = $match_and_bet[1];
            $odd_at_time = $match_and_bet[2];
            // Vetor para ser utilizado no calculo do premio possivel
            $odds[] = $match_and_bet[2];
            $query = "INSERT INTO 'bet_matches' SET match_id = $id_match, type_bet = '$type_bet', bet_id = $last_id, datetime_add = now(), odd_at_time = $odd_at_time";
            // var_dump($query);
            $data = mysqli_query(Conexao::conectar(), $query);
            if (!$data) {
                exit();
            }
        }
        // Calcula a cotacao
        $possible_prize = Bet::calcBetPossiblePrize($odds) * $value;
        // Guarda o Premio Possivel
        Bet::setPossiblePrize($possible_prize, $last_id);

        if ($data) {
                header("Location: ../bet_details.php?bet_id=".$last_id."&msg=betMakeSuccess");
            }
            else{
                header("Location: ../bet_details.php?bet_id=".$last_id."&msg=betMakeDuplicate");
            }

    }
    else
        return 0;   
}

EDIT: FOLLOW MY FUNCTION Connection: connect ()

public static function conectar()
{
    $con = mysqli_connect('localhost', 'root', 'vagrant', 'boleiros') or trigger_error(mysql_error(),E_USER_ERROR);

    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        //you need to exit the script, if there is an error
        exit();
    }
    return $con;
    // var_dump($con);
}

php mysql

asked by anonymous 17.06.2016 / 19:26

1 answer

MD5 is good enough? Join repeats records? [closed]

score 3 · Accepted Answer

It is very important that you save the connection to a variable so that it is not always connecting. I do not know how you have your connection but try this:

....
$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);
    ....