Help with php queries

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 chrisjchrisj   User is offline

  • D.I.C Head

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

Help with php queries

Posted 23 November 2019 - 04:50 PM

Currently, I have this php code where the "//add data to paid table" executes successfully.
(and successfully adds the '$site_add_amount' into the 'site_amount' table column):

$site_add_amount = $video_cost_new *0.50;

// add data to paid table
$insert_buy = $db->insert('paid_videos', [
'id_user' => $user_id,
'video_play_price' => (string)$video_cost_new,
'id_video' => $video_id,
'site_amount' => $site_add_amount,
'time' => $time_start
]);



However, I'm trying to get 'site_add_amount' to be totaled in the db, each time the code is executed, so it shows a continuous total, of every 'site_amount'. I'm not very skilled in queries, but I've tried this without success:

$insert = "INSERT INTO paid_videos (site_total) VALUES('$site_add_amount')";
$site_total = $db->rawQuery("SELECT SUM('site_amount') FROM paid_videos");
//$update_site_total = $site_total->site_amount+$site_total;



any guidance/help is appreciated

This post has been edited by chrisjchrisj: 23 November 2019 - 05:20 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Help with php queries

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 05:49 PM

A few things visually are off the mark.

Your insert doesn't have a key field that I can tell. This seems like a poor choice if you have different instances of information being stored.

Additionally why would the sum of all the lines be stored, continuously, in each inserted row? If you want to insert multiple entries for a given key, that's fine. Your SELECT, when you pull what ever out, would do the summing GROUPED BY the key.

Ex:

cre ate ta ble #foo(id int, lval int)
in sert int o #foo(id, lval) values (1, 1)
inse rt in to #foo(id, lval) values (1, 2)
in sert i nto #foo(id, lval) values (1, 5)
inse rt i nto #foo(id, lval) values (2, 7)

select *
from #foo

SELECT id, sum(lval)
from #foo
group by id

dro p tab le #foo


id          lval
----------- -----------
1           1
1           2
1           5
2           7

 

id          
----------- -----------
1           8
2           7

Was This Post Helpful? 0
  • +
  • -

#3 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 - 06:05 PM

Thanks so much for your reply.
I'm sure you are right "Your insert doesn't have a key field that I can tell. This seems like a poor choice if you have different instances of information being stored".

"why would the sum of all the lines be stored, continuously, in each inserted row", you're right, that is a bad idea. Thanks for thinking that through for me.

This sounds right: "to insert multiple entries for a given key, that's fine.
Your SELECT, when you pull what ever out, would do the summing GROUPED BY the key",
but I don't understand your example.

You're saying I should have a separate table for the grouped (site_total) entries (site_amount)?

You example, although very appreciated, is a bit above my skill level

Any additional clarification/explanation is welcomed

Much thanks again

This post has been edited by chrisjchrisj: 23 November 2019 - 06:07 PM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 06:12 PM

Let's back up.

What are you storing in the table?

As it looks you would have user id and video id as your keys. Meaning you can have one user to many videos.

You do *NOT* want to store a running total. That is what any select is for.

Just insert the user id, video id, date time it happened, and price.
Was This Post Helpful? 0
  • +
  • -

#5 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 - 06:21 PM

Much thanks again.

Regarding "Just insert the user id, video id, date time it happened, and price";

that's what I already have here, right?

// add data to paid table
$insert_buy = $db->insert('paid_videos', [
'id_user' => $user_id,
'video_play_price' => (string)$video_cost_new,
'id_video' => $video_id,
'site_amount' => $site_add_amount,
'time' => $time_start
]);


what I was ultimately trying to accomplish was to see a total of the amount that the site gets from a transaction (site_amount total).
How would I do that?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 06:22 PM

Is the 'site' connected to the user id or whose site?
Was This Post Helpful? 0
  • +
  • -

#7 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 - 06:23 PM

I don't think I'm clear on you question

'id_user' => $user_id,

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 06:25 PM

What is "site_amount"? Is this a site owned by the user id person? Is this a site owned by someone else?

How is that different from 'video_play_price'?
Was This Post Helpful? 0
  • +
  • -

#9 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 - 06:28 PM

a user purchases a video for $2 (video_play_price), for example, $1 goes to the video uploader, and $1 goes to the site/admin and that amount stored/displayed in the db in the 'site_amount' column

This post has been edited by chrisjchrisj: 23 November 2019 - 06:29 PM

Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 06:34 PM

Okay.. then your structure is wrong.

Give me a few minutes to write up what's wrong with it and what to do to fix it.
Was This Post Helpful? 0
  • +
  • -

#11 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 - 06:35 PM

ok, I look forward to your insight
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 06:42 PM

How is the division of money determined? Is it per video as in some videos may provide 50/50 split, or what?
Was This Post Helpful? 0
  • +
  • -

#13 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 - 06:45 PM

50/50 split of video_play_price

			// credit the uploader 50% of the video cost
			$up_amount = $video_cost_new *0.50;

			// credit the website 50% of the video cost
			$site_add_amount = $video_cost_new *0.50;


of each video purchase

This post has been edited by chrisjchrisj: 23 November 2019 - 06:46 PM

Was This Post Helpful? 0
  • +
  • -

#14 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,843
  • Joined: 12-June 08

Re: Help with php queries

Posted 23 November 2019 - 06:47 PM

Always and with no exception?

No funky money on a per video basis?
Was This Post Helpful? 0
  • +
  • -

#15 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 - 06:48 PM

always, however, the price varies, but not the split
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2