Showing several sentences for a given category in a SQLite database

Question

I am developing an app quote and I am using a local database to import the quotes. I have an issue here as you can see in the picture I want to put multiple quotes in one title.

For example, The title "Life" when I clicked it in the app it only shows one quote. I want it to show me a quote then I slide to another one.

Show us what you have done? What's your query to fetch the results when someone clicks on "Life" — Tejashwi Kalp Taru, Oct 22 '18 at 10:52
Use **2 distinct tables**. One for the Categories (or call them Titles, if you prefer), and one for the Sentences. Relate the Sentences to the Categories through a Category_ID. So you can fetch all the sentences relative to a specific category. — Phantômaxx, Oct 22 '18 at 13:24
Can you help me do that im searching in youtube but i didint find any thing and how could i connect the database with android studio? im working with db browser for sqlite — Hicham louzali, Oct 22 '18 at 14:08
Do use Google, not YouTube, for searching for tutorials. Keywords: `android sqlite related tables`. For your needs, the third table used in *many to many* relations (look-up table) is not needed. You need a simple *one to many* relation (one category, many sentences). — Phantômaxx, Oct 22 '18 at 17:12

MikeT · Accepted Answer · 2018-10-23T06:08:22.593

What you need is multiple tables and to adhere to normalisation (keeping data to a minimum) you'd want 4 tables.

A table for the titles. A table for the quotes themselves. A table for the authors (e.g rather than repeating "Mahatma Ghandi"). A table that maps/references titles and quotes (allowing a many to many relationship).

The title table would need two columns; - one for it's id (note _id has been used as this is potentially required as a column name for Android). - one for the title itself.

The quote table would need three columns; - one for it's id (_id again). - one for the quote itself. - one for the reference to the author table's id

The author table would need two columns; - one for it's id (_id again). - one for the author.

The last table (title_quote_reference) needs two columns - one to reference the title - one to reference the quote

The following could be used to build the above tables, populate them and run a basic query that display the full contents (as far as user friendly data is concerned (i.e. the id's/references would mean nothing/little to a user) :-

DROP TABLE IF EXISTS title_quote_map;
DROP TABLE IF EXISTS quote;
DROP TABLE IF EXISTS author;
DROP TABLE IF EXISTS title;

CREATE TABLE IF NOT EXISTS author (_id INTEGER PRIMARY KEY, author TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS title (_id INTEGER PRIMARY KEY, title TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS quote (_id INTEGER PRIMARY KEY, quote TEXT UNIQUE, author_reference INTEGER REFERENCES author(_id));
CREATE TABLE IF NOT EXISTS title_quote_map (
    title_reference INTEGER REFERENCES title(_id), 
    quote_reference INTEGER REFERENCES quote(_id), 
    UNIQUE (title_reference, quote_reference));

INSERT INTO title (title) VALUES 
    ('Life'),
    ('Happiness'),
    ('Positivity'),
    ('Famous Quotes'),
    ('Friendship'),
    ('Love'),
    ('Family'),
    ('Motivation')
;

INSERT INTO author (author) VALUES
    ('Leonardo da-Vinci'),
    ('Mahatma Ghandi'),
    ('Winston Churchill'),
    ('anon')
;

INSERT INTO quote (quote,author_reference) VALUES
    ('Life is my message.',2),
    ('Not how long, but how well you have lived.',4),
    ('Never in the field of human combat have so many owed so much to so few',3),
    ('I love those who can smile in trouble',1)
;

INSERT INTO title_quote_map VALUES
    (1,1),(1,2),(1,4), -- Life quotes
    (2,2),(2,4), -- Happiness quotes
    (3,1),(3,2),(3,4), -- Positivity quotes
    (4,1),(4,3),(4,4), -- Famous quotes
    (6,4), -- Love quotes
    (8,1),(8,2),(8,4)
;

SELECT quote.quote, title.title, author.author 
    FROM title_quote_map
        JOIN title ON title._id = title_quote_map.title_reference
        JOIN quote ON quote._id = title_quote_map.quote_reference
        JOIN author ON author._id = quote.author_reference
;

Running the above would/should result in messages along the lines of :-

DROP TABLE IF EXISTS title_quote_map
> OK
> Time: 0.622s


DROP TABLE IF EXISTS quote
> OK
> Time: 0.358s


DROP TABLE IF EXISTS author
> OK
> Time: 0.359s


DROP TABLE IF EXISTS title
> OK
> Time: 0.282s


CREATE TABLE IF NOT EXISTS author (_id INTEGER PRIMARY KEY, author TEXT UNIQUE)
> OK
> Time: 0.242s


CREATE TABLE IF NOT EXISTS title (_id INTEGER PRIMARY KEY, title TEXT UNIQUE)
> OK
> Time: 0.307s


CREATE TABLE IF NOT EXISTS quote (_id INTEGER PRIMARY KEY, quote TEXT UNIQUE, author_reference INTEGER REFERENCES author(_id))
> OK
> Time: 0.642s


CREATE TABLE IF NOT EXISTS title_quote_map (
    title_reference INTEGER REFERENCES title(_id), 
    quote_reference INTEGER REFERENCES quote(_id), 
    UNIQUE (title_reference, quote_reference))
> OK
> Time: 0.307s


INSERT INTO title (title) VALUES 
    ('Life'),
    ('Happiness'),
    ('Positivity'),
    ('Famous Quotes'),
    ('Friendship'),
    ('Love'),
    ('Family'),
    ('Motivation')
> Affected rows: 8
> Time: 0.34s


INSERT INTO author (author) VALUES
    ('Leonardo da-Vinci'),
    ('Mahatma Ghandi'),
    ('Winston Churchill'),
    ('anon')
> Affected rows: 4
> Time: 0.276s


INSERT INTO quote (quote,author_reference) VALUES
    ('Life is my message.',2),
    ('Not how long, but how well you have lived.',4),
    ('Never in the field of human combat have so many owed so much to so few',3),
    ('I love those who can smile in trouble',1)
> Affected rows: 4
> Time: 0.24s


INSERT INTO title_quote_map VALUES
    (1,1),(1,2),(1,4), -- Life quotes
    (2,2),(2,4), -- Happiness quotes
    (3,1),(3,2),(3,4), -- Postivity quotes
    (4,1),(4,3),(4,4), -- Famout quotes
    (6,4), -- Love quotes
    (8,1),(8,2),(8,4)
> Affected rows: 15
> Time: 0.242s


SELECT quote.quote, title.title, author.author 
    FROM title_quote_map
        JOIN title ON title._id = title_quote_map.title_reference
        JOIN quote ON quote._id = title_quote_map.quote_reference
        JOIN author ON author._id = quote.author_reference
> OK
> Time: 0s

The result from the query would be :-

As you can see a title can have multiple quotes and a quote can also have multiple titles.

You could modify the query to be :-

SELECT quote.quote, title.title, author.author 
    FROM title_quote_map
        JOIN title ON title._id = title_quote_map.title_reference
        JOIN quote ON quote._id = title_quote_map.quote_reference
        JOIN author ON author._id = quote.author_reference
    ORDER BY random()
    LIMIT 1
;

This would then select a random quote e.g. for quote of the day, as an example.

Note the above includes Foreign Keys, these have to be turned on when utilising the Android's native SQLite using/executing the SQL "PRAGMA foreign_keys=ON;" (in the onOpen or the onConfigure method). There is no actual need to have the REFERENCES ... so these could be removed or ignored if you didn't want to turn Foreign Keys on.

Thaaank you so much you really helped me a lot all i need how to connect with android studio here is my code — Hicham louzali, Oct 23 '18 at 14:05
As it's an external database, you need to copy the database (be.db) into the assets folder (sub-directory databases for SQLiteAssetHelper) and then copy (when the database doesn't exist) to it's final location (generally data/data/your_package/databases). [SQliteAssetHelper](https://github.com/jgilfelt/android-sqlite-asset-helper) can simplify this. — MikeT, Oct 23 '18 at 19:07
can i have your facebook so we can talk if you want to of course , im really having trouble here — Hicham louzali, Oct 24 '18 at 16:03
A working example that creates a DB in DB Browser for SQlite (as above), and copies it from the App's assets folder has been put into [GITHUB](https://github.com/Mike-j-t/MyQuotes). Please note that Stack Overflow is not an ongoing code writing service. It is meant for answering questions on a specific programming issue. — MikeT, Oct 24 '18 at 20:56

Showing several sentences for a given category in a SQLite database

1 Answers1