Image Click Counter, data base Row update, and Order by hits

Question

Im trying to make an image click counter, so that when some one click on an image it updates hit column by 1.

Im having trouble with the syntax, as far as getting the row number that needs to be updated.

Later i want to use this hit column to sort the order that the images are displayed on the site.

If you feel like throwing me a bone and telling how to do that as well it would be much appreciated :)

connect.php

<?php
$servername = "*******";
$username = "********";
$password = "********";
$dbname = "********";

$conn = new mysqli($servername, $username, $password,$dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
//echo "Connected successfully";
?>

art.php

     <?php
    require 'connect.php';

    $sql = "SELECT * FROM art";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo 

            "<div class='art'>
            <a href='img/".$row["name"].".jpg ' 
//part that I need help with
onclick='
<?php
include 'update_hits.php';
update_hit('$row');
?>'

 target='_blank'>
                <img src='img/".$row["name"]."_tnail.jpg' alt='".$row["name"]."' title='".$row["name"]." • ".$row["year"]." • ".$row["type"]."'/>
            </a>
            <p>
                &nbsp;&nbsp;&nbsp;&nbsp;".$row["name"]." &nbsp; • &nbsp; ".$row["year"]."&nbsp; • &nbsp;".$row["type"]."
            </p>
        </div>"
            ;
        }
    } else {
        echo "0 results";
    }
    $conn->close();
    ?>

update_hits.php

<?php
require 'connect.php'; 
 function update_hit($row){

        $query = "SELECT 'hits' FROM 'art'";
        if(@$query_run = mysql_query($query);){     
            $count = mysql_result($query_run, '$row' , 'hits');
            $count_inc = $count + 1;        
            $query_update = "UPDATE 'art' SET 'hits' = '$count_inc'";
            @$query_update_run =mysql_query($query_update);     
            }       
        }           
?>

Your code is failing you for 2 reasons and who knows which API you're really using to connect with. — Funk Forty Niner, May 08 '16 at 18:32
It seems like you're mixing oo mysqli with procedural mysql. You should stick with the former. — Jeff Puckett, May 08 '16 at 18:36
possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) and [Can I mix MySQL APIs in PHP?](http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — Funk Forty Niner, May 08 '16 at 18:38
Fred -ii- What code can i add so that you can get all the info to help? — Dylan, May 08 '16 at 18:46
Honestly looking at this code? Throw it out. Start over. It's pretty much unsalvageable. — Niet the Dark Absol, May 08 '16 at 18:55
Then Please help me and let me know where I can look for some help. or learn how to do it the correct way letting me its unsalvageable doesn't teach me any thing. — Dylan, May 08 '16 at 19:01
while clicking on the image are you opening that image in a new page or using javascript to open the image in a popup? — abhiklpm, May 08 '16 at 19:01
this link should solve your issue, in the PHP part you will need to make necessary changes to increment hit in database, in the example it writes to a file. [link] (http://stackoverflow.com/questions/1123132/how-can-i-use-php-and-javascript-to-make-an-image-clickable-and-increment-a-cou) — abhiklpm, May 08 '16 at 19:07
In art.php you will need to remove the onclick and change the href to something like href='displayImage.php?image=img/".$row["name"].".jpg'. Then in displayImage.php page you will need to get the image ($image = $_GET['image'];) echo that image. Do necessary code to increment database. In the above link you can ignore the javascript part as you are opening in new page. — abhiklpm, May 08 '16 at 19:13
I looked at it and still makes no don't understand how that works with my code as they storing the counts in a .txt not a database. here is the website all i really want is to put the most clicked ones at the top. [link](http://jarrettonions.co.za) — Dylan, May 08 '16 at 19:14
@NiettheDarkAbsol Seems like I've fallen onto a few deaf ears, *sigh*. Not just the "OP". — Funk Forty Niner, May 08 '16 at 19:27

abhiklpm · Answer 1 · 2016-05-09T17:10:51.567

This is only the logic, your code is not perfect it needs many validation and additional checks. I have not tested this code check for syntax errors. Its only for you to get the logic on how to do this.

modified art.php

<?php
    require 'connect.php';
    $sql = "SELECT * FROM art";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo 
                "<div class='art'>
    <a href='displayImg.php?image=".$row["name"]."' target='_blank'>
    <img src='img/".$row["name"]."_tnail.jpg' alt='".$row["name"]."' title='".$row["name"]." • ".$row["year"]." • ".$row["type"]."'/>
    </a>
    <p>
    &nbsp;&nbsp;&nbsp;&nbsp;".$row["name"]." &nbsp; • &nbsp; ".$row["year"]."&nbsp; • &nbsp;".$row["type"]."
    </p>
    </div>"
                ;
        }
    } else {
        echo "0 results";
    }
    $conn->close();
    ?>

new displayImg.php

<?php
require 'connect.php'; 
$image = $_GET['image'];
//need to check whether file exists also
if(!empty($image)){
    echo 'img/'.$image.'jpg'; // prefix full path if needed
    $sql = "UPDATE 'art' SET hits=hits+1 where name = ".$image;
    $result = $conn->query($sql);
}       
?>

I get these 2 warnings when trying to run it Warning: mysql_query(): Access denied for user ''@'localhost' (using password: NO) in /home/jarresmt/public_html/displayImg.php on line 7 Warning: mysql_query(): A link to the server could not be established in /home/jarresmt/public_html/displayImg.php on line 7 — Dylan, May 08 '16 at 19:59

Dylan · Answer 2 · 2016-05-08T21:25:11.213

0

Finally got it to work

     <?php
require 'connect.php'; 

$image = $_GET['image'];
//need to check whether file exists also
if(!empty($image)){
    echo "<img src='".$image.".jpg'>"; // prefix full path if needed
    $imagename = explode("/", $image); 

    $sql = "UPDATE `art` SET `hits`=`hits` +1 WHERE `name` = '".$imagename[1]."'";


     if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();

}       
?>

edited May 08 '16 at 21:25

answered May 08 '16 at 20:55

Dylan

85
2
9

Instead of accepting my answer, you added another one? - @Dylan – abhiklpm May 09 '16 at 17:13

Image Click Counter, data base Row update, and Order by hits

2 Answers2