PHP and SQLite ATTACH statement

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

Post 3+ Months Ago

Due to the nature of SQLite it seems that it would be best to separate databases in the web app that i am making.

I have a user database and a ticket database that contains the following tables:

Code: [ Select ]
// IN USER DB
$table_users = "CREATE TABLE users (
    userid INTEGER PRIMARY KEY,
    username TEXT,
    name_first TEXT default '',
    name_last TEXT default '',
    email TEXT,
    password TEXT,
    phone TEXT,
    status INTEGER);";

// IN TICKET DB
$table_tickets = "CREATE TABLE tickets (
    ticketid INTEGER PRIMARY KEY,
    status INTEGER,
    severity INTEGER,
    priority INTEGER,
    madeby INTEGER,
    madename TEXT,
    madephone TEXT,
    madedate TEXT,
    body TEXT);";

$table_comments = "CREATE TABLE notes (
    noteid INTEGER PRIMARY KEY,
    madeby INTEGER,
    madedate INTERGER,
    body TEXT);";
  1. // IN USER DB
  2. $table_users = "CREATE TABLE users (
  3.     userid INTEGER PRIMARY KEY,
  4.     username TEXT,
  5.     name_first TEXT default '',
  6.     name_last TEXT default '',
  7.     email TEXT,
  8.     password TEXT,
  9.     phone TEXT,
  10.     status INTEGER);";
  11. // IN TICKET DB
  12. $table_tickets = "CREATE TABLE tickets (
  13.     ticketid INTEGER PRIMARY KEY,
  14.     status INTEGER,
  15.     severity INTEGER,
  16.     priority INTEGER,
  17.     madeby INTEGER,
  18.     madename TEXT,
  19.     madephone TEXT,
  20.     madedate TEXT,
  21.     body TEXT);";
  22. $table_comments = "CREATE TABLE notes (
  23.     noteid INTEGER PRIMARY KEY,
  24.     madeby INTEGER,
  25.     madedate INTERGER,
  26.     body TEXT);";


Now i know i'm not awesome at creating efficient database designs, but how do i attach the two so i can query between the two? Any help would be appreciated.
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Which of the fields in 'tickets' corresponds with a user from 'users'? Any? madeby?
  • greentiger
  • Newbie
  • Newbie
  • greentiger
  • Posts: 7

Post 3+ Months Ago

UPSGuy wrote:
Which of the fields in 'tickets' corresponds with a user from 'users'? Any? madeby?


yes where madeby=userid
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

And what would you like to get from such a query? You need to use JOIN's, but if you can specific, I can give you a better example to go with.
  • greentiger
  • Newbie
  • Newbie
  • greentiger
  • Posts: 7

Post 3+ Months Ago

UPSGuy wrote:
And what would you like to get from such a query? You need to use JOIN's, but if you can specific, I can give you a better example to go with.


well mostly i would like to query for the user data so people reading the ticket can get ahold of the submitter. which, looking back, would make madename, and madephone redundant in the table tickets.
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Good call. ;) And here's what how you could go ahead and use one query to pull in the ticket and user info for parsing as needed:

Code: [ Select ]
SELECT t.*,u.* FROM tickets t
LEFT JOIN USERS u on u.id = t.madeby
WHERE t.ticketid = '[ticket number goes here]'
  1. SELECT t.*,u.* FROM tickets t
  2. LEFT JOIN USERS u on u.id = t.madeby
  3. WHERE t.ticketid = '[ticket number goes here]'
  • greentiger
  • Newbie
  • Newbie
  • greentiger
  • Posts: 7

Post 3+ Months Ago

UPSGuy wrote:
Good call. ;) And here's what how you could go ahead and use one query to pull in the ticket and user info for parsing as needed:

Code: [ Select ]
 
SELECT t.*,u.* FROM tickets t
LEFT JOIN USERS u on u.id = t.madeby
WHERE t.ticketid = '[ticket number goes here]'
 
  1.  
  2. SELECT t.*,u.* FROM tickets t
  3. LEFT JOIN USERS u on u.id = t.madeby
  4. WHERE t.ticketid = '[ticket number goes here]'
  5.  


so you don't need an ATTACH statement?
i take it t. and u. are the ticketdb and userdb filenames respectively?

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 110 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
 
cron
 

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