Help with php queries

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 763 Views - Last Post: 23 November 2019 - 07:05 PM Rate Topic: -----

#16 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15498
  • View blog
  • Posts: 62,060
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 07:03 PM

Things need to be in discrete areas. Video Ids to who created it and price. Transactions are who made it, which video, and when.

Extrapolate prices and what not with a join.

cr eate table #transaction(id int, purchase_userid int, videoid int, dtentered datetime)
cr eate table #videos(id int, creater_userid int, price int, dtentered datetime)
cre ate table #user(id int, dtentered datetime)

-- who and when.
in sert into #user(id, dtentered) values(1, '01/03/2019')
in sert into #user(id, dtentered) values(10,'01/03/2019')
in sert into #user(id, dtentered) values(11,'01/03/2019')

-- id is unique, who made it, how much, and when.
in sert into #videos(id, creater_userid, price, dtentered) values(1, 10, 6, '02/01/2019')
ins ert into #videos(id, creater_userid, price, dtentered) values(2, 10, 4, '02/02/2019')
in sert into #videos(id, creater_userid, price,  dtentered) values(3, 11, 2, '02/03/2019')

-- transaction only needs a unique id, purchaser's id, video's id purchased, and when.
in sert into #transaction(id, purchase_userid, videoid, dtentered)values (1, 1,   2, '05/01/2019')
in sert into #transaction(id, purchase_userid, videoid, dtentered)values (2, 2,   2, '05/01/2019')
in sert into #transaction(id, purchase_userid, videoid, dtentered)values (3, 1,   3, '05/01/2019')

select id
from #user

select id, creater_userid, price
from #videos

select id, purchase_userid, videoid
from #transaction

-- you extrapolate the price from a join.
select a.*, b.price, (b.price / 2) as creator_profit
from #transaction a
join #videos b on a.videoid = b.id

dr op table #user
d rop table #videos
d rop table #transaction




id
-----------
1
10
11

(3 rows affected)

id          creater_userid price
----------- -------------- -----------
1           10             6
2           10             4
3           11             2

(3 rows affected)

id          purchase_userid videoid
----------- --------------- -----------
1           1               2
2           2               2
3           1               3

(3 rows affected)

id          purchase_userid videoid     dtentered               price       creator_profit
----------- --------------- ----------- ----------------------- ----------- --------------
1           1               2           2019-05-01 00:00:00.000 4           2
2           2               2           2019-05-01 00:00:00.000 4           2
3           1               3           2019-05-01 00:00:00.000 2           1



Now your account is where it gets murkey. If it's something "instantaneous" then a deposit table of sorts needs to be setup with a the transaction id and value.

You would need some way to audit this and verify the values add up.

Alternatively you could just query for a month, get the creator_profit totals for a given creator id, and pay out that way.

.. but all of that gets into murky waters of how ever you plan on doing it.

You know it's 50/50 so that result doesn't need to be calculated _OR_ stored.
Was This Post Helpful? 0
  • +
  • -

#17 chrisjchrisj   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 04-February 19

Re: Help with php queries

Posted 23 November 2019 - 07:05 PM

Wow, thanks so much, that's a lot to absorb.
I will work on it and get back to you
many thanks again
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2