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?
2
Answers
You can’t do that, but you can do the way everybody does. Just define
as
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
Better use cross-table. For example “users_tweets”.
table structure:
Where
user_id
– it isid
from ‘user_table’ and ‘tweet_id’ it is ‘id’ fromtweettable
or
Other way – convert twitter_details to string:
and insert this string
$strDetails
to database