PHP variable in SQLite query

Question

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>";

Your Common Sense · Accepted Answer · 2013-07-24 13:33:50Z

4

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)
...

answered Jul 24, 2013 at 13:33

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2126173 Over a year ago

Thanks a lot, this fixed it! I think it was the use of '?' in the sql query that I missed in the first place.

Your Common Sense Over a year ago

No, you can use either way. It was the proper order of prepare / execute what you really missed

Skyvory · Accepted Answer · 2017-08-03 07:20:47Z

1

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.

answered Aug 3, 2017 at 7:20

Skyvory

3737 silver badges16 bronze badges

Comments

karthikr · Accepted Answer · 2013-07-24 16:40:27Z

0

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

edited Jul 24, 2013 at 16:40

karthikr

100k26 gold badges208 silver badges191 bronze badges

answered Jul 24, 2013 at 16:17

Gobinath Mallaiyan

262 bronze badges

1 Comment

user2126173 Over a year ago

This would have been the simplest solution and the solution I hoped, but neither of these worked. Thanks for the help though!

TechDogLover OR kiaNasirzadeh · Accepted Answer · 2018-07-15 22:35:22Z

0

you should do Three steps:

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");

bind your php variable with the previous step "imaginary word" like this:
```
$statement -> bindValue(':imaginary_word', $php_variable);
```
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...

answered Jul 15, 2018 at 22:35

TechDogLover OR kiaNasirzadeh

5,3651 gold badge34 silver badges42 bronze badges

Comments

zenlord · Accepted Answer · 2013-07-24 13:39:03Z

-2

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

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

answered Jul 24, 2013 at 13:39

zenlord

4004 silver badges16 bronze badges

1 Comment

zenlord Over a year ago

Not sure why this answer was downgraded, as it is the (or at least 'a') correct answer to the question at hand. While I agree with what @Your-Common-Sense has written, he doesn't answer the question.

Collectives™ on Stack Overflow

PHP variable in SQLite query

5 Answers 5

2 Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related