Does this query fetch all videos

I believe, this query fetches all videos for a given user (id_user), and by the table name (u_paid_videos), only for videos that the user paid for. Does that sound correct:

$get_videos = $db->rawQuery("SELECT DISTINCT(v.id), v.* FROM u_paid_videos AS upv LEFT JOIN " . T_VIDEOS . " AS v ON (upv.id_video = v.id) WHERE upv.id_user = {$user->id} ORDER BY upv.id");

I look forward to any assistance

Why can someone have paid for the same video multiple times?

Perhaps it’s pay-per-view.

Thanks for your replies.

Ultimately, I’m trying to get data from the ‘u_paid_videos’ table (which stores each paid transaction) to appear in the array list (which displays in an html table).

$paid_list1 = '';

$get_videos = $db->rawQuery("SELECT DISTINCT(v.id), v.* FROM u_paid_videos AS upv LEFT JOIN " . T_VIDEOS . " AS v ON (upv.id_video = v.id) WHERE upv.id_user = {$user->id} ORDER BY upv.id");

if (!empty($get_videos)) {
    $len = count($get_videos);
    foreach ($get_videos as $key => $video) {
        $video = PT_GetVideoByID($video, 0, 0, 0);
        $pt->last_video = false;
        if ($key == $len - 1) {
            $pt->last_video = true;
        }
        $paid_list1 .= PT_LoadPage('transactions/list',array(
            'ID' => $video->id,
	    'PAID_USER' =>$user->id,
	    'VIDEO_NAME' =>$video->title,
	    'AMOUNT' => $earned_amount,
	    "CURRENCY" => $currency,
	     "TIME" => $time
        ));
    }
}

But, I don’t believe the array is correct. Any ideas/suggestions/guidance will be appreciated.

Yikes.

I would strongly recommend not reusing your variable names. I can’t tell what $video is supposed to be.

There’s a lot of unknown function calls in here.

  • rawQuery is not a function of any standard PHP database interaction class, so I have no idea what $db->rawQuery returns, and if it’s iterable. I assume it is, from the rest of your code.
  • What’s PT_LoadPage? What’s it doing, how’s it doing it, what’s it expecting?
  • Where did $earned_amount and $currency and $time come from, and why are they not coming from your database result?

Thanks for your reply.

Regarding not reusing, etc. I copied the query and array from another page in this script I didn’t write, to see if it would load the data so it would display in the html table, when a transaction was made.If you have an improved code example, I’d appreciate any help.

I asked the developer about PT_LoadPage, the reply was “it works same as include or require function of PHP its custom constructed function which allows loading files”.

earned_amount and time are from the db ‘u_paid_videos’ table (which stores each paid transaction). Can you help provide some assistance with how I could get the data from that db ‘u_paid_videos’ table: ‘id_user’, ‘earned_amount’, and ‘time’, to appear in the array correctly, so it can display that in the html table?

In what way is it incorrect? Is it missing some rows, or does it contain some rows that it should not?

They might be in that table, but nothing the code you have posted extracts them. They may have been returned by your query into the $get_videos array, but there’s nothing inside the loop that extracts them into those flat variable names. And because you re-use the $video variable name at the start of your foreach() loop, you won’t be able to access them.

If you var_dump($get_videos) and exit before the start of your foreach loop, does it show what you expect, or not? If not, how does it vary from your expectation?

Much thanks for your reply.
The array isn’t correct because it is not getting the correct Payer. It should reflect id_user in the html table, but does not.

Yes, I’m hoping to get some assistance to extract earned_amount and time are from the db ‘u_paid_videos’ table. I don’t really want to get videos, I just want to get the data to display on the html table.

How can access them and change it to not " re-use the $video variable name"?
so that earned_amount and time get extracted “into those flat variables”?

I added:

var_dump($get_videos);
exit();

before the start of your foreach loop and there was a full page of data, for example:

array(10) { [0]=> object(stdClass)#505 (51) { ["id"]=> int(2) ["video_id"]=> string(15) "xsuJHZcFdHldoO7" ["user_id"]=> int(2) ["short_id"]=> string(6) "LcxiOE" ["title"]=> string(10) "Rabbitt39" ["description"]=> string(23) "ttestt" ["thumbnail"]=> string(106) "uploads/photos/2019/07/91e784043ff053py77fd0f6d8fd1dc368613f3291B00Lv1WabcrZ3RJetPbH.video_thumb_5757_9.jpeg" ["video_location"]=> string(103) "upload/videos/2019/07/1A7JUyUR2CJ9pLCcQUSe_31_e0131b6ffe6dd87daaa7084c07bf4635_video_240p_converted.mp4" ["youtube"]=> string(0) "" ["vimeo"]=> string(0) "" ["daily"]=> string(0) ""

I just don’t know what I can use that info for.

Any additional guidance with getting data from the ‘u_paid_videos’ table (which stores each paid transaction) to appear in the array list (which displays in an html table), is appreciated

That is the information that your query returns, and you should be able to look through that to see whether or not it is retrieving the information that you require. If it is, your problems lie in the rest of your code. If it is not, then the query is at fault. So that’s the first thing to narrow down - is the information you require in the array?

Change your code so that instead of using $video in one of the two places you use it, you use a different name. So you either need to change your foreach() loop, or you need to change the name that you use for the return from the PT_GetVideoByID() call, and then how you refer to the variables you retrieve.

Thanks for your reply.

The dump appears to provide id, video_id, user_id, title, description, thumbnail:

array(30) { [0]=> object(stdClass)#522 (51) { ["id"]=> int(10) ["video_id"]=> string(15) "5qhjqTmPYpAV39Z" ["user_id"]=> int(4) ["short_id"]=> string(6) "GQujDH" ["title"]=> string(10) "PGirl" ["description"]=> string(16) "testing 102-0804" ["thumbnail"]=> string(107) 

I like instead to have: id, video_id, id_user, title, earned_amount and time.

Can you give me an example of " either need to change your foreach() loop, or you need to change the name that you use for the return from the PT_GetVideoByID() call, and then how you refer to the variables you retrieve"

I look forward to any additional assistance

By that I mean, instead of using

foreach ($object as $key => $data) { 
   $data = myfunction($data[0]);

I’d do something like

foreach ($object as $key=> $data) { 
  $singledata = myfunction($data[0]);

You see - I’m not re-using the same variable inside the loop, so that if I later need to refer to an element within the original $data, the one created by the foreach() loop, I can. In your code, you re-use $video inside the loop, so when you need it, you don’t have it.

And are all of those columns inside the tables that you refer to in the query? Presumably not, so you’ll need to modify the query to retrieve them from the table they are in, with an appropriate JOIN. Does any other part of your site retrieve the same combination of columns? You could look at the query from there, if it does.

Much thanks again for your reply.
I’ve modified the code to this:

$data_1 = $db->rawQuery(“SELECT id, id_user, id_video, earned_amount, time_date FROM u_paid_videos”);
if (!empty($data_1)) {
$ads_list1 .= PT_LoadPage(‘transactions/list’,array(
‘ID’ => $video->id,
‘PAID_USER’ => $user->id,
‘VIDEO_NAME’ => $video->title,
‘AMOUNT’ => $tr->earned_amount,
“CURRENCY” => $currency,
‘TIME’ => date(“Y-m-d h:i A (T)”, strtotime($video->time_date))
));
}
Any additional help/suggestion/guidance to change what I have here so as to extract the info from the columns in a db table (u_paid_videos) to match up with the array, so the array data will display info in an html table, will be greatly appreciated

Does that query return the data you need? That is, does the $data_1 array contain the information that you want to display, when you display the contents using the technique you tried a few posts ago?

Thanks for your reply.

By the technique, if you mean ‘var_dump’, I tried this:

$data_1 = $db->rawQuery("SELECT id, id_user, id_video, earned_amount, time_date FROM u_paid_videos");

if (!empty($data_1)) {

        $ads_list1 .= PT_LoadPage('transactions/list',array(
            'ID' => $video->id,
			'PAID_USER' => $user->id,
			'VIDEO_NAME' =>  $video->title,
			'AMOUNT' => $tr->earned_amount,
			"CURRENCY" => $currency,
			'TIME' => date("Y-m-d h:i A (T)", strtotime($video->time_date))
        ));
    }


var_dump($data_1);
exit();

and I see this:

array(49) { [0]=> object(stdClass)#502 (5) { ["id"]=> int(1) ["id_user"]=> int(2) ["id_video"]=> int(2) ["earned_amount"]=> string(1) "1" ["time_date"]=> string(19) "2019-07-31 17:53:04" } [1]=> object(stdClass)#568 (5) { ["id"]=> int(2) ["id_user"]=> int(2) ["id_video"]=> int(3) ["earned_amount"]=> string(1) "1" ["time_date"]=> string(19) "2019-08-01 15:19:54" } [2]=> object(stdClass)#501 (5) { ["id"]=> int(3) ["id_user"]=> int(2) ["id_video"]=> int(2) ["earned_amount"]=> string(1) "1" ["time_date"]=> string(19) "2019-08-01 15:43:09" } [3]=> object(stdClass)#499 (5) { ["id"]=> int(4) ["id_user"]=> int(2) ["id_video"]=> int(3) ["earned_amount"]=> string(1) "1" ["time_date"]=> string(19) "2019-08-01 16:05:12" } [4]=> object(stdClass)#498 (5) { ["id"]=> int(5) ["id_user"]=> int(2) ["id_video"]=> int(1) ["earned_amount"]=> string(1) "1" ["time_date"]=> string(19) "2019-08-01 16:05:18" } [5]=>

etc…

So, does that var_dump show you that it is returning the data in the query?

It is certainly returning some data, but it doesn’t tell me whether it’s the correct data. Does it tell you?

thanks for your reply.

If this:
["id"]=> int(1) ["id_user"]=> int(2) ["id_video"]=> int(2) ["earned_amount"]=> string(1) "1" ["time_date"]

means that it is returning: id, id_user, id_video, earned_amount, and time_date from the query, then yes it is returning the data that I’m hoping for, but is returning the same as ‘extracting’ the data?

Well, it does seem to be returning something for all those columns, presumably the value is present for time_date but you’ve cut it off your post? It was present in the earlier one, so it’s probably fine, as long as you are happy that those look like the values you would expect.

You started the thread saying that the information didn’t look correct. If you look through all of this array, does the information now appear to be correct? If it does, then you can go on to getting it to display.

Well, you’re using the terms, so does it seem to be the same to you? You now have an array with some values in, which of course you can use to display a list. Whether you’d describe that as “extracting” or “returning” is a matter of opinion. I used the term “returning” because you called a function, rawQuery(), which returns an array of data or (presumably) an error code. In this case it’s returned some data. I guess you could say it’s extracted it, I only stick to that because your function uses the return() statement to pass the data back to the calling code.

What you need to do now, presuming you’re happy with what is in the array, is modify your foreach() loop to use the values from the array. You’ve dropped the loop, and the line that gets the video information based on the ID of the video, so the values starting with $video-> don’t exist any more. And it was never clear where the other values were coming from. So you’ve made progress, in that at least you’re starting with the correct values in the array.

Thanks again for all the help.
I’ve made some progress.
This code populates the html table with correct data, but each new transaction replaces the one row (in the html table) with the new data (instead of creating a 2nd row of data, etc.).

$data_1 = $db->rawQuery(“SELECT id, id_user, id_video, earned_amount, time_date FROM u_paid_videos”);

if (!empty($data_1)) {

    $ads_list1 .= PT_LoadPage('transactions/list',array(
        'ID' => $video->id,
        'PAYER_NAME' => $tr->id_user,
        'VIDEO_NAME' =>  $video->title,
        'AMOUNT' => $tr->earned_amount,
        "CURRENCY" => $currency,
        "TIME" => $tr->time_date
    ));
}

any help with tweaking this so that all transactions get listed in the html table will be greatly appreciated.

I can’t see how you are using the data that you retrieved from the query, unless there is some code you have edited out of your post. Your query data is in $data1, but your $ads_list1 gets information from elsewhere.

Thanks for your reply.
I have since copied some code (from another part of this web script) and this is what I have now:

$get_videos = $db->rawQuery("SELECT DISTINCT(v.id), v.*, upv.time_date as time_date FROM u_paid_videos AS upv LEFT JOIN " . T_VIDEOS . " AS v ON (upv.id_video = v.id) WHERE upv.id_user = {$user->id} AND upv.time_date ORDER BY upv.id DESC");

$pt->videos = $get_videos;

if (!empty($get_videos)) { $len = count($get_videos); foreach ($get_videos as $key => $video) { $video = PT_GetVideoByID($video, 0, 0, 0); $pt->last_video = false; if ($key == $len - 1) { $pt->last_video = true; }

    $ads_list1 .= PT_LoadPage('transactions/list',array(
        'ID' => $video->id,
        'PAYER_NAME' => $tr->id_user,
        'VIDEO_NAME' =>  $video->title,
        'AMOUNT' => $tr->earned_amount,
        "CURRENCY" => $currency,
        'TIME' => date("Y-m-d h:i A (T)", strtotime($video->time_date))
    ));
}
}

This is an improvement as it does not repeat rows. This displays all transactions listed in order of correct date. It displays correct title, correct ID and correct time/date. However, whatever the latest transaction ‘Amount’ is, it changes all transactions displayed, to this ‘Amount’. Any suggestions, comments, assistance you’d like to provide so that the correct ‘earned_amount’ displays for each transaction is appreciated.