Updating a database with a hyperlink

ajax, javascript, mysql, php

Solution

Firstly, you need to validate input and your code is vunerable to sql injection. Check How to prevent SQL injection in PHP?

Please, don't use `mysql_*` functions in new code. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi

So with that in mind, here is a PDO script which does the exact same thing, and I realise it's way longer, but you can use it as a class if needed as this is an example only.

<?php

// create connection to database
$conn = new PDO('mysql:dbname=DATABASE_NAME;host=localhost;port=3306', USERNAME, PASSWORD);

// prepare query
$pdo = $conn->prepare("UPDATE ss_character SET location = :location WHERE id = :session_id");

// set up parameters
$params = ['location' => (int)$_POST['location'], 'session_id' => $_SESSION['id']];

// loop through the paramaters to determine the type
foreach ($params as $key => $value) {

    switch ($value) {

        case is_int($value):
            $param = PDO::PARAM_INT;
            break;

        case is_bool($value):
            $param = PDO::PARAM_BOOL;
            break;

        case is_null($value):
            $param = PDO::PARAM_NULL;
            break;

        default:
            $param = PDO::PARAM_STR;
            break;
    }

    // bind paramter to query
    $pdo->bindValue(":$key", $value, $param);
}

// execute the query
$result = $pdo->execute($params);

// echo result for ajax
echo ($result) ? true : false;

And you will want some jQuery to do you ajaxing so the page isn't forced to reload

<script>

    function updatePlayerLocation(location) {

        // ensure location is numeric or stop
        if !isNaN(location) return false;

        // update location via ajax
        $.ajax({
            url: 'http://your_url/to/php/script.php',
            type: 'POST',
            data: 'location=' + location,
            success: function(data) {
                // log result to console for error trapping purposes
                console.log(data);
            }
        });

        // stop link from being processed
        return false;

    }

</script>

The HTML would of course include jQuery, the script above and at least one link:

<a href="#" onclick="return updatePlayerLocation(0);">Location name</a><br />

Problem

Original source

Related problems

• Why shouldn't I use mysql_* functions in PHP?
• How can I prevent SQL injection in PHP?