Increment id on click in MySQL

Question

I have a problem to visualize the solution for the problem that I have now.

The user is allowed to insert a row in a table.

And I try to display a button (input) +1 who allow the user to increment a column (vote) in a selected row among all created.

The problem is that I don't get the thing for rely incrementation to the desired id.

Here my code :

      <form action="" method="post">

        <input type="text" name="disease">name  

        <input name="mainsubmit" type="submit" value="submit">

     </form>

 </body>
 </html>



<?php

if(isset($_POST['mainsubmit']))
{

$nameDisease = $_POST['disease'];


$req = $db->prepare('INSERT into disease(name) VALUES(:name)');

$req->execute(array('name' => $nameDisease));

}

$query = $db->query('SELECT * FROM disease');

while ($result = $query->fetch())
{
    $id = $result['id']; 
    echo $id ?>
    <form action="" method="post"> <input name="secondsubmit"            type="submit" value="+1"> </form><?php

    if(isset($_POST['secondsubmit']))
{

$db->exec("UPDATE disease SET vote = vote + 1 WHERE id = " .$id);


}

}

Logically, the code above doesn't work but I don't understand how find the solution.

In brief, i want to allow the user to increment a column in a selected row.

Thanks

Edit: Shadow, it's not my problem because your solution is used for automatically chose between INSERT or UPDATE if the line doesn't exist or exist. Me, I want allow the user to create rows and allow he to vote +1 on each of one that exist, and it will not be possible for he to insert a row from the input +1.

Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — Shadow, Mar 06 '17 at 11:51
It's not my problem because your solution is used for automatically chose between insert or update if the line doesn't exist or exist. Me, I want allow the user to create rows and allow he to vote +1 on each of one if he wants. — Lafdoma, Mar 06 '17 at 12:49
I completely disagree with you. If the row does not exists, then you can set the vote to 1 automatically in the insert. — Shadow, Mar 06 '17 at 13:24
But why set the vote to 1 automatically if the row doesn't exist ? I believe that you not understand my problem. What I try to do is similar to posts on stackoverflow, there are the posts create by users, alright. And then, there are the upvote (so vote in my case) who allow to select the best posts for the users thanks to the counter. — Lafdoma, Mar 06 '17 at 13:41
All these things were not described in your original question. If you do not describe your issue completely, then you are going to get wrong answers. — Shadow, Mar 06 '17 at 13:49
"And I try to display a button (input) +1 who allow the user to increment a column (vote) in a selected row among all created." I think that was clear. — Lafdoma, Mar 06 '17 at 13:59

score 1 · Accepted Answer · answered Mar 06 '17 at 13:25

I created code snippet similar to your code style.

You have two submit buttons so you need to separate handling of those two requests.

The $id of the item you want to update in the second submit need's to come from hidden value in form.

In order for this to work you need to create table in mysql:

create table disease (id MEDIUMINT NOT NULL AUTO_INCREMENT, name VARCHAR(20), vote INTEGER, PRIMARY KEY (id)); - for example like this

<html>
<body>
<form action="" method="post">
    <input type="text" name="disease">name
    <input name="mainsubmit" type="submit" value="submit">
 </form>
 </body>
 </html>

<?php
$db = new PDO('mysql:dbname=phpapp;host=db', 'root', 'phpapptest');
if (isset($_POST['mainsubmit'])) {
    $nameDisease = $_POST['disease'];
    $req = $db->prepare('INSERT into disease (name, vote) VALUES(:name, 0)');
    $req->bindParam(':name', $nameDisease);

    $req->execute();
    $query = $db->query('SELECT * FROM disease');

    while ($result = $query->fetch()) { ?>
        <form action="" method="post">
            <p><?php echo $result['name'] . " : " . $result['vote'];?>
                <input name="secondsubmit" type="submit" value="+1" />
                <input type="hidden" name="id" value="<?php echo $result['id'];?>" />
            </p>
        </form>
    <?php }
}

if (isset($_POST['secondsubmit'])) {
    $req = $db->prepare("UPDATE disease SET vote = vote + 1 WHERE id = " . $_POST['id']);
    $req->execute();
    $query = $db->query('SELECT * FROM disease');

    while ($result = $query->fetch()) {?>
        <form action="" method="post">
            <p><?php echo $result['name'] . " : " . $result['vote'];?>
                <input name="secondsubmit" type="submit" value="+1" />
                <input type="hidden" name="id" value="<?php echo $result['id'];?>" />
            </p>
        </form>
    <?php }
}
?>

Increment id on click in MySQL

1 Answers1