PHP Mysqli updating url clicks

Question

$short = $_REQUEST[ "short" ];

    $mysqli = new mysqli( DB_HOST, DB_USERNAME, DB_PASSWORD, DB );

    $clicks = $mysqli->query("SELECT clicks FROM leafy_urls WHERE short = '$short'")->fetch_object()->clicks;

    $clicks++;

    $statement = $mysqli->prepare( "UPDATE leafy_urls SET clicks=? WHERE short=?" );
    $statement->bind_param('is', $clicks, $short );

    $results =  $statement->execute();

    if( $results )
    {
        $long_url = $mysqli->query("SELECT long_url FROM leafy_urls WHERE short = '$short'")->fetch_object()->long_url;
        Header("HTTP/1.1 301 Moved Permanently");
        header("Location: " .$long_url. "");
    }
    else
    {
        //$html = "Error: Cannot find short URL";
        $html = 'Error : ('. $mysqli->errno .') '. $mysqli->error;
    }

    $mysqli->close();

This code works to the point it doesn't throw error but the number in the database does not increase. Stays at 1

score 0 · Accepted Answer · answered Sep 02 '16 at 21:22

0

Good that you use prepared statements but you should use it for all your input values, like so:

$statement = $mysqli->prepare("UPDATE leafy_urls SET clicks=clicks+1 WHERE short = ?");
$statement->bind_param( 's', $short );

Also note that you can use purely sql for incrementing a value.

answered Sep 02 '16 at 21:22

MarcHoH

340
1
6

Silly question. How would I use purely sql for incrementing a value? Adding an auto increment on update? – Blizzard Sep 02 '16 at 21:33
You do that like this: `clicks=clicks+1` as you can see in my answer. – MarcHoH Sep 02 '16 at 21:41
OK done the adjustments. Still not updating the number of clicks. I must be missing something? – Blizzard Sep 02 '16 at 22:35
Guess that the problem may lies somewhere else then, can you maybe show some more code and what is the value of $short, $result and $mysqli->error? – MarcHoH Sep 02 '16 at 22:48
I'll have to wait till I get home. I'll put as much info here as I can. – Blizzard Sep 02 '16 at 23:51
MarcHoH i updated my first post. hope thats enough info... i get no errors which doesnt help me in finding out whats going wrong – Blizzard Sep 05 '16 at 09:07
Can't find any problems in your code. You forgot the the sql variant of incrementing btw. Do you have any error reporting turned on? If not check [here] (http://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments) on how to turn them on. That will hopefully point us to the problem. – MarcHoH Sep 06 '16 at 17:54
I ended up finding out it was how I was handling the php header. Header("HTTP/1.1 301 Moved Permanently");. After first time link gets clicked. It was then bypassing my code and going straight to long url. – Blizzard Sep 06 '16 at 23:29

PHP Mysqli updating url clicks

1 Answers1