Update photo counter instantly

Question

I’m doing a website, where I display products that are registered in a database. In the table, there is a field that I call relevancy and I wanted when someone clicked on the image to increase this field. I have no idea how to do this, and I wanted some tips. I tried to put in the image onclick a php function that update this field, but I know it doesn’t work to do so the way onclick works.

Although I knew it wouldn’t work, follow the code below to understand what I wanted to happen:

while($dados=mysql_fetch_array($sql)){
                    $aux=$dados['relevancia']+1;
                    $res= "UPDATE cwfolheados.produtos SET relevancia=".$aux." WHERE codigo=".$dados['codigo']."";
                echo "<li><a href='".$dados['foto']."' onclick='mysql_query($res)' style=' margin-left:0' data-lightbox='image-1' data-title=".$aux2.">
            <img  style=' width:200px; height:150px;  ' border='0' alt='image 02' src='".$dados['foto']."' />
            <figcaption>".$dados['nome']." - ".$dados['codigo']."</figcaption></a></li>     ";}

Answer 1

You can assemble a code structure using the jQuery method on and ajax:

HTML:

<img id="img-id" class="img-class" src="img/imagem.jpg" alt="Minha imagem" />

Javascript:

$('.img-class').on('click', 'img', function (e) {
    var id = this.id;

    $.ajax({
        url: 'ajax.php',
        type: 'post',
        data: id
    }).done(function(data) {
        if ( data == 'relevancia incrementada' ) {
            alert('Sucesso.');
        } else {
            alert(data);
        }
    });
});

PHP:

<?php
    if ( !isset( $_POST["id"] || empty($_POST["id"]) ) ) {
        echo "id nao setado ou nulo";
        exit();
    }

    $id = $_POST["id"];

    try {
        $conn = new PDO("mysql:host=localhost;dbname=database", "root", "");
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $this->conn->prepare("UPDATE tabela SET relevancia = relevancia + 1 WHERE codigo = :id");
        $stmt->bindParam( ":id", $id );

        $stmt->execute();
        $result = $stmt->rowCount();

        if ( $result > 0 ) {
            return "relevancia incrementada";
        }
    } catch(PDOException $e) {
        return "erro: " . $e->getMessage();
    }

• _{Note that the id of each image will be previously recovered from the database.}
• _{I used PDO in the example, but also works with the functions mysql_ although it is highly recommended to leave such functions.}

Answer 2

As I explained in another answer, the attribute onclick cannot be used this way.

All you need to do is run the update each time the script showing the product is loaded. This way at each view the counter will be incremented by 1.

Ex.:

mysql_query("UPDATE cwfolheados.produtos SET relevancia=relevancia+1 WHERE codigo=".$dados['codigo'].");

I used it as your base query and I only modified the column value sum (which can be done directly in SQL). I also assumed that the product code is in $dados['codigo'] as you reported.

Another thing, there’s no need to use while when you know that your query will return only one result (only one product in this case). You can use only $dados = mysql_fetch_array($sql); that the effect will be the same.