still having problems (PHP SQLite)

  • greentiger
  • Newbie
  • Newbie
  • greentiger
  • Posts: 7

Post 3+ Months Ago

i'm having problems wrapping my head around ATTACH statements and the various JOIN statements out there, below is my code, it's easy to see what i'm trying to accomplish but i know there's a better way to do it, but i need a good explanation how:


Code: [ Select ]
 
    // grab pertinent records
    $db = sqlite_open($ticketdb) or die ("can not create or read file $ticketdb");
        $r = sqlite_escape_string($r);
 
    if(!isset($u)) {
        $query = "SELECT ticketid, status, severity, priority, madeby, madedate, assignto, described, body
                    FROM tickets ORDER BY madedate DESC LIMIT $r, $hits;";
        }
    else {
            $u = sqlite_escape_string($u);
        $query = "SELECT ticketid, status, severity, priority, madeby, madedate, assignto, described, body
                    FROM tickets WHERE assignto = '$u' ORDER BY madedate DESC LIMIT $r, $hits;";
        }
    $result = sqlite_query($db, $query);
    $arrayTickets = sqlite_fetch_all($result);
    sqlite_close($db);
 
... SNIP PRINT TABLE ...
 
    foreach($arrayTickets as $row)
        {
        $madeby = $row[4];
        $assignto = $row[6];
 
        $dbuser = sqlite_open($chatdb) or die ("can not create or read file $chatdb");
            $madeby = sqlite_escape_string($madeby);
            $query = "SELECT username FROM users WHERE userid='$madeby';";
            $result = sqlite_query($dbuser, $query);
            $array = sqlite_fetch_array($result);
            $madeby = $array[0];
 
            $assignto = sqlite_escape_string($assignto);
            $query = "SELECT username FROM users WHERE userid='$assignto';";
            $result = sqlite_query($dbuser, $query);
            $array = sqlite_fetch_array($result);
            $assignto = $array[0];
        sqlite_close($dbuser);
 
... SNIP PRINT TABLE ...
 
  1.  
  2.     // grab pertinent records
  3.     $db = sqlite_open($ticketdb) or die ("can not create or read file $ticketdb");
  4.         $r = sqlite_escape_string($r);
  5.  
  6.     if(!isset($u)) {
  7.         $query = "SELECT ticketid, status, severity, priority, madeby, madedate, assignto, described, body
  8.                     FROM tickets ORDER BY madedate DESC LIMIT $r, $hits;";
  9.         }
  10.     else {
  11.             $u = sqlite_escape_string($u);
  12.         $query = "SELECT ticketid, status, severity, priority, madeby, madedate, assignto, described, body
  13.                     FROM tickets WHERE assignto = '$u' ORDER BY madedate DESC LIMIT $r, $hits;";
  14.         }
  15.     $result = sqlite_query($db, $query);
  16.     $arrayTickets = sqlite_fetch_all($result);
  17.     sqlite_close($db);
  18.  
  19. ... SNIP PRINT TABLE ...
  20.  
  21.     foreach($arrayTickets as $row)
  22.         {
  23.         $madeby = $row[4];
  24.         $assignto = $row[6];
  25.  
  26.         $dbuser = sqlite_open($chatdb) or die ("can not create or read file $chatdb");
  27.             $madeby = sqlite_escape_string($madeby);
  28.             $query = "SELECT username FROM users WHERE userid='$madeby';";
  29.             $result = sqlite_query($dbuser, $query);
  30.             $array = sqlite_fetch_array($result);
  31.             $madeby = $array[0];
  32.  
  33.             $assignto = sqlite_escape_string($assignto);
  34.             $query = "SELECT username FROM users WHERE userid='$assignto';";
  35.             $result = sqlite_query($dbuser, $query);
  36.             $array = sqlite_fetch_array($result);
  37.             $assignto = $array[0];
  38.         sqlite_close($dbuser);
  39.  
  40. ... SNIP PRINT TABLE ...
  41.  


help would be appreciated! thanks.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

I don't know SQLite, and frankly, I don't know if the suggestion I'm about to make is good-worthy for SQLite :lol:

Why are you opening a connection to SQLite within a loop? Do that outside of the loop...
  • greentiger
  • Newbie
  • Newbie
  • greentiger
  • Posts: 7

Post 3+ Months Ago

Bogey wrote:
I don't know SQLite, and frankly, I don't know if the suggestion I'm about to make is good-worthy for SQLite :lol:

Why are you opening a connection to SQLite within a loop? Do that outside of the loop...


well SQLite is a text-file database. basically it's a mid step solution between writing a text file and a "real" database solution like MySQL. the problem with SQLite then, is that it has several limitations a "real" DB wouldn't. like file sizes, file locking, etc ...

well to help avoid some of these problems i wrote the app to use two databases (well in the end it'll probably be a few) and the ATTACH statement is supposed to allow SQLite to join two databases and temporarily build it as a single table. i'm still unclear on how to do it so what i ended up doing is reading the results of the tickets db and then querying the users db for usernames (hence the looping). the one good thing about this is that SQLite is EXTREMELY fast at reads.

but there is a more elegant solution but i don't know how to implement it.

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 82 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.