Данные, добавленные с готовым утверждением, не могут быть найдены с помощью WHERE

Данные, которые я добавляю в базу данных SQLite3 с использованием операторов prapared, не доступны для поиска с помощью WHERE:

SELECT Active FROM Users WHERE Username="john"

У меня есть демонстрация в PHP, которая добавляет данные с подготовленным и прямым заявлением, а затем пытается их найти.

У меня два вопроса:

  1. Почему это происходит?
  2. Как я могу искать данные, которые я добавляю через подготовленные заявления?

Вот скрипт PHP.

<?php

error_reporting(E_ALL);
date_default_timezone_set('Europe/Helsinki');
ini_set('default_charset', 'UTF-8');
mb_internal_encoding("UTF-8");
header('Content-Type: text/html; charset=UTF-8');

$timezone = date('Z');
$db = '';

// ---

//
// adds a user in the db with a prepared statement
//

function add_user1($name, $pass)
{
    global $timezone;
    global $db;

    $time = time();

    try
    {
        $statement = "INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES     (:Username,:Password,:Time,:Timezone,:Active);";    
        $query = $db->prepare($statement);
        $query->bindValue(':Username', $name, SQLITE3_TEXT);
        $query->bindValue(':Password', $pass, SQLITE3_TEXT);
        $query->bindValue(':Time', $time, SQLITE3_INTEGER);
        $query->bindValue(':Timezone', $timezone, SQLITE3_INTEGER);
        $query->bindValue(':Active', '1', SQLITE3_INTEGER);
        $ok = $query->execute();
    }
    catch(PDOException $exception)
    {
        echo $exception->getMessage();
    }
}

//
// adds a user in the db with a direct execution
//

function add_user2($name, $pass)
{
    global $timezone;
    global $db;

    $time = time();

    try
    {
        $db->exec('INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES ("' .     $name . '", "' . $pass . '", ' . $time . ', ' . $timezone . ', 1);');
    }
    catch(PDOException $exception)
    {
        echo $exception->getMessage();
    }
}

//
// seeks a password for a given username
//

function seek($user)
{
    global $timezone;
    global $db;

    try
    {
        // previous tests showed that this doesn't work on all cases
        $result = $db->query('SELECT Password FROM Users WHERE Username="'. $user . '"');
        foreach ($result as $row)
        {
            $password = $row['Password'];
            echo "search through SQLite: password for $user is $password\n";
        }

        $result = $db->query("SELECT * FROM Users");
        foreach($result as $row)
        {
            $username = $row['Username'];
            $password = $row['Password'];

            if ($username == $user)
            {
                echo " search through array: password for $username is $password";
                break;
            }
        }
    }
    catch(PDOException $exception)
    {
        echo $exception->getMessage();
    }
}

// ---

echo "<pre>\n";

try
{
    $db = new PDO('sqlite::memory:');
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                                                                            $db->exec("CREATE     TABLE     IF     NOT         EXISTS         Users     (Id             INTEGER         PRIMARY     KEY,         Username     TEXT     UNIQUE     NOT NULL, Password TEXT NOT NULL, Time INTEGER UNIQUE NOT NULL, Timezone INTEGER NOT NULL, Active BOOLEAN NOT NULL);");
}
catch(PDOException $exception)
{
    echo $exception->getMessage();
}

add_user1("Bob", "cat");
sleep(1);
add_user1("Mark", "dog");
sleep(1);
add_user2("John", "mouse");
sleep(1);
add_user2("Alice", "rodent");

try
{
    $result = $db->query('SELECT * FROM Users');
    foreach ($result as $row)
    {
        echo "      Id: " . $row['Id'] . "\n";
        echo "Username: " . $row['Username'] . "\n";
        echo "Password: " . $row['Password'] . "\n";
        echo "    Time: " . $row['Time'] . "\n";
        echo "Timezone: " . $row['Timezone'] . "\n";
        echo "  Active: " . $row['Active'] . "\n";
        echo "\n";
    }
}
catch(PDOException $exception)
{
    echo $exception->getMessage();
}

seek("Alice");

echo "\n\n";

seek("Mark");


$db = NULL;

?>
Источник

1 ответ

Решение

Кто-то сказал мне, что я должен удалить типы на привязке. Я сделал, и это работает:)

Спасибо всем, кто прочитал это.

Вот полный рабочий пример.

<?php

error_reporting(E_ALL);
date_default_timezone_set('Europe/Helsinki');
ini_set('default_charset', 'UTF-8');
mb_internal_encoding("UTF-8");
header('Content-Type: text/html; charset=UTF-8');

$timezone = date('Z');
$db = '';

// ---

//
// adds a user in the db with a prepared statement
//

function add_user1($name, $pass)
{
    global $timezone;
    global $db;

    $time = time();

    try
    {
        $statement = "INSERT INTO Users (Username, Password, Time, Timezone, Active)     VALUES     (:Username,:Password,:Time,:Timezone,:Active);";    
        $query = $db->prepare($statement);
        $query->bindValue(':Username', $name);
        $query->bindValue(':Password', $pass);
        $query->bindValue(':Time', $time);
        $query->bindValue(':Timezone', $timezone);
        $query->bindValue(':Active', '1');
        $ok = $query->execute();
    }
    catch(PDOException $exception)
    {
        echo $exception->getMessage();
    }
}

//
// adds a user in the db with a direct execution
//

function add_user2($name, $pass)
{
    global $timezone;
    global $db;

    $time = time();

    try
    {
        $db->exec('INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES ("'     .     $name . '", "' . $pass . '", ' . $time . ', ' . $timezone . ', 1);');
    }
    catch(PDOException $exception)
    {
        echo $exception->getMessage();
    }
}

//
// seeks a password for a given username
//

function seek($user)
{
    global $timezone;
    global $db;

    try
    {
        // previous tests showed that this doesn't work on all cases
        $result = $db->query('SELECT Password FROM Users WHERE Username="'. $user . '"');
        foreach ($result as $row)
        {
            $password = $row['Password'];
            echo "search through SQLite: password for $user is $password\n";
        }

        $result = $db->query("SELECT * FROM Users");
        foreach($result as $row)
        {
            $username = $row['Username'];
            $password = $row['Password'];

            if ($username == $user)
            {
                echo " search through array: password for $username is $password";
                break;
            }
        }
    }
    catch(PDOException $exception)
    {
        echo $exception->getMessage();
    }
}

// ---

echo "<pre>\n";

try
{
    $db = new PDO('sqlite::memory:');
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                                                                                                                                                                    $db->exec("CREATE             TABLE                 IF                 NOT                         EXISTS                 Users                 (Id                 INTEGER         PRIMARY     KEY,         Username     TEXT     UNIQUE     NOT NULL, Password TEXT NOT NULL, Time INTEGER UNIQUE NOT NULL, Timezone INTEGER NOT NULL, Active BOOLEAN NOT NULL);");
}
catch(PDOException $exception)
{
    echo $exception->getMessage();
}

add_user1("Bob", "cat");
sleep(1);
add_user1("Mark", "dog");
sleep(1);
add_user2("John", "mouse");
sleep(1);
add_user2("Alice", "rodent");

try
{
    $result = $db->query('SELECT * FROM Users');
    foreach ($result as $row)
    {
        echo "      Id: " . $row['Id'] . "\n";
        echo "Username: " . $row['Username'] . "\n";
        echo "Password: " . $row['Password'] . "\n";
        echo "    Time: " . $row['Time'] . "\n";
        echo "Timezone: " . $row['Timezone'] . "\n";
        echo "  Active: " . $row['Active'] . "\n";
        echo "\n";
    }
}
catch(PDOException $exception)
{
    echo $exception->getMessage();
}

seek("Alice");

echo "\n\n";

seek("Mark");


$db = NULL;

?>
Источник
Другие вопросы по тегам