Chrisj Posted August 6, 2019 Share Posted August 6, 2019 The php web script that I’m trying to modify shows an html page of transactions. I’d like to change what is displayed there. The array that generates what is displayed begins on approx line 184 (in bold, near the bottom of the code below, with the line: $ads_list .= PT_LoadPage('transactions/list',array( I’d like help to substitute what appears on the html transaction page by changing what is listed in the transaction/list array (with data from the ‘u_paid_videos’ table, which has these columns: id, id_user, id_video, video_title, time, user_id_uploaded, time_date, earned_amount, currency). Here is current the php code: <?php error_reporting(-1); // set maximum errors ini_set('display_errors' , 'true'); if (!IS_LOGGED || ($pt->config->sell_videos_system == 'off' && $pt->config->usr_v_mon == 'off') ) { header('Location: ' . PT_Link('404')); exit; } $currency = '$'; if ($pt->config->payment_currency == 'EUR') { $currency = '€'; } $types = array('today','this_week','this_month','this_year'); $type = 'today'; if (!empty($_GET['type']) && in_array($_GET['type'], $types)) { $type = $_GET['type']; } if ($type == 'today') { $start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am"); $end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm"); $array = array('00' => 0 ,'01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0); $ads_array = $array; $date_type = 'H'; $pt->cat_type = 'today'; $pt->chart_title = $lang->today; $pt->chart_text = date("l"); } elseif ($type == 'this_week') { $time = strtotime(date('l').", ".date('M')." ".date('d').", ".date('Y')); if (date('l') == 'Saturday') { $start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am"); } else{ $start = strtotime('last saturday, 12:00am', $time); } if (date('l') == 'Friday') { $end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm"); } else{ $end = strtotime('next Friday, 11:59pm', $time); } $array = array('Saturday' => 0 , 'Sunday' => 0 , 'Monday' => 0 , 'Tuesday' => 0 , 'Wednesday' => 0 , 'Thursday' => 0 , 'Friday' => 0); $ads_array = $array; $date_type = 'l'; $pt->cat_type = 'this_week'; $pt->chart_title = $lang->this_week; $pt->chart_text = date('y/M/d',$start)." To ".date('y/M/d',$end); } elseif ($type == 'this_month') { $start = strtotime("1 ".date('M')." ".date('Y')." 12:00am"); $end = strtotime(cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'))." ".date('M')." ".date('Y')." 11:59pm"); if (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 31) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0 ,'31' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 30) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 29) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 28) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0); } $ads_array = $array; $pt->month_days = count($array); $date_type = 'd'; $pt->cat_type = 'this_month'; $pt->chart_title = $lang->this_month; $pt->chart_text = date("M"); } elseif ($type == 'this_year') { $start = strtotime("1 January ".date('Y')." 12:00am"); $end = strtotime("31 December ".date('Y')." 11:59pm"); $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0); $ads_array = $array; $date_type = 'm'; $pt->cat_type = 'this_year'; $pt->chart_title = $lang->this_year; $pt->chart_text = date("Y"); } $day_start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am"); $day_end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm"); $this_day_ads_earn = $db->rawQuery("SELECT SUM(amount) AS sum FROM ".T_ADS_TRANS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND type = 'video' AND video_owner = ".$pt->user->id); //$this_day_video_earn = $db->rawQuery("SELECT * FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id); $this_day_video_earn = $db->rawQuery("SELECT * FROM u_paid_videos c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id); $day_net = 0; foreach ($this_day_video_earn as $tr) { if ($tr->currency == "USD") { //$day_net = $day_net + ($tr->amount - $tr->admin_com); $day_net = $day_net + ($tr->earned_amount); } } $today_earn = $this_day_ads_earn[0]->sum + $day_net ; $month_start = strtotime("1 ".date('M')." ".date('Y')." 12:00am"); $month_end = strtotime(cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'))." ".date('M')." ".date('Y')." 11:59pm"); $this_month_ads_earn = $db->rawQuery("SELECT SUM(amount) AS sum FROM ".T_ADS_TRANS." c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end." AND type = 'video' AND video_owner = ".$pt->user->id); //$this_month_video_earn = $db->rawQuery("SELECT * FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end." AND user_id = ".$pt->user->id); $this_month_video_earn = $db->rawQuery("SELECT * FROM u_paid_videos c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end." AND user_id_uploaded = ".$pt->user->id); $month_net = 0; foreach ($this_month_video_earn as $tr) { if ($tr->currency == "USD") { //$month_net = $month_net + ($tr->amount - $tr->admin_com); $month_net = $month_net + ($tr->earned_amount); } } $month_earn = $this_month_ads_earn[0]->sum + $month_net ; //$trans = $db->where('user_id',$user->id)->orderBy('id','DESC')->get(T_VIDEOS_TRSNS); $trans = $db->where('user_id_uploaded',$user->id)->orderBy('id_user')->get('u_paid_videos'); $ads_trans = $db->where('time',$start,'>=')->where('time',$end,'<=')->where('video_owner',$pt->user->id)->where('type','video')->get(T_ADS_TRANS); $total_ads = 0; if (!empty($ads_trans)) { foreach ($ads_trans as $key => $ad) { if ($ad->time >= $start && $ad->time <= $end) { $day = date($date_type,$ad->time); if (in_array($day, array_keys($ads_array))) { $ads_array[$day] += $ad->amount; $total_ads += $ad->amount; } } } } $ads_list = ""; $total_earn = 0; if (!empty($trans)) { foreach ($trans as $tr) { //$video = PT_GetVideoByID($tr->video_id, 0, 0, 2); $video = PT_GetVideoByID($tr->id_video, 0, 0, 2); $user_data = PT_UserData($tr->id); $currency = ""; $admin_currency = ""; $net = 0; if ($tr->currency == "USD") { $currency = "$"; //$admin_currency = "$".$tr->admin_com; $net = $tr->earned_amount; } else if($tr->currency == "EUR"){ $currency = "€"; //$admin_currency = "€".$tr->admin_com; //$net = $tr->amount - $tr->admin_com; $net = $tr->earned_amount; } elseif ($tr->currency == "EUR_PERCENT") { $currency = "€"; //$admin_currency = $tr->admin_com."%"; //$net = $tr->amount - ($tr->admin_com * $tr->amount)/100; $net = $tr->earned_amount; } elseif ($tr->currency == "USD_PERCENT") { $currency = "$"; //$admin_currency = $tr->admin_com."%"; //$net = $tr->amount - ($tr->admin_com * $tr->amount)/100; $net = $tr->earned_amount; } if ($tr->time >= $start && $tr->time <= $end) { $day = date($date_type,$tr->time); if (in_array($day, array_keys($array))) { $array[$day] += $net; } } $total_earn = $total_earn + (float)$net; if (!empty($video) && !empty($user_data)) { **$ads_list .= PT_LoadPage('transactions/list',array(** 'ID' => $tr->id, 'PAID_USER' => substr($user_data->name, 0,20), 'PAID_URL' => $user_data->url, 'USER_NAME' => $user_data->username, 'VIDEO_NAME' => substr($video->title, 0,20) , 'VIDEO_URL' => $video->url, 'VIDEO_ID_' => PT_Slug($video->title, $video->video_id), 'AMOUNT' => $tr->earned_amount, "CURRENCY" => $currency, "A_CURRENCY" => $admin_currency, "NET" => $net, "TIME" => PT_Time_Elapsed_String($tr->time) )); } } } $total_earn = $total_earn + $total_ads; $pt->array = implode(', ', $array); $pt->ads_array = implode(', ', $ads_array); $pt->page_url_ = $pt->config->site_url.'/transactions'; $pt->title = $lang->earnings . ' | ' . $pt->config->title; $pt->page = "transactions"; $pt->description = $pt->config->description; $pt->keyword = @$pt->config->keyword; $pt->currency = $currency; $pt->content = PT_LoadPage('transactions/content',array( 'CURRENCY' => $currency, 'ADS_LIST' => $ads_list, 'TOTAL_EARN' => $total_earn, 'TODAY_EARN' => $today_earn, 'MONTH_EARN' => $month_earn )); any help/guidance/suggestion is appreciated Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 7, 2019 Share Posted August 7, 2019 You don't mention anything about HOW you want to change it, so there is no way we can provide much guidance. Your script is calling a function "PT_LoadPage". That function takes two parameters. The first appears to be a description of the "type" of output: e.g. a list or content. The second is the array. I would assume those two "types" of output expect an array in the exact configuration as you are passing them now. So, you will need to find the function and modify that. However, if all you want to do is substitute a value in the current array with something else you can either change the process that runs the query to generate the data to create an array with the same structure (but different values) or add some process to change va;lues in the array. But, since you've not provided any description of what/how you want to change the output - I don't know what to say. Quote Link to comment Share on other sites More sharing options...
Chrisj Posted August 7, 2019 Author Share Posted August 7, 2019 (edited) Thanks for your reply. I'd like to substitute with id_user, user_id_uploaded, id_video, earned_amount and time_date, so I have tried this: $total_earn = $total_earn + (float)$net; if (!empty($video) && !empty($user_data)) { $ads_list .= PT_LoadPage('transactions/list',array( 'ID' => $tr->id, 'PAID_USER' => $tr->id_user, 'USER_NAME' => $tr->user_id_uploaded, 'VIDEO_ID_' => $tr->id_video, 'AMOUNT' => $tr->earned_amount, "CURRENCY" => $currency, "TIME" => $tr->time_date //'ID' => $tr->id, //'PAID_USER' => substr($user_data->name, 0,20), //'PAID_USER' =>id_user, //'PAID_URL' => $user_data->url, //'USER_NAME' => $user_data->username, //'VIDEO_NAME' => substr($video->title, 0,20) , //'VIDEO_URL' => $video->url, //'VIDEO_ID_' => PT_Slug($video->title, $video->video_id), //'AMOUNT' => $tr->earned_amount, //"CURRENCY" => $currency, //"A_CURRENCY" => $admin_currency, //"NET" => $net, //"TIME" => PT_Time_Elapsed_String($tr->time) )); } } } on the html page I see this: see attached image. Any additional assistance is appreciated. Edited August 7, 2019 by Chrisj Quote Link to comment Share on other sites More sharing options...
chhorn Posted August 7, 2019 Share Posted August 7, 2019 And what's the problem then? You use template variables that are different from what the template expects, so you have to change your variable names according to the template - like the ones you commented out - or you change the template. Quote Link to comment Share on other sites More sharing options...
Chrisj Posted August 7, 2019 Author Share Posted August 7, 2019 Thanks for your reply/help. Now the displayed result is much improved, thanks. However, ultimately, I'm trying to display data in that table when a purchase takes place. I have added/copied this code to that page: " " " " " " $ads_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; } $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" => $tr->time_date )); } } $total_earn = $total_earn + $total_ads; $pt->array = implode(', ', $array); $pt->ads_array = implode(', ', $ads_array); $pt->page_url_ = $pt->config->site_url.'/transactions'; $pt->title = $lang->earnings . ' | ' . $pt->config->title; $pt->page = "transactions"; $pt->description = $pt->config->description; $pt->keyword = @$pt->config->keyword; $pt->currency = $currency; $pt->content = PT_LoadPage('transactions/content',array( 'CURRENCY' => $currency, 'ADS_LIST' => $ads_list1, 'TOTAL_EARN' => $total_earn, 'TODAY_EARN' => $today_earn, 'MONTH_EARN' => $month_earn )); and it populates the html table, but appears to repeat that same thing in the 'time' column and the Payer Name displayed is incorrect. The Payer Name should come from the 'u_paid_videos' table column named 'id_user'. Any additional assistance will be appreciated (and help getting the table to display in ID order). thanks Quote Link to comment Share on other sites More sharing options...
chhorn Posted August 8, 2019 Share Posted August 8, 2019 (edited) Your statement is incomplete for the lack of all those variables and the missing database abstraction layer, also you're just "select *"-ing without any reference to your table layout - so how do you expect someone to reproduce your problem? Just make an encapsulated mockup with sqlite in memory (<- searchterm) that holds a complete example. You can output any data with `var_dump()` to see which column of the result you must refer to. Edited August 8, 2019 by chhorn Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted August 8, 2019 Share Posted August 8, 2019 this code is repetitive and has a number of logical mistakes. there are two different current 'owner' (uploaded) user ids, in $user->id and in $pt->user->id. the code querying for the video transaction data is getting data for all dates, but the code querying for the ad transactions is getting data for just a range of dates, so the sum of the amounts from those two things is meaningless. the code getting the $user_data, which, based on the usage, is the purchaser, is using the transaction id, not the user_id. the code is already looping over the video transactions for the current logged in user. the query and loop you just added is looping over the video transactions again, but is using some pieces of data from the outer loop, in $tr, which is why the amount and date are not changing. get rid of the query and loop you just added and use the data you already have (after you fix it so that it gets the $user_data based on the user_id and not the transaction id.) 12 hours ago, Chrisj said: help getting the table to display in ID order what order do you want the data to be in? the video transaction query is currently ordering the video transaction data by the user_id, which makes no sense, but the currently displayed id values are the video ids. this code/queries are doing what they were written to do, mistakes and all. it's up the programmer writing the code/queries to define what he/she wants, before writing anything, then design, write, test, and debug the code/queries to make sure they are doing what was defined. Quote Link to comment Share on other sites More sharing options...
Chrisj Posted August 16, 2019 Author Share Posted August 16, 2019 Thanks for the replies. I have made progress where all columns of the html table populate correctly, except for the PAYER_NAME column: $ads_list1 = ''; $pt->videos = $get_videos; $get_videos = $db->rawQuery("SELECT DISTINCT(v.id), v.*, upv.earned_amount as earned_amount, upv.id_user as id_user, 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' => $user->id, 'VIDEO_NAME' => $video->title, 'AMOUNT' => $video->earned_amount, "CURRENCY" => $currency, 'TIME' => date("Y-m-d h:i A (T)", strtotime($video->time_date)) )); } } I'd like the PAYER_NAME data to come from the db table: 'u_paid_videos' > 'id_user' column. Any ideas, suggestions are appreciated Quote Link to comment Share on other sites More sharing options...
chhorn Posted August 19, 2019 Share Posted August 19, 2019 So why don't you output id_user anywhere? Quote Link to comment Share on other sites More sharing options...
Chrisj Posted August 19, 2019 Author Share Posted August 19, 2019 Thanks for your reply. I appears that this now successfully reflects the correct PAYER NAME, in the html table, after this query was changed to this: $get_videos = $db->rawQuery("SELECT DISTINCT(v.id), v.*, upv.id_user as id_user, upv.earned_amount as earned_amount, 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.time_date ORDER BY upv.id DESC"); So, for each transaction; ID, PAYER_NAME, VIDEO_NAME, AMOUNT, CURRENCY and TIME now displays correctly in the html table. The full page shows the earned amount for the User and his purchase balance, and now thanks to this latest query modification the page also displays the correct data in an html table at the bottom of the page. However, it should just display the ID, PAYER_NAME, AMOUNT, etc. of the transactions pertaining to this User's videos, but it displays all transactions. In fact, all Users now see all transactions, not just those transactions pertaining to their own videos. So, I'm looking for some assistance with getting the code to just display the transaction data only pertaining to a Users' own videos. Any guidance is appreciated. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2019 Share Posted August 19, 2019 3 hours ago, Chrisj said: WHERE upv.time_date That is the only selection condition in your query so it displays every record with a non-zero date. You need to put a conditional expression in there to limit it to just the user's records. EG WHERE upv_time_date AND <user id of record> = <id of current user> Quote Link to comment Share on other sites More sharing options...
Chrisj Posted August 20, 2019 Author Share Posted August 20, 2019 Much thanks for reply/help and example. I tried this without success: WHERE upv.time_date AND upv.user_id_uploaded = user_id ORDER BY upv.time_date DESC"); in 'u_paid_videos ' db table the 'user_id_uploaded' column represents the owner (uploader) of the video and 'user_id" is owner/uploader from the 'videos' db table, which would be, I think should be the logged-in user seeing this page. apparently I am not getting the correct <user id of record> or <id of current user>, or something else is incorrect. (in 'users' db table the user id column is simply 'id'). any additional assistance is appreciated Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2019 Share Posted August 20, 2019 When a user logs in, where in your code is the id of that user stored? In my example in my previous post, <id of current user> should be that value, not another column in your data. Quote Link to comment Share on other sites More sharing options...
Chrisj Posted August 20, 2019 Author Share Posted August 20, 2019 Great help. Thanks for all the replies, and for your last clue. The html table now successfully displays the ID, PAYER_NAME, AMOUNT, etc. of the transactions pertaining to the logged-in User’s videos. I am wondering, however, if instead of the html table showing, in the Payer_Name column, the payers’ id, if it could display that payer’s ‘username’ instead? The only place ‘username’ is stored is in the ‘users’ db table. Any clues, as to how that might be possible, are appreciated. Quote Link to comment Share on other sites More sharing options...
chhorn Posted August 23, 2019 Share Posted August 23, 2019 just INNER JOIN on your users table Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.