How to query SQLITE database from inside an HTML script

Question

What's the write syntax to query an SQLITE database from inside an HTML script? The code below (which btw works perfectly when used in a separate php file) doesn't return anything in the textarea....

Any help would be highly appreciated. Thanks

<p>Patient search:</p> 
<textarea id="SearchBoxPt" textarea name="SearchBoxPt" style="height: 30px; resize: none; width: 600px;">       
</textarea></p> 

<button type="button" onclick="populateField_SearchPt()">Load Pt 1</button>

<script>
function populateField_SearchPt() 
{        
    <?php
    class MyDB extends SQLite3
    {
        function __construct()
        {
            $this->open('Anagrafica.db');
        }
    }

    $db = new MyDB();
    if(!$db)
    {
        echo $db->lastErrorMsg();
    } else 
    {
        echo "Opened database successfully\n\n";
    }

    $results = $db->query('SELECT name FROM Anagrafica WHERE hospital_ID="1"');
    ?>    

    document.getElementById ("SearchBoxPt").value = $results; 
}
</script>

You'll need to fetch your result: php.net/manual/en/sqlite3.open.php — Progrock
– Progrock, Commented May 28, 2017 at 21:05

sic-sic · Accepted Answer · 2017-05-30 21:16:34Z

1

Here is an example

PHP

<?php
class MyDB extends SQLite3
{
    function __construct()
    {
        $this->open('Anagrafica.db');
    }
}

$db = new MyDB();
if(!$db)
{
    echo $db->lastErrorMsg();
}

$hId = $_GET['hId']; //we're getting the passed hId as a paramater in the url

$query = $db->prepare("SELECT name FROM Anagrafica WHERE hospital_ID=:id");
$query->bindValue(':id', $hId, SQLITE3_INTEGER);
$results = $query->execute()->fetchArray();
echo $results['name'];
?>

HTML

<p>Enter Hospital Id:
    <input id="HospitalId" type="number" value="1">
</p>

<p>Patient search:</p> 
<textarea id="SearchBoxPt" textarea name="SearchBoxPt" style="height: 30px; resize: none; width: 600px;">       
</textarea>
</p> 

<button type="button" onclick="populateField_SearchPt()">Load Pt 1</button>

<script>
function populateField_SearchPt() 
{
    var inputId = document.getElementById("HospitalId").value; //we get the user input value and put it in a var

    var xhttp = new XMLHttpRequest();
    xhttp.open("GET", "path/to/yourPhpFile.php?hId=" + inputId, true); // we're passing the hId to the server as a parameter
    xhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("SearchBoxPt").value = this.responseText;
        }
    };
    xhttp.send(); 

}
</script>

edited May 30, 2017 at 21:16

answered May 29, 2017 at 17:43

sic-sic

1821 silver badge9 bronze badges

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

7 Comments

sic-sic Over a year ago

try to go to localhost/path/to/yourPhpFile.php?hId=1 and check the output result

jeddi Over a year ago

Blank page... I have noticed some differences in the quotes between your initial php code and the latest one with hld. Could that be the reason? $hId = $_GET['hId']; //we're getting the passed hId as a paramater in the url $results = $db->query("SELECT name FROM Anagrafica WHERE hospital_ID=$hId")->fetchArray(); //Get specific entry - hard coded $results = $db->query('SELECT name FROM Anagrafica WHERE hospital_ID="1"')->fetchArray();

sic-sic Over a year ago

try escaping the hId like this: $results = $db->query("SELECT name FROM Anagrafica WHERE hospital_ID=" . $db->escapeString($hId))->fetchArray();

jeddi Over a year ago

Nothing... I still get an empty page

jeddi Over a year ago

The problem is in WHERE hospital_ID=$hId, but I don't know the correct syntax to combine them

|

sic-sic · Accepted Answer · 2017-05-29 13:42:41Z

1

You can not put php scripts in javascript knowing that php is executed in the server side and not on the browser.

The solution is to keep your php code into a seperated file and make an ajax call to your php on every button click to retreive data from database.

PHP

<?php
class MyDB extends SQLite3
{
    function __construct()
    {
        $this->open('Anagrafica.db');
    }
}

$db = new MyDB();
if(!$db)
{
    echo $db->lastErrorMsg();
}

$results = $db->query('SELECT name FROM Anagrafica WHERE hospital_ID="1"')->fetchArray();
echo $results['name'];
?>

HTML

<p>Patient search:</p> 
<textarea id="SearchBoxPt" textarea name="SearchBoxPt" style="height: 30px; resize: none; width: 600px;">       
</textarea>
</p> 

<button type="button" onclick="populateField_SearchPt()">Load Pt 1</button>

<script>
function populateField_SearchPt() 
{
    var xhttp = new XMLHttpRequest();
    xhttp.open("GET", "path/to/yourPhpFile.php", true);
    xhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("SearchBoxPt").value = this.responseText;
        }
    };
    xhttp.send(); 

}
</script>

edited May 29, 2017 at 13:42

answered May 28, 2017 at 21:53

sic-sic

1821 silver badge9 bronze badges

8 Comments

jeddi Over a year ago

Thanks. I have tried but the PHP must have some issues because it doesn't retrieve the value...

jeddi Over a year ago

Are you sure about echo $results['name']; ?

sic-sic Over a year ago

I have updated the php file (line 16). Try it and feedback me

sic-sic Over a year ago

Also ensure you are using a correct path to your php file... if your php file is in the same folder as your html file then call open() method like this: xhttp.open("GET", "yourPhpFileName.php", true);

jeddi Over a year ago

Awesome! :-) Great answer. Thank you so much

|

Collectives™ on Stack Overflow

How to query SQLITE database from inside an HTML script

2 Answers 2

7 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related