1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with adding transaction tracking data

Discussion in 'MySQL' started by chrisj, Oct 14, 2019.

  1. #1
    The php video web script that I’m trying to modify has a process where Users generate credit units from viewed videos.
    The total ‘balance’ of credits is stored in mysql db table named 'User', in a column titled “balance”, with each total reflected in a users’ row, and the 'balance' total gets updated (goes up or down), depending on the activity of the credits.

    However, if 2 credits get added to the balance, for example, there is no tracking of what type of activity caused the balance total change.
    SEMrush
    I believe a separate table that gets a new row inserted for each transaction, that affects the amount should be created? but I don't know how to go about doing that. Any guidance you'd like to share is appreciated.
    What else might this need? Or do you have a better/another idea?
    I look forward to all comments/suggestions.
     
    chrisj, Oct 14, 2019 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    25,354
    Likes Received:
    3,486
    Best Answers:
    101
    Trophy Points:
    665
    #2
    It depends on what you intend to do with the data you record

    You could add a simple char column that catches a code
    or
    You could add a foreign id column that links back to another table that has each type listed.
     
    sarahk, Oct 14, 2019 IP
    JEET likes this.
  3. JEET

    JEET Well-Known Member

    Messages:
    2,423
    Likes Received:
    133
    Best Answers:
    3
    Trophy Points:
    185
    #3
    All you need to do is, create a new table "tracking_data"
    Something like this:

    id bigint(18) not null auto_increment,
    userid bigint(18),
    tracking_activity varchar(256),
    activity_type char(7),
    activity_date date


    Now, on each activity page, just before balance is updated, add records to this table,
    something like this

    insert into `tracking_data`
    ( id, userid, tracking_activity, activity_type, activity_date ) values
    ( '', '123', 'watched video ', 'add', 'timestamp' )


    insert into `tracking_data`
    ( id, userid, tracking_activity, activity_type, activity_date ) values
    ( '', '123', 'purchased video ', 'minus', 'timestamp' )

    You can set field types differently.
    Instead of BIGINT(), you can use INT()
    Instead of varchar(256), you can use char()
    Instead of activity_type being a char, you can use boolean (for add or minus balance)

    If your update balance is inside a function, then use these inserts inside that function, and you will not need to modify each activity page separately.
     
    JEET, Oct 28, 2019 IP