Jump to content

Recommended Posts

I have a table which currently contain data like Videoid,Tags,and clicks.

As table Tags contain multiple tags, this causing problem where a same video will show a top clicked video on each tag section which it contain.

What it should be,is how each tag have it's own clicks.

At the moment solutions which i have but dont find it suitable are following:

1.To put a single tag instead list of tags, this is problem as then i would have to copy same data for every tag

2.To create another table called Clicks, and everytime when it is clicked on video in new tag, copy all relevant data from first table and then call UNION query.

Problem with this how we also have duplicates(tho alteast not big as in first case, as new data is inserted only when click occur) and

second is how same videos could show, as first it will show one which contain click stats and single tag, and then same video which dont contain click stats from first table.

 

Closest to solution was using CROSS JOIN query, but  problem with that query is how is simply pulling all data id by id, and it need to be linked,

for example both tables  have Videoid table which is same, and then order by Clicks which is in second table.

Edited by saynotojava
Link to comment
https://forums.phpfreaks.com/topic/300621-need-help-with-query/
Share on other sites

Ok let me give entry example:
Table Name: Videos
VideoId  - 12345
Tags - Dogs,Cats
Clicks - 150
So this is current table, where problem is when i order by Clicks, there will be same video showed on both tags(tags is same thing as categories). Video should have separate clicking statistic for each Tag listed in Tags, so there could be different videos listed when clicking on Tag ordered by clicks.
I was thinking about removing Clicks from structure  of table videos, then creating table called Clicks which will contain Videoid, Tag and Clicks.
So Videoid should be same value as relevant videoid in table Videos, so we can know to which video entry is related clicking statistic.
And here is example:
Table name:clicks
Videoid:12345
Tag:Dogs
Clicks:150
Hopefully now is more clear.Can post more examples if needed.

 

From what you have described you would want four tables (see attached model)

  • video
  • tag
  • video_tag (to store the tags for each video)
  • click (to log each click of a video's tag)

 

But where is Tags in Video? That one is in list form (Dogs,Cats) and cannot be changed as it used already. That look fine if i would start with fresh table structure but i need to find solution to make it work with Tags in list form at table Video.

You started out acknowledging there was a problem with your current structure so I gave you a solution.

 

Now you say you want to stick with that crap. problematic design and get someone to bail you out. Perhaps someone else will.

  • Like 1
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.