Search code examples
javascriptphpdatabaseposts

Can we filter data stored in JSON?

I am creating a website like medium. As I am new in web development I am using php ajax with mysql database and storing posts of users in JSON format. Click to see database structure. Titles and descriptions are inside JSON. My questions are:-

  1. How can I search posts as the user enters title in search bar.
  2. Should the likes and comments be stored in same JSON file (many users can comment at a same time)?
  3. Different posts have different JSON will the title and descriptions be shown on google search?
  4. How should I store big datas like:- title,descriptions,likes,comments so that I can filter easily from search input.

Thanks for help :)

Solution

  • "can you" - yes. should you no.

    You're going to need several related tables, at minimum articles, comments, users and likely more as you'll want to store attachments in a different table as well. Generally JSON should not be used for data you wish to search/index. more store for convenience.

    Example table structure. This is to get the "juices flowing" and "point you in the right direction", it should NOT be taken verbatim and turned into a production database.

    If you use the below, if you want to search for articles that contain the word "cats" you simply use

    SELECT * FROM articles WHERE content like '%cats%';

    articles

    column name column type
    id int, autoincrement
    title varchar(255)
    content text
    created_by int, Foreign Key users.id
    created_on datetime, default now
    last_updated_by int, Foreign Key users.id
    last_updated_on datetime, default now, change on update

    comments

    column name column type
    id int, autoincrement
    article_id int, Foreign Key articles.id
    content text
    created_by int, Foreign Key users.id
    created_on datetime, default now
    last_updated_by int, Foreign Key users.id
    last_updated_on datetime, default now, change on update

    users

    column name column type
    id int, autoincrement
    name varchar(255)
    username varchar(255)
    email text
    password varchar(255) STORE HASHES NOT PLAINTEXT
    created_on datetime, default now