How do I SELECT then INSERT

Asked

Viewed 582 times

-1

How do I select and return more than 1 record does the UPDATE and return 0 records does the INSERT ?

Look how I did it here only 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
php mysql sql pdo sql-insert

  • What’s the matter?

  • @bigown N appeared no PHP error, only I have no record and is showing the Error message when registering

  • There are two mistakes when registering, which one?

  • The error of 'if($result->rowCount() >= 1){'

  • 1

    does not have logic the conditional structures and also erroneous use of rowCount() in Insert.

  • 1

    The error must be SQL, have it printed to see what it is. Is it duplicate key? Anyway this logic is wrong, can provoke a racing condition. You don’t have to check anything, you have to enter, if you fail you should take a stand.

  • 1

    If select returns 2 or more records, which one is updated?

  • 1

    Sorry, I voted to close as "not clear enough" after observing ambiguities and details not well informed in the question, expressed in the comments. Describe the question better. Describe clearly what the flow rules are.

  • @Danielomine It worked already, it was Make a SELECT > INSERT or UPDATE

  • 3

    @Williamalvares is not what worked, it seems to work, it’s different. Right is something else.

  • ^.- What do you mean, look at the code down there

Show 6 more comments

4 answers

1

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

1

This resolves more simply and without risks of running 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>';
}

I put in the Github for future reference.

  • More like I’m gonna check if you don’t already have a record ?

  • You need it to be unique, right? So you have a unique key set up in this table, right? If you don’t, you’re going to have problems. With the unique key, if the die already exists there, the INSERT will fail. This is the only reliable way to verify if it already exists. Read the link answer. No use fixing a problem and leaving other assets.

  • You will check the user id, id_user that you are logged in, if you already have an error or update

  • What you will do you decide later, but have to try to insert the right way. Or you can do an operation upsert http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html. I did not answer this because the question did not mention anything about.

  • bigown is there any topic here of yours regarding this "Running condition"? I read the wiki but honestly did not understand very well kkkk

  • 2

    No, but you can try to find something in http://answall.com/search?q=condition+de+corrida and http://answall.com/search?q=race+condition. This is a mistake that about 90% of programmers make. Many even learning prefer not to care because the program will work right most of the time. And this is enough for the person. She doesn’t want the right program 100% of the time.

Show 1 more comment

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 anything getting the record with fetch(), if you have something do 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

  • Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[23000]: Integrity Constraint Violation: 1048 Column 'comment' cannot be null' in C: XAMPP htdocs pages topic.php:215 Stack trace: #0 C: XAMPP htdocs pages topic.php(215): Pdostatement->execute() #1 C: XAMPP htdocs page.php(28): include('C: XAMPP htdocs...') #2 {main} thrown in C: XAMPP htdocs pages topic.php on line 215

  • @Williamalvares, $comment is empty. In your code I don’t know where it comes from

  • comes from here $comment = Trim(strip_tags($_POST['comment']));

  • Got it, I’ll edit your post with all the code here

0

Suggestion for correction 

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 footage that doesn’t 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 traded for it if($result->rowCount() < 1){

I traded if($result->rowCount() == 0){ for if($result->rowCount()){

  • Did not work, error message did not appear and no error occurred in php and n inserted

  • 2

    seriously? And now?

  • Did not enter and gave error message

Browser other questions tagged php mysql sql pdo sql-insert

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