html value and a prepared statement

Question

MySQL database consist of the following columnnames: id, store, results, and have 350 rows.

I have a button called "Shuffle". When I click that button I get 3 random rows printed from MySQL database(selectshuffle.php). Each row result have a "Select" button(getdata.php)

When I click the button "Select" on one row, the columnname "records" should update with +1.

I can update that row with +1 in the code I posted, and get the message "Updated Succesfull", but only if I set the value with a number like this:

<input type="hidden" value="333" name="id" />

But I need to update the columnname "results", on that id I click "Select" on. I thought I could do that like this: <input type="hidden" value="<?php $_POST['id']; ?>" name="id" />. With this code I still get the message "Updated Succesfull", but no row is affected.

Can anybody see what I am doing wrong?

index.php

<form action="updaterecords.php" method="post">
  <input type="hidden" value="333" name="id" />
  <button type="submit" name="selectStore" >Select</button>
  <button type="button" data-dismiss="modal">Close</button>
</form>

updaterecords.php

<?php error_reporting(E_ALL); ini_set('display_errors', 1);
if (mysqli_connect_errno()) { echo "Error: no connexion allowed : " . mysqli_connect_error($mysqli); } 
?>
    <?php
        include 'dbconnection.php';

        if(isset($_POST['selectStore'])) {
        $id = $_POST['id'];

        $stmt = $mysqli->prepare("UPDATE stores SET records = records + 1 WHERE id = ?");

        $stmt->bind_param('i', $id);

        $stmt->execute();

        $stmt->close();

        $mysqli->close();   
        if($stmt) {
            echo "Updated Succesfull";
        } else {
            echo "Failed: " .  $stmt->error;
          }
        }
    ?>

getdata.php

<?php
include 'dbconnection.php';
if(isset($_POST['post_id'])) {
    $id = $_POST['post_id'];
    $sql = "SELECT id, store, results FROM stores WHERE id = ".$id;
    $res = $mysqli->query($sql);
    if($res){
        $data = $res->fetch_assoc(); 

        echo json_encode($data);
     }else{
        echo "no results: <br>";
    echo $sql;
    }
}
?>

selectshuffle.php

 <?php
      include 'dbconnection.php';
      $sql = "SELECT id, store, results FROM stores WHERE 1=1 ORDER BY RAND ( ) LIMIT 3";

      $res = $mysqli->query($sql);

    if ($res->num_rows > 0) {
        while($row = $res->fetch_assoc()) {
            echo "Id: " . $row["id"]. "<br>" .
                 "Store: " . $row["store"]. "<br>" .
                 "Result: " . $row["results"]. "<br><br>".
                 '<div class="btn btn-warning btn-open-modal" data-id="'.$row["id"].'">See Details '.'</div><br><br>';              
        }
    } else {
    echo "There is no results";
    }
?>
<div class="modal fade" id="editBox" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal">&times;</button>
                <h4 class="modal-title"><center>Get Heading</center></h4>
            </div>
            <div class="modal-body">
                <div class="form-data"></div>
                <p>Get Description</p>
            </div>

            <div class="modal-footer msg">
              <form action="updaterecords.php" method="post">
                <input type="hidden" value="333" name="id" />
                <button type="submit" name="selectStore" >Select</button>
                <button type="button" data-dismiss="modal">Close</button>
              </form>
            </div>
        </div>
    </div>
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script>

$(document).ready(function() {
  $(".btn-open-modal").click(function() {
    var id = $(this).data("id");
    $.ajax({
      type: 'post',
      url: 'getdata.php',
      data: {
        post_id: id
      }, 
      success: function(data) {
        console.log(data);
        var jdata = JSON.parse(data);
        if (jdata) {
          console.log("is json");
          $("#editBox").modal().show();
          $("#editBox .modal-title").html(jdata.headline);
          $("#editBox .modal-body").html("Store: " + jdata.store + "<br><br>Result: " + jdata.results); //  + " " + $query $query => query

        } else {
          console.log("not valid json: " + data);
        }
      }
    });
  });
});
</script>

score 1 · Accepted Answer · answered May 06 '17 at 20:27

Give your id input an id, this will be used to set the id returned from getdata.php

<form action="page.php" method="post">
    <input type="hidden" id="store-id" value="" name="id" />
    <button type="submit" name="selectStore" >Select</button>
    <button type="button" data-dismiss="modal">Close</button>
</form>

Then in your ajax call set the value of the input.

$(".btn-open-modal").click(function() {
    var id = $(this).data("id");
    $.ajax({
        type: 'post',
        url: 'getdata.php',
        data: {
            post_id: id
        },
        success: function(data) {
            console.log(data);
            var jdata = JSON.parse(data);
            if (jdata) {
                $("#editBox").modal().show();
                $('#store-id').val(jdata.id);  // set the value from the returned data
                $("#editBox .modal-title").html(jdata.headline);
                $("#editBox .modal-body").html("Store: " + jdata.store + "<br><br>Result: " + jdata.results); //  + " " + $query $query => query

            } else {
                console.log("not valid json: " + data);
            }
        }
    });
});

html value and a prepared statement

1 Answers1