How to access sqlite3-database from function in PHP

Question

I'm new to SQLite and currently trying to use it for a project. I've created a function that inserts a new row if one with the given name doesn't exist already. However, I can't seem to access the database-variable from inside the function. Nothing is happening.

My first try throws an error:

$db = new SQLite3("name.db");
$db->exec("CREATE TABLE IF NOT EXISTS warehouses (id INTEGER PRIMARY KEY, name VARCHAR(255), adjacent_l INTEGER DEFAULT null, adjacent_r INTEGER DEFAULT null, chem_a INTEGER DEFAULT 0, chem_b INTEGER DEFAULT 0, chem_c INTEGER DEFAULT 0, cap INTEGER)");

function newWH($name, $cap)
{
    $letResult = $db->query("SELECT * FROM warehouses WHERE name = " . $name);
    $letArray = (array) $letResult;
    if (empty($letArray)) {
        $db->query("INSERT INTO warehouses (name, cap) VALUES (" . $name . ", " . $cap . ")");
    }
}

newWH("wh1", 10);

I tried passing the db-variable as a parameter. The website no longer throws an error, but no inserts are happening. (And yes, I've tried it without the if-clause :-)

$db = new SQLite3("name.db");
$db->exec("CREATE TABLE IF NOT EXISTS warehouses (id INTEGER PRIMARY KEY, name VARCHAR(255), adjacent_l INTEGER DEFAULT null, adjacent_r INTEGER DEFAULT null, chem_a INTEGER DEFAULT 0, chem_b INTEGER DEFAULT 0, chem_c INTEGER DEFAULT 0, cap INTEGER)");

function newWH($name, $cap, $letDb)
{
    $letResult = $letDb->query("SELECT * FROM warehouses WHERE name = " . $name);
    $letArray = (array) $letResult;
    if (empty($letArray)) {
        $letDb->query("INSERT INTO warehouses (name, cap) VALUES (" . $name . ", " . $cap . ")");
    }
}

newWH("wh1", 10, $db);

Thomas G · Accepted Answer · 2018-05-08T10:54:31.960

It's better to declare your $db as global in the function
your name column is VARCHAR so you have to surround its value with quotes in your SQL instructions

Thus:

$db = new SQLite3("name.db");
$db->exec("CREATE TABLE IF NOT EXISTS warehouses (id INTEGER PRIMARY KEY, name VARCHAR(255), adjacent_l INTEGER DEFAULT null, adjacent_r INTEGER DEFAULT null, chem_a INTEGER DEFAULT 0, chem_b INTEGER DEFAULT 0, chem_c INTEGER DEFAULT 0, cap INTEGER)");

function newWH($name, $cap)
{
    global $db;
    $letResult = $db->query("SELECT * FROM warehouses WHERE name = '$name'");
    $letArray = (array) $letResult;
    if (empty($letArray)) {
        $db->query("INSERT INTO warehouses (name, cap) VALUES ('$name', $cap)");
    }
}

newWH("wh1", 10);

use parametrized queries and bind variables to avoid SQL injections. THIS is a good start to learn how to use PDO with SQLite
If you don't care about 3, then you should at least escape quotes characters in name

Awesome! Thank you. 1 and 2 did the job I am using SQLite for dummy purposes, so I didn't want to bring PDO into the project, but I might — Markus O, May 08 '18 at 18:06

How to access sqlite3-database from function in PHP

1 Answers1