Embedding PHP variable into Sqlite statement

Question

I have been struggling with this for hours. I am pretty new to Sqlite and have been trying to write a prepared statement, everything works up until I try to get my variable in.

The following works as does a direct SELECT - entering the string straight in the statement.

    <?php

$myusername=$_POST['user'];
echo $myusername;
$mypassword=$_POST['pass'];

$db = new SQLite3('capT.db', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);
$statement = $db->prepare('SELECT * FROM Users WHERE Name = ?');
$statement->bindValue(1, 'aName');
$result = $statement->execute();

echo("Get the 1st row as an associative array:\n");
print_r($result->fetchArray(SQLITE3_ASSOC));
echo("\n");

$result->finalize();

However as soon as I bring a variable in to play with bindParam , I do not get results. like so:

<?php

$myusername=$_POST['user'];
echo $myusername;
$mypassword=$_POST['pass'];

$db = new SQLite3('capT.db', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);
$statement = $db->prepare('SELECT * FROM Users WHERE Name = ?');
$statement->bindParam('s', $myusername);
$result = $statement->execute();

echo("Get the 1st row as an associative array:\n");
print_r($result->fetchArray(SQLITE3_ASSOC));
echo("\n");


$result->finalize();

I have checked the $_POST['user'] variable and it is pulling through. Any help that solves my issue or shows how I might debug would be most welcome.

I am working in PHP Storm just in case there is something specific to that IDE. I have also tried with Name = :Name and binding with that.

Thanks in advance.

try the form as described on the home page php.net/manual/en/sqlite3.prepare.php — nbk
– nbk, Commented Mar 2, 2024 at 2:00
I have already tried this method and the bindParam() type, which is the one I think I need as I am putting in a variable. If I take this example, change the database name to mine and use as is, it does not work. I get a 500 error. I think one of my issues is inadequate debugging. Whenever I have used Mysqli, for example, I always managed to get the error messages some way, even if I went to the log. I don't know where to look here to get the specific issue. — fcreav
– fcreav, Commented Mar 2, 2024 at 10:22
Why are you using 's' when you have no such parameter? Also see stackoverflow.com/a/5077108/14868997 — Charlieface
– Charlieface, Commented Mar 2, 2024 at 20:34
Hi link really helped and solved the issue for me. The examples I found did not demo with a variable like it did on the above link. Thanks. — fcreav
– fcreav, Commented Mar 3, 2024 at 0:18

fcreav · Accepted Answer · 2024-03-03 00:25:30Z

With guidance from Charlieface it's solved. The link he/she provided gives a clear example of how to insert variable.

Final working code:

$myusername = $_POST['user'];
$mypassword=$_POST['pass'];

$db = new SQLite3('capT.db', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);
$statement = $db->prepare('SELECT * FROM Users WHERE Name = :Name');
$statement->bindValue(':Name', $myusername);
$result = $statement->execute();
echo("Get the 1st row as an associative array:\n");
print_r($result->fetchArray(SQLITE3_ASSOC));
echo("\n");
$result->finalize();

I was binding values incorrectly.

Stack Exchange Network

Embedding PHP variable into Sqlite statement

1 Answer 1

Hot Network Questions

Embedding PHP variable into Sqlite statement

1 Answer 1

Related

Hot Network Questions