Can't connect with database.

[SimonIoa]

Hello i want to make a statement that increases a row on a table on MySql every time one visits the page. The $_GET['ID'] its ok dont waste your time with that.
This is the statement

require 'config.php';
$db = getDB();
$ID=$_GET['ID'] ;
mysqli_query($db, "UPDATE table SET count = count + 1 WHERE id=".$ID)or die(mysqli_error($db));

and on the config.php i have this to connect with db

function getDB() 
{
    $dbhost=DB_SERVER;
    $dbuser=DB_USERNAME;
    $dbpass=DB_PASSWORD;
    $dbname=DB_DATABASE;
    $dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); //Other database change PDO configuration
    $dbConnection->exec("set names utf8");
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbConnection;
}

My Current PHP version: 5.3

[cereal]

Hi,

it happens because getDB() is using PDO and in the script you are using MySQLi. Fix it and it should work.

[SimonIoa]

You mean something like this?

try{
       $db = getDB();

    $sql = "UPDATE table SET count = count + 1 WHERE id=".$ID;

    // Prepare statement
    $stmt = $db->prepare($sql);

    // execute the query
    $stmt->execute();
}
catch(PDOException $e) {
    echo $e->getMessage();
}
$db = null;        // Disconnect

doesnt work
OR THIS

try
    {
            $db = getDB();
          $sql = "UPDATE messages SET views_count = views_count + 1 WHERE id=:ID";

        // Prepare statement
        $stmt = $db->prepare($sql);
        $stmt->bindValue(":ID", $ID);

        // execute the query
        $stmt->execute();
            $db = null;        // Disconnect

    }

[SimonIoa]

OK the statement works fine. The problem was that i should have put incude_once 'config.php'; and not reguire

But can the statement be executed in a single line code? I mean like i did with mysqli?

[cereal]

In this very specific case, because $ID is expected to be an integer, you could use exec() which returns the number of affected rows by the statement or something that evaluates to boolean FALSE (if something goes wrong) but you have to properly sanitize the variable.

$ID = filter_input(INPUT_GET, 'ID', FILTER_VALIDATE_INT);

if(FALSE !== $ID)
{
    $update = $db->exec("UPDATE table SET count = count + 1 WHERE id=".$ID);

    if(FALSE === $update)
    {
         // log the error, kill the script, etc.
    }

    else
    {
        // successful update
    }
}

else
{
    // $ID is not valid
}

In case of strings, instead, the filter function is not enough, because sending something like 0 OR 1=1 would be valid and expose your query to an SQL injection attack.

I prefer to have few extra lines of code and go with prepared statements.

Besides, in PDO you can send the values as an array, in the execute() method:

$stmt->execute([':ID' => $ID]);