PHP - Moved: Using Sqlite Vs. Mysql For This Project

This topic has been moved to PHP Freelancing.

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

This topic has been moved to Miscellaneous.

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

This topic has been moved to PHP Freelancing.

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

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    

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.
 

Howdy,   So I am creating a website with SQLite and this time I decided to follow the rule of putting the SQLite DB outside of the document root. However, the admin would need to admin, basically, the DB, so I have two choices:   a) Put PhpLiteAdmin (The SQLite DB manager like PHPmyAdmin is for MySQL) in the document root while giving it a path to the actually DB that is outside of the document root;   b) Set a subdomain to a directory outside of the document root of course and put both PhpLiteAdmin and the SQLite DB there and the admin would access it like this - db.Awesome-Website.com;   Which one of those two options are safer?       Thank you very much!

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.      

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 

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

This topic has been moved to Third Party PHP Scripts.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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