PHP - Putting Sqlite Db Outside Document Root

I wasn't sure where to ask this question, so I decided to ask here since it might require PHP alone to do the job.

So, I recently changed from MySQL to SQLite ( Speed performance in the servers I run. (Not websites) ), so I was wondering if its possible to retrieve info from SQLite DB and display it on the website. I know how to do it with MySQL.

The SQLite DB is located on an FTP server, so I will use ftp functions to get the data. So, if It's possible, can ya give me a small example on how to achieve this? Thanks.

The following snippit should retrieve 10 images stored in an SQLite database table. But for some reason that mystifies me, it only retrieves 9. (Or one less than the number of images that are actually stored on the table): ___________ # retrieve photos for this property
        
        $query = "SELECT * FROM binary_data where propID = $prop_id";
        $result = $db->query($query);
        $row = $result->fetch(PDO::FETCH_ASSOC);
        
        
        
        # save each image data to file
        foreach($result as $row) {
        $filename = $row['filename'];
        $image = $row['bin_data'];
        file_put_contents($filename, $image);
        }
        ?> _____________ It saves the ones it gets to the temporary files as it should, and they appear on the webpage as they should. But one is always missing. Any suggestions/corrections appreciated. -Robert    

This topic has been moved to Application Design.

http://www.phpfreaks.com/forums/index.php?topic=332001.0

hello all, i am stuck with a problem and hoping someone can help me.
i have got the following code to sort of work.
the problem i am having is when i put e.g. index.php?page=test and there is no information with test under page in the sqlite db i get this message, Notice: Undefined offset: 0 in C:\xampp\htdocs\test\Frontend\left.php on line 8.
as soon as i type index.php?page=test2 and i have test2 in the database the error is gone and shows the content
heres the code
Code: [Select]

        if (isset($_GET['page']))
        {
$page = $_GET['page'];

// Display Page.
$query = "SELECT body FROM content WHERE page = '$page' AND location = 'left' AND disabled = 'no' ORDER BY id";
$results = $base->arrayQuery($query, SQLITE_ASSOC);
$arr = $results[0];

echo '<div id="left">';
                echo '<p>' . $arr['body'] . '</p>';
echo '</div>';

}
        else
        {
                //Show Home Page
$query = "SELECT body FROM content WHERE page = 'home' AND location = 'left' AND disabled = 'no' ORDER BY id";
$results = $base->arrayQuery($query, SQLITE_ASSOC);
$arr = $results[0];

echo '<div id="left">';
                echo '<p>' . $arr['body'] . '</p>';
echo '</div>';   
               
} hope you understand what i am trying to say, thanks very much all.
 

I have a table korisnici in SQLite with INTEGER field aktivan that can have only 0 or 1 value (CHECK constraint).

Field aktivan has value 0 but PHP returns value 1, why? Is this a bug?

This is PHP code that I am running:

 $sql = "SELECT ime, aktivan FROM korisnici WHERE lower(ime) = '" . $ime . "'" . " AND sifra = '" . $_POST["sifra"] . "'";
                
$result = $db->query($sql);
$row = $result->fetchArray(SQLITE3_ASSOC);

$row['aktivan'] = 1 but in table the value is 0.

When I run same query in DB Browser for SQLite I get correct value 0.

Is this a bug?

Hi everyone this is my first post so I'm sorry if I've posted it in the wrong section!

Before I begin I'd like to point out that I'm not just here to get someone to produce what I want for me! I have a genuine interest in this forum and I'm slightly surprised and disappointed in myself that I hadn't took the time to join sooner!

Anyway to the point of this post: (Please bare with me I think I'll need to justify the use of SQLite)

I am currently undergoing a University gorup project that uses wireless sensors to collect environmental data such as, Temperature, Humidity, Light levels and Dew point. This application is programmed in Python and it collects these pieces of data (every 30 seconds) and places them into an SQLite database.

My job is to now graphically plot/show the data that the Python program collects and stores in the SQLite database on a web page. I'm using Ubuntu and have successfully installed LAMP, SQLite3 and the SQLite PD0 driver.

I have also successfully established a connection to the SQLite database as well as displaying the data on a web page (locally - thats all I need!) in the form of a simple HTML table through the use of the following PHP script:

Code: [Select]
<?php
  try
  {
    //open the database
    $db = new PDO('sqlite:/var/databases/307Code/python/readings.db');

    //now output the data to a simple html table...
    print "<table border=1>";
    print "<tr><td>Date & Time Recieved</td><td>Node</td><td>Temp</td><td>Hum</td><td>Light</td><td>Dew</td></tr>";
    $result = $db->query('SELECT * FROM readingstable');
    foreach($result as $row)
    {


      print "<tr><td>".$row['Recieved']."</td>";
      print "<td>".$row['Node']."</td>";
      print "<td>".$row['Temp']."</td>";
      print "<td>".$row['Hum']."</td>";
      print "<td>".$row['Light']."</td>";
      print "<td>".$row['Dew']."</td></tr>";
    }
    print "</table>";

    // close the database connection
    $db = NULL;
  }
  catch(PDOException $e)
  {
    print 'Exception : '.$e->getMessage();
  }
?>
What I'm now looking to do is the following:

1. Display the data from the SQLite database in the form of line graphs (I've seen that I may need to convert the data to XML?)
2. Make the application asynchronous, preferably every time a new entry is added to the SQLite database the line graph(s) update without a user needing to refresh the browser.
3. The SQLite database is storing a ridiculous floating point number for the time field in the database, for example: "2012-03-23 16:49:42.440818" is a entry in the SQLite database. Is there any way to omit the .440818 through the use of PHP? Or will I need to edit the Python script?

Also one thing to note: The person in my group who built the Python script didn't build it to make the SQLite database give each database entry a unique ID/Primary key.

Any tips/advice/help would be massively appreciated!

Regards,

Rich

I'm trying to conditionally add a table to a select query using the join, but I always get an "no such table"  error.  I'm not at all sure if the syntax is correct, so I decided to ask.    I'm using the sqlite C API interface and my tables and query were constructed using sprintf, so please ignore any %d, %s that may appear

CREATE TABLE IF NOT EXISTS tbl_master
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     volume TEXT(16) UNIQUE NOT NULL DEFAULT '',
     note   TEXT(%d) NOT NULL DEFAULT '',
     items INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_file
(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      volume_key TEXT(16) NOT NULL DEFAULT '',
      name TEXT(%d),
      size TEXT(20),
      type TEXT(4),
      path TEXT(%d),
      file_id INTEGER
);
 
CREATE TABLE IF NOT EXISTS tbl_hash
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     hash TEXT(33) NOT NULL DEFAULT '',
     volume_key TEXT(16) NOT NULL DEFAULT '',
     file_key INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_media
(
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       runtime TEXT(10) NOT NULL DEFAULT '',
       frame TEXT(12) NOT NULL DEFAULT '',
       type_key TEXT(4) NOT NULL DEFAULT '',
       hash_key TEXT(33) NOT NULL DEFAULT '',
       volume_key TEXT(16) NOT NULL DEFAULT '',
       file_key INTEGER NOT NULL DEFAULT 0
);

I milled over a few JOIN tutorials, but I'm still unclear on the exact usage of JOIN; I'm trying to add the media table if the file type is a vid, snd, or pix, but I need file information regardless.  I've tried various flavors of the following query, but each time I get the table doesn't exist error.  Selects, deletes, updates, inserts work on all tables, so I'm guessing my syntax is wrong with the JOIN.

SELECT tbl_file.*, tbl_hash.hash
  FROM tbl_file AS f, tbl_hash AS h
  LEFT OUTER JOIN tbl_media AS m
  ON  ((f.type='VID' OR f.type='SND' OR f.type='PIX')
  AND m.file_key=f.file_id)
  WHERE ((f.volume_key=tbl_master.volume
  AND (h.volume_key=tbl_master.volume AND h.file_key=f.file_id)))
  ORDER BY f.path ASC;

Any ideas on how to pull off what I'm trying to do would be greatly appreciated.  Thank you for your time.      

I am trying to convert and old website from mysql database to sqlite. One of the chores it must do is collect information from the database and put it in a list/menu select box on a page so the user can choose which item to pursue.   In the following (incomplete) snippit, I am doing something incorrectly because the sql query does get the proper information (I can put it in a table on the page just fine). But I'm having trouble getting the information into the select options on a list/menu. It appears to be putting them all, one after the other in the first option spot. The last one seems to be the only one of 6 or 7 that shows up.   It's been 10 or 12 years since I've messed with php, so I think I'm way behind... any help would be appreciated.   --------------------------- <form action="sqlPropDisplay.php" method="post" id="Residential">

hello dear php-experts,

i fairly new to simple_html_dom usage and methods. I know a little the parser,
 
i want to gather some information from this site:

https://europa.eu/youth/volunteering/organisations_en#open

is this possible to get the content - of let us say 10 or 20 last records on that page - and subesquently to store it in my mysql - db!?

<?php
// Report all PHP errors (see changelog)
error_reporting(E_ALL);

include('inc/simple_html_dom.php');

    //base url
    $base = 'https://europa.eu/youth/volunteering/organisations_en#open';

    //home page HTML
    $html_base = file_get_html( $base );

    //get all category links
    foreach($html_base->find('a') as $element) {
        echo "<pre>";
        print_r( $element->href );
        echo "</pre>";
    }

    $html_base->clear(); 
    unset($html_base);

?>

 

I have the above code and I'm trying to get certain elements of the page but it isn't returning anything. 

 

Is it possible that certain PHP functions might be disabled on the server to stop that?

The above code works perfectly on other sites.

 

Is there any workaround?

 

 

btw:  i have created a small snipped as a proof of concept to run this with Python and BeautifulSoup - 

 

import requests
from bs4 import BeautifulSoup
 
url = 'https://europa.eu/youth/volunteering/organisations_en#open'
response = requests.get(url)
soup = BeautifulSoup(response.content, 'lxml')
print(soup.find('title').text)
block = soup.find('div', class_="eyp-card block-is-flex")

 

and this.... 

 

European Youth Portal
>>> block.a
<a href="/youth/volunteering/organisation/48592_en" target="_blank">"Academy for Peace and Development" Union</a>
>>> block.a.text
'"Academy for Peace and Development" Union'
 
>>> block.select_one('div > div > p:nth-child(9)')
<p><strong>PIC:</strong> 948417016</p>
>>> block.select_one('div > div > p:nth-child(9)').text
'PIC: 948417016'

 

what is aimed in the end - i want to gather the first 20 results of the page - and put them in to a sql-db or alternatively show the information in a little widget 

Having problems pointing to where files are.

Is this the root?:

Code: [Select]
include "../connectdb.php";
That file is locate din the root.  If I am already in the root when I include that statment, the file isn't found.  If I use the above code while I am in the subdirectory hello, (root/hello), it works.

 I use this in every page, so I think I need a way to point to the absolute root, not relative.

Hi guys,

I've got a simple script that I'm running from a folder above the root (as a cron job).

I've tried the script in the httpdocs folder and it works fine.

Code: [Select]
mail('email@example.com', 'Before include', '', 'From:<email@example.com>');

// include the myriad class
include($_SERVER['DOCUMENT_ROOT'].'/path/to/class');

mail('email@example.com', 'After include', '', 'From:<email@example.com>');

// initiate and instance of our class
$obj = new class_name;

$email = $obj->email;

$file = $obj->file_name;

mail('email@example.com', 'End of file', $email.' & '.$file, 'From:<email@example.com>');
If I run this file in httpdocs, it's fine and I get all emails.

If I run this from 1 folder above the root (from a cron job), I get the first 2 emails but not the last one, suggesting that the include hasn't worked.

Could this be a permissions problem?
I have tried running the cron job as both root and apache and changed the owner/group of the script to match.

Is there a reason why includes won't work above the root?

If I an deep inside of nested folders, how do I get back to the Web Root (as far as directory notation goes)??



Debbie

Hi,
I want to get root path and use on links even if i'm in sub to sub folder etc...
suppose my site name is fitness.com and having two subfolder. so url will become

http://www.fitness.com/dir/dir_sub

here i want to put a link to go to root directory file. there are different ways to do that. e.g:

Code: [Select]
<a href='../../filename.php'>go to that page</a>

but i want to use some constant that always shows to root directory. as i defined here

Code: [Select]
define("SERVER_NAME" , $_SERVER['HTTP_HOST']);
//and used it like this
<a href='<?php echo SERVER_NAME;  ?>/filename.php'>go to that page</a>

it works when we're on root directory. but when we go to sub directory it added sub directory name with it. which i don't want.

is there any way?

Thanks

Hi All,

I googled this and there is endless results.  I went through a lot of them but couldn't get this working properly.

How do I link from within my web site root to files outside the root?  It works for me using relative links  i.e.  ../../phpfiles/includes but that is going to get messy and I can't get a way of doing absolute links to work.  If someone could lay that out so a newbie can get it clearly I would really appreciate it!


Also - I understand why I should put all of my php files outside the web root but is this a guaranteed way to secure these files other than someone hacking my ftp access?


I've looked at a few site hierarchy examples - Am I right that the only pages within the web site root should be template pages with calls to required files (outside the root), session checks, and content includes and all other includes that have php executable code should be outside the root?

I really appreciate the advice and insight.  Thank you!

is there a way in php to link from the root dir ?

like in html you just use the '/' at the start for the link " <a herf="/link.php" ></a> "

but i noticed this does not work when using php like include or require.

so is there anywey to tell a link to start from root dir? without using the ../../link.php