Twitter API Question posted in
The official Twitter API documentation can be found here.

Nested table in mysql – Twitter API

I have 2 tables. USER table with all personal details and TWEETTABLE with all tweets from user’s timeline retrieved through twitter api

User table

  id|twitter_user_name|firstname|lastname|email
 -----------------------------------------------
  1 |  @johnsmith     |john     |smith   |[email protected]

tweettable

name|scr_name|tweets      |created_at_ |followers_count
----------------------------------------------------------
CNN |CNN     |*tweet text*| 14nov 12:32|   38713286

What i actually actually want is:

     id|            twitter_details                             |
  -----|------------------------------------------------------- |
       |name |scr_name|tweets      |created_at_ |followers_count|
       | ----|--------|------------|------------|--------------
     1 |  CNN|CNN     |*tweet text*| 14nov 12:32|   38713286    |
   ----|------------------------------------------------------
     2 |  BBC|BBC     |*tweet text*| 14Nov 16:43|   38713254    |
   ----|-----|--------|------------|------------|---------------|------
     3

that is, a table inside a column. Column 1 will have the user id and column two with all of his corresponding twitter timeline details.I can probably do this in oracle by doing this:

CREATE TYPE tweet_typ AS OBJECT
( 
 name VARCHAR2(20),
 scr_name VARCHAR2(25),
 tweets text,
 created_at timestamp;
 followers count VARCHAR2(25)
 )
MEMBER PROCEDURE display_details (SELF IN OUT
twitter_typ )
);

and use this inside the twitter_details column as a nested table

CREATE TABLE TWEETTABLE
( id INT,
  twitter_details tweet_typ
   );

And then insert

 INSERT INTO TWEETTABLE
  VALUES ( 1,twitter_typ (--extracted values goes here--));

But Mysql does not allow nested tables.So how can I define tweettable as an object and making it a nested table inside the column twitter_details in mysql?

Tags:

2

Answers


  1. 0

    You can’t do that, but you can do the way everybody does. Just define

    tweettable

    as

    tweetID | userID |name|scr_name|tweets      |created_at_ |followers_count
----------------------------------------------------------
  1          1    CNN |CNN     |*tweet text*| 14nov 12:32|   38713286

    you add 2 columns. One (can be autoinc) tweetID and one userID, related to the user who twitted.

    Then you get your result with join

    select * from user u join tweettable t where u.id = t.userID;
    Login or Signup to reply.
  2. 0

    Better use cross-table. For example “users_tweets”.
    table structure:

    user_id | tweed_id

    Where user_id – it is id from ‘user_table’ and ‘tweet_id’ it is ‘id’ from tweettable

    or

    user_id | tweet_name

    Other way – convert twitter_details to string:

    $strDetails = json_encode($twitter_details)

    and insert this string $strDetails to database

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search