How do I do a SELECT then an INSERT [closed]

-1

How do I do a SELECT and if it returns more than 1 record does the UPDATE and if it returns 0 records does the INSERT?

Look how I've done here but it's not working:

if(isset($_POST['submit'])){
    $query = $conexao->prepare("SELECT id_mark, id_user FROM tb_comment WHERE id_mark=:post_id AND id_user=:idLogged");
    $query->bindParam(':post_id', $post_id, PDO::PARAM_INT);
    $query->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
    $query->execute();

    if($result->rowCount() >= 1){
        echo '<div class="alert alert-danger">
        <strong>Erro!</strong> Não foi possível cadastrar sua avaliação.
        </div>';
    }
    if($result->rowCount() == 0){

        $comment   = trim(strip_tags($_POST['comment']));

        $insert = "INSERT into tb_comment (id_mark, id_user, comment, up_c, down_c, rate, active) VALUES (:post_id, :idLogged, :comment, 0, 0, :star, NOW())";

            $result = $conexao->prepare($insert);
            $result->bindParam(':post_id', $post_id, PDO::PARAM_INT);
            $result->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
            $result->bindParam(':star', $star, PDO::PARAM_INT);
            $result->bindParam(':comment', $comment, PDO::PARAM_STR);
            $result->execute();
            if($result->rowCount() == 0){
                echo '<div class="alert alert-success" role="alert">
                <strong>Sucesso!</strong> avaliação cadastrada.
                </div>';
            }
            else
            {
                echo '<div class="alert alert-danger">
                <strong>Erro ao cadastrar!</strong> Não foi possível cadastrar a avaliação.
                </div>';
            }
    }
}//if
    
asked by anonymous 15.12.2015 / 21:19

4 answers

1

Your intention with this select to be to avoid duplicate records in the database, an alternative is to mark some column (email) as unique key, so a repeated email will not be registered.

Via php check if the query returns something by getting the record with fetch() , if it has something make an update, otherwise an insert.

if(isset($_POST['submit'])){
    $comment   = trim(strip_tags($_POST['comment']));

    $query = $conexao->prepare("SELECT id_mark, id_user FROM tb_comment WHERE id_mark=:post_id AND id_user=:idLogged");
    $query->bindParam(':post_id', $post_id, PDO::PARAM_INT);
    $query->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
    $query->execute();
    $coment = $query->fetch(PDO::FETCH_ASSOC);

if(!empty($coment)){
    $insert = "UPDATE tb_comment SET comment=:comment, rate=:star, active=NOW() WHERE id_mark=:post_id AND id_user=:idLogged";
    $result = $conexao->prepare($insert);
    $result->bindParam(':post_id', $post_id, PDO::PARAM_INT);
    $result->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
    $result->bindParam(':star', $star, PDO::PARAM_INT);
    $result->bindParam(':comment', $comment, PDO::PARAM_STR);
    if($result->execute()){
        echo 'sucesso';
    }
}else{
    $insert = "INSERT into tb_comment (id_mark, id_user, comment, up_c, down_c, rate, active) VALUES (:post_id, :idLogged, :comment, 0, 0, :star, NOW())";
    $result = $conexao->prepare($insert);
    $result->bindParam(':post_id', $post_id, PDO::PARAM_INT);
    $result->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
    $result->bindParam(':star', $star, PDO::PARAM_INT);
    $result->bindParam(':comment', $comment, PDO::PARAM_STR);
    if(!$result->execute()){
        echo 'error:';
    }else{
        echo 'sucesso';
    }
}
} //if
    
15.12.2015 / 21:42
1

You are using the $result object, but at what time is it instantiated? I believe you should use the rowCount() method of the $query object. For more information about the rowCount() method, see the documentation documentation.

    
15.12.2015 / 21:33
1

This simplifies, without risk of race condition .

$comment   = trim(strip_tags($_POST['comment']));
$insert = "INSERT into tb_comment (id_mark, id_user, comment, up_c, down_c, rate, active) VALUES (:post_id, :idLogged, :comment, 0, 0, :star, NOW())";
$result = $conexao->prepare($insert);
$result->bindParam(':post_id', $post_id, PDO::PARAM_INT);
$result->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
$result->bindParam(':star', $star, PDO::PARAM_INT);
$result->bindParam(':comment', $comment, PDO::PARAM_STR);
if($result->execute()){
    echo '<div class="alert alert-success" role="alert">
    <strong>Sucesso!</strong> avaliação cadastrada.
    </div>';
} else {
    echo '<div class="alert alert-danger">
    <strong>Erro ao cadastrar!</strong> Não foi possível cadastrar a avaliação.
    </div>';
}
    
15.12.2015 / 21:34
0

Correction hint

if(isset($_POST['submit'])){
    $query = $conexao->prepare("SELECT id_mark, id_user FROM tb_comment WHERE id_mark=:post_id AND id_user=:idLogged");
    $query->bindParam(':post_id', $post_id, PDO::PARAM_INT);
    $query->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
    $query->execute();

       if($result->rowCount() < 1){

            $comment   = trim(strip_tags($_POST['comment']));

            $insert = "INSERT into tb_comment (id_mark, id_user, comment, up_c, down_c, rate, active) VALUES (:post_id, :idLogged, :comment, 0, 0, :star, NOW())";

                $result = $conexao->prepare($insert);
                $result->bindParam(':post_id', $post_id, PDO::PARAM_INT);
                $result->bindParam(':idLogged', $idLogged, PDO::PARAM_INT);
                $result->bindParam(':star', $star, PDO::PARAM_INT);
                $result->bindParam(':comment', $comment, PDO::PARAM_STR);
                $result->execute();
                if($result->rowCount()){
                    echo '<div class="alert alert-success" role="alert">
                    <strong>Sucesso!</strong> avaliação cadastrada.
                    </div>';
                }
                else
                {
                    echo '<div class="alert alert-danger">
                    <strong>Erro ao cadastrar!</strong> Não foi possível cadastrar a avaliação.
                    </div>';
                }
        }
}

I've removed all this stretch that does not seem to make sense:

if($result->rowCount() >= 1){
    echo '<div class="alert alert-danger">
    <strong>Erro!</strong> Não foi possível cadastrar sua avaliação.
    </div>';
}
if($result->rowCount() == 0){

and I switched to if($result->rowCount() < 1){

I changed if($result->rowCount() == 0){ by if($result->rowCount()){

    
15.12.2015 / 21:33