Update database table without HTML form tag and get a specific value from MySQL table and display

Question

I currently have a database table with 2 columns: Photo No. & Vote Count. I would like to increase the vote count number in the database when the respective photo is pressed on the html page. Also, I did not want to go to another page as there's a overlay content when the photo is pressed. (thus there's no action in the form tags.

HTML

<form action="" method="post">
        <input id="box1" type="submit">
        <input id="result1" name="result1" type="text" value="0"/>
</form>

PHP

<?php
$link = mysqli_connect("localhost", "root", "", "votingcount");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

 $sql = "UPDATE vote_result SET vote_count = vote_count + 1 WHERE photo_no=1";
if(mysqli_query($link, $sql)){
echo "Records added successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// close connection
mysqli_close($link);
?>

to run php

$(function() {
        $('form').bind('submit', function(){
            $.ajax({
                type: 'post',
                url: "insert.php",
                data: $("form").serialize(),
                success: function() {
                }
            });
            return false;
        });
    });

What I've done was to only allow the first photo vount count to be updated in the database. But I have 7 other photos which I would also like to update the respective vote count when the respective photos are clicked.

ajax will do each image from db must a have an id then listen when the image is clicked then update the votes of that image ID — Masivuye Cokile, Feb 07 '17 at 11:16
@shibon Hi, I've updated the image of the html. When I click on the photo 1 or 2, I would want the respective vote count to be updated. — Kayden, Feb 07 '17 at 11:30

score 0 · Answer 1 · edited May 23 '17 at 12:01

Does this could fit your needs ?

var votes = [0, 0, 0];
$(document).ready(function() {
  $('.photoButton').click(function() {
    // Here, make you ajax call using photo_id
    ++votes[$(this).parent().attr('photo_id')];
    $(this).prev().text(votes[$(this).parent().attr('photo_id')]);
  });
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="#photo-gallery">
  <div class="photo" photo_id="0">
    <img src="http://lmsotfy.com/so.png" style="width:100px" />
    <span class="vote">0</span> <span class="photoButton">+</span>
  </div>
  <div class="photo" photo_id="1">
    <img src="http://zelcs.com/wp-content/uploads/2013/02/stackoverflow-logo-dumpster.jpg" style="width:100px" />
    <span class="vote">0</span> <span class="photoButton">+</span>
  </div>
  <div class="photo" photo_id="2">
    <img src="https://www.stackoverflowbusiness.com/hubfs/logo-so-color.png?t=1484933198957" style="width:100px" />
    <span class="vote">0</span> <span class="photoButton">+</span>
  </div>
</div>

Using ajax (this is the idea, it's not properly tested) :

$(function() {
    $('.photoButton').click(function(){
        $.ajax({
            type: 'post',
            url: "insert.php",
            data: {
                'photo_id' : $(this).parent().attr('photo_id')
            },
            success: function(data) {
                $(this).prev().text(data['vote']);
            }
        });
        return false;
    });
});

And PHP :

try {
    $sql->prepare("UPDATE vote_result SET vote_count = vote_count + 1 WHERE photo_no=:photo_id")
    ->bind('photo_id', $_GET['photo_id'])
    ->execute();
    echo json_encode(array('vote' => $sql->query('SELECT vote_count FROM vote_result WHERE photo_no=:photo_id')->bind('photo_id', $_GET['photo_id'])->fetch()->vote_count));
} catch(SqlException $e){
    // Deal with SQL errors or wrong IDs or ...
    // depending on how your sql wrapper works
}

As I say, this is minimalist and just the idea of making this to work properly.

EDIT : You shouldn't use mysqli_* functions directely, prefer the use of PDO or any other SQL wrapper and use prepare and/or binding queries to prevent your code form SQL injection

score 0 · Answer 2 · answered Feb 08 '17 at 02:46

at the time of image click we need to do update using ajax

data-id needs to unique for each image

the id of hidden field and the image data-id needs to be same

<div>
 <img src="path/to/image" data-id="vote1"  class="vote"/>
 <input type="hidden" id="vote1" value="0">
</div>
 <div>
 <img src="path/to/image" data-id="vote2"  class="vote"/>
 <input type="hidden" id="vote2" value="1">
</div>

and in juery

$(documnet).on('click', '.vote', function(){
 var imageId = $(this).attr("data-id");
 var voteCnt = $("#"+imageId).val()+1;
   $.post("process.php", { Photo:imageId, vote:voteCnt }
   function(data){
        //please use success or failure function
        $("#"+imageId).val(voteCnt);
        alert(data);
    });
});

Update database table without HTML form tag and get a specific value from MySQL table and display

2 Answers2