CODEWITHSUNDEEP
phpsqlsqlite
user2126173
user2126173

Reputation: 83

PHP variable in SQLite query

I'm a beginner with web-related coding and I'm trying to make a web-interface from where I can read and write to the sqlite database. My current problem is implementing a PHP-variable ($inNodeID) to sqlite query:

SELECT * FROM data WHERE NodeID = "$inNodeID"

If I replace the variable in query to the value of the variable ("ID007") everything seems to work. So what is wrong with my syntax in this manner?

    $inNodeID = "ID007";
    echo "Requested node: $inNodeID \n";

    print "<table border=1>";
    print "<tr><td>NodeID</td><td>MemoryIndex</td><td>DataIndex</td><td>TimeStamp</td></tr>";
    $result = $db->query('SELECT * FROM data WHERE NodeID = "$inNodeID"');
    //$result->bindParam(':inNodeID', $inNodeID, PDO::PARAM_STR);

    foreach($result as $row)
    {
    print "<td>".$row['NodeID']."</td>";
    print "<td>".$row['MemoryIndex']."</td>";
    print "<td>".$row['DataIndex']."</td>";
    print "<td>".$row['TimeStamp']."</td></tr>";
    }
    print "</table>";

Upvotes: 3

Views: 9192

Answers (5)

TechDogLover OR kiaNasirzadeh
TechDogLover OR kiaNasirzadeh

Reputation: 5181

you should do Three steps:

  1. prepare your sql code with imaginary word and ":" instead of your variable like this:

    $statement = $db -> prepare("SELECT * FROM table WHERE col_test = :imaginary_word");

  2. bind your php variable with the previous step "imaginary word" like this:

    $statement -> bindValue(':imaginary_word', $php_variable);

  3. your statement which is a combination of your SQL code and PHP variables is ready and it's the time to execute it like this:

    $your_result = $statement -> execute();

    ♦ now you can use this "$your_result" for fetch_array() , fetch_all Or anything you want...

Upvotes: 0

Skyvory
Skyvory

Reputation: 373

With SQLite3, you can do it like this:

$query = $db->prepare('SELECT * FROM data WHERE NodeID = ? OR NodeID = ?');
$query->bindParam(1, $yourFirstNodeID, SQLITE3_INTEGER);
$query->bindParam(2, $yourSecondNodeID, SQLITE3_INTEGER);
$result = $query->execute();

var_dump($result->fetchArray());

You can find the documentation about bindParam here.

Upvotes: 1

Gobinath Mallaiyan
Gobinath Mallaiyan

Reputation: 26

Problem is because of you have enclosed variable $inNodeID. If a variable is enclosed in Quotes PHP behave in different ways based on the Quote thats used. PHP evaluates a variable only when its enclosed in Double quotes, if its used with Single Quote then PHP treats it as a STRING.

please change your code to any one of the below option, your issue will be solved

Option 1

$result = $db->query("SELECT * FROM data WHERE NodeID = $inNodeID");

Option 2

$result = $db->query('SELECT * FROM data WHERE NodeID = '.$inNodeID);

For more info Check Out PHP Manual

Upvotes: 0

zenlord
zenlord

Reputation: 340

You don't need to put " around the variable. So try:

$result = $db->query('SELECT * FROM data WHERE NodeID = ' . $inNodeID );

Upvotes: -2

Your Common Sense
Your Common Sense

Reputation: 157989

It seems you were about to use the right way but for some reason gave up

Here you go:

$result = $db->prepare('SELECT * FROM data WHERE NodeID = ?');
$result->execute(array($inNodeID));
$data = $result->fetchAll();

foreach($data as $row)
...

Upvotes: 4

Related Questions