Checking if a record exists with PHP and sqlite

Question

I'm totally new to programming (normally I'm a sysadmin) but now I have to do a project where you can register and logon, with PHP and SQLite (there's no alternative).
I need to get a statement where I can do a query:

is there a user with the name from $_POST['username']

with SQLite. I just don't get it... I found some articles but none of them really helped me.

This is my code:

$db = new PDO('sqlite:mysqlitedb.db');

$sql_create_table_users = 'CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY,
    user_name TEXT,
    user_password TEXT
    )';
$db->execute($sql_create_table_users);

$username = $_POST['username'];

$sql_checkuserexist = 'SELECT * FROM users WHERE user_name = :user_name';

$stmt = $db->prepare($sql_checkuserexist);
$stmt->execute(array(':user_name'=>$username));
$result = $stmt->fetchAll();

if (count($result) > 0) {
    echo 'Exists';
} else {
    echo 'Does not exist';
}

But here I get this error:

Fatal error: Call to undefined method SQLite3Stmt::exec()

This error shows to the line where I have

$stmt->execute(array(':user_name'=>$username));

Does anyone know why? My table is called "users" and has got 3 rows: user_id, user_name and user_password.

@Kostas: This is my current code you gave me (I'm only working with this code atm):

try {
//Make your connection handler to your database
$db = new PDO('sqlite:mysqlitedb.db');
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql_create_table_users = 'CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY,
    user_name TEXT,
    user_password TEXT
    )';
$stmt->execute($sql_create_table_users);

$sql = "SELECT * FROM users WHERE user_name = :username";
//Prepared statements so no SQL Injection occurs
$stmt = $db->prepare($sql);
//Execute your query
$stmt->exec(array(':username'=>$_POST['username']));
$result = $stmt->fetchAll();
if (count($result) > 0) {
    echo 'Exists';
} else {
    echo 'Does not exist';
}

} catch(PDOException $e) {
echo $e->getMessage();
die();
}

Also for executing prepared statements use 'execute' function. — Kostas Mitsarakis, Nov 21 '15 at 16:00
Also you have $db = new DBO not PDO. Fix this using the correct PDO and try again. — Kostas Mitsarakis, Nov 21 '15 at 16:03
sqlite is okay with my server. insert and create table etc. did work, but this statement doesn't... — MichelleH, Nov 21 '15 at 16:04
yes I just typed it in here wrongly, in my code it's correct. — MichelleH, Nov 21 '15 at 16:05
There's an error: Fatal error: Call to undefined method PDO::execute() On this line where there is $db->execute($sql_create_table_users); — MichelleH, Nov 21 '15 at 16:13
Also change from: $db->execute($sql_create_table_users); TO $db->exec($sql_create_table_users); Exec is for direct sql queries and execute for prepared statements. — Kostas Mitsarakis, Nov 21 '15 at 16:14
Oh, NOT $db->execute($sql_create_table_users); BUT $stmt->execute($sql_create_table_users); — Kostas Mitsarakis, Nov 21 '15 at 16:15
You call a plain sql query with database handler but when you have to call a prepared statement you have to call it via $stmt that previously had created. — Kostas Mitsarakis, Nov 21 '15 at 16:19
Oh man, there are 2 new errors now: Notice: Undefined variable: stmt AND Fatal error: Call to a member function execute() on null both on the same line as the error before ($stmt->execute($sql_create_table_users); — MichelleH, Nov 21 '15 at 16:21
Just copy & paste the EXACT code you currently use so I will be able to fix it. — Kostas Mitsarakis, Nov 21 '15 at 16:21
Sure! Just one more question: I've added this code: $sql_select_users = 'SELECT * FROM users'; $result = $db->query($sql_select_users); while ($row = $result->fetchArray()) { print $row['user_id'] . '
'; print $row['user_name'] . '
'; print $row['user_password'] . '
'; } But there's a problem with the line where i do the while () : Fatal error: Call to undefined method PDOStatement::fetchArray() Do you know why? — MichelleH, Nov 21 '15 at 16:47
Yes, replace with the following: $stmt = $db->query($query); $result = $stmt->fetchAll(); — Kostas Mitsarakis, Nov 21 '15 at 16:53
Also check this: http://stackoverflow.com/questions/4700623/pdos-query-vs-execute — Kostas Mitsarakis, Nov 21 '15 at 16:54
Oh my god, it still doesn't work... I'm just feeling so dumb! My code for getting all user's now: $query = 'SELECT * FROM users'; $stmt = $db->query($query); $result = $stmt->fetchAll(); while ($row = $result->fetchArray()) { print $row['user_id'] . '
'; print $row['user_name'] . '
'; print $row['user_password'] . '
'; } - But what's wrong? — MichelleH, Nov 21 '15 at 16:59
Replace while loop with this: foreach($result as $row) { print $row['user_id'] . '
'; print $row['user_name'] . '
'; print $row['user_password'] . '
'; } — Kostas Mitsarakis, Nov 21 '15 at 17:02

Kostas Mitsarakis · Accepted Answer · 2015-11-21T16:27:17.300

1

You can try the following code. You can find additional info here.

try {
    //Make your connection handler to your database
    $db = new PDO('sqlite:mysqlitedb.db');
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $sql_create_table_users = '
        CREATE TABLE IF NOT EXISTS users (
            user_id INTEGER PRIMARY KEY,
            user_name TEXT,
            user_password TEXT
        )';
    $db->exec($sql_create_table_users);

    $username = '';
    if (isset($_POST['username'])) {
        $username = $_POST['username'];
    }

    $sql = "SELECT * FROM users WHERE user_name = :username";
    $stmt = $db->prepare($sql);
    $stmt->execute(array(':username'=>$username));

    $result = $stmt->fetchAll();

    if (count($result) > 0) {
        echo 'Exists';
    } else {
        echo 'Does not exist';
    }

} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

edited Nov 21 '15 at 16:27

answered Nov 21 '15 at 15:26

Kostas Mitsarakis

4,657
3
22
35

Thanks a lot! Is there really no easier way to do it? – MichelleH Nov 21 '15 at 15:30
PDO and prepared statements is the best way as far as I know. It avoids SQL Injection and you will have code consistency. – Kostas Mitsarakis Nov 21 '15 at 15:32
I've done this code: $username = $_POST['username']; $sql_checkuserexist = 'SELECT * FROM users WHERE user_name = "' .$username. '"'; $stmt = $db->prepare($sql_checkuserexist); $stmt->exec(array(':user_name'=>$username)); $result = $stmt->fetchAll(); if (count($result) > 0) { echo 'Exists'; } else { echo 'Does not exist'; } my table is called "users" with 3 rows: user_id, user_name and user_password. I got this error: Call to undefined method SQLite3Stmt::exec() (this is where there is $stmt->exec(array ... Can you see my error? – MichelleH Nov 21 '15 at 15:43
First: Change to this: 'SELECT * FROM users WHERE user_name = :user_name' – Kostas Mitsarakis Nov 21 '15 at 15:49
Then change also to this: $stmt->execute(array(':user_name'=>$username)); – Kostas Mitsarakis Nov 21 '15 at 15:49
The above should fix the errors. – Kostas Mitsarakis Nov 21 '15 at 15:50
Hm, doesn't work either... – MichelleH Nov 21 '15 at 15:52
Have you made the connection to the database? Where do you create variable $db? – Kostas Mitsarakis Nov 21 '15 at 15:52
you can see my code in the top! I've added all things you need, I guess – MichelleH Nov 21 '15 at 15:56

Checking if a record exists with PHP and sqlite

1 Answers1