PHP and SQLite ATTACH statement

  • greentiger
  • Newbie
  • Newbie
  • No Avatar
  • Joined: Jun 29, 2009
  • Posts: 7
  • Status: Offline

Post June 29th, 2009, 1:50 pm

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.
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post June 29th, 2009, 1:50 pm

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post June 29th, 2009, 1:57 pm

Which of the fields in 'tickets' corresponds with a user from 'users'? Any? madeby?
I'd love to change the world, but they won't give me the source code.
  • greentiger
  • Newbie
  • Newbie
  • No Avatar
  • Joined: Jun 29, 2009
  • Posts: 7
  • Status: Offline

Post June 29th, 2009, 1:58 pm

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
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post June 29th, 2009, 2:04 pm

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.
I'd love to change the world, but they won't give me the source code.
  • greentiger
  • Newbie
  • Newbie
  • No Avatar
  • Joined: Jun 29, 2009
  • Posts: 7
  • Status: Offline

Post June 29th, 2009, 2:12 pm

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
  • Joined: Jul 25, 2005
  • Posts: 2735
  • Loc: Nashville, TN
  • Status: Offline

Post June 29th, 2009, 2:25 pm

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]'
I'd love to change the world, but they won't give me the source code.
  • greentiger
  • Newbie
  • Newbie
  • No Avatar
  • Joined: Jun 29, 2009
  • Posts: 7
  • Status: Offline

Post June 29th, 2009, 2:43 pm

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

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.