micky007

Ah ok I understand now. Thank you very much!

Thank you very much @Barand. Worked exactly as i wanted. I do have another one I'm not struggling with which is this, I'm trying to get the ConsignmentNo. I have tried each of the below without success. echo $arr->OrderAddresses->ConsignmentNo; echo $arr->OrderAddresses[0]->ConsignmentNo; echo $arr[0]['ConsignmentNo']; echo $arr[1]['ConsignmentNo']; Here is the Array. Array ( [OrderLines] => Array ( [0] => Array ( [Description] => Product Name [Brand] => ABCD [Quantity] => 1 [SellingPrice] => 3.68 [BudgetCode] => [LedgerCode] => [OnHold] => [PartNum] => FG5675446345 ) ) [OrderAddresses] => Array ( [0] => Array ( [BOOrderNum] => 6547467 [AccountName] => Name 1 [AccountNumber] => [FirstName] => Fname [LastName] => Lname [Address1] => My Address [Address2] => 2nd PArt [City] => MANCHESTER [County] => [PostCode] => AB1 2CD [Country] => UK [ConsignmentNo] => 547645363546 [NumberOfContainers] => 1 [PackageType] => Box [Carrier] => FEDEX ) ) [OrderNumber] => M123456 [Placed] => 2022-08-17T14:04:47.653 [TotalPrice] => 3.68 [ConsignmentNo] => [DeliveryType] => Standard [Weight] => 0.45 ) Thank you again for your support.

Hi, I've been reading a lot of tutorials from other sites on how to get a value from a JSON array but none of them seem to work for me. I have the below JSON I'm trying to get the value of OrderNumber and place it within its own variable. But all the tutorials I've read don't have the [ at the beginning of their JSON. So everything I'm trying doesn't seem to be working for me. [{"OrgID":"1234567890987654321","OrderNumber":"M123456","OrigOrderNumber":"","Placed":"2022-08-17T14:04:47.653","PostCode":"AB1 2CD","BackOfficeOrderNumber":"12345678","CustomerName":"My Name","OutletName":"My Name Two","OrderStatus":"Delivered","OrderRef":"O-0012345678","AddressCount":1}] Any help would be much appreciated please. Thank you.

Hi, I have a string with the value of { "success": true, "delivered": true, "contactDetailsRequired": false, "message": "Signed For by: D ANDERSON ", "signature": "https://webservices.thedx.co.uk/PodImage/ImageHandler.ashx?tn=906732192165", "date": "21-07-2020", "serviceLevelName": "Consigned", "time": "13:33:19", "trackedProductName": "DataExchange" } How do i go about placing the values into variables so for example: $success = "true"; $delivered = "true"; $message = "Signed for by: D ANDERSON"; etc.... My mind has gone blank and im having one of those days, any help would be greatly appreciated. Thanks

I've gone ahead and Indexed the Primary Keys too and i still get the error.

Hi everyone, I keep getting the error (see title of this post) on the first ALTER query but i cant seem to work out why. Below is the SQL and the Error. SQL: CREATE TABLE `affiliates` ( `affiliate_id` int AUTO_INCREMENT, `email` varchar(255), `password` varchar(255), `first_name` varchar(255), `last_name` varchar(255), `company` varchar(255), `vat_id` varchar(255), `address_1` varchar(255), `address_2` varchar(255), `city` varchar(255), `county` varchar(255), `postcode` varchar(255), `country` varchar(255), `telephone` varchar(255), `mobile` varchar(255), `ip` varchar(255), `status` tinyint, `signup_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(affiliate_id) ); CREATE TABLE `affiliate_deals` ( `affiliate_deal_id` int AUTO_INCREMENT, `revenue_share_percentage` decimal, `ftd_amount` decimal, `cpa` decimal, `cpc` decimal, `start_date` timestamp NULL DEFAULT NULL, `end_date` timestamp NULL DEFAULT NULL, `added_by` varchar(255), PRIMARY KEY(affiliate_deal_id) ); CREATE TABLE `deal_type` ( `deal_type_id` int AUTO_INCREMENT, `affiliate_id` int, `program_id` int, `affiliate_deal_id` int, PRIMARY KEY(deal_type_id,affiliate_id,program_id,affiliate_deal_id) ); CREATE TABLE `clicks` ( `click_id` int AUTO_INCREMENT, `affiliate_id` int, `program_id` int, `clicks_data_id` int, `creative_id` int, PRIMARY KEY(click_id,affiliate_id,program_id,clicks_data_id,creative_id) ); CREATE TABLE `clicks_data` ( `click_data_id` int AUTO_INCREMENT, `sub_id` varchar(255), `referer` varchar(255), `ip` varchar(255), `timestamp` timestamp, `ip_region` varchar(255), `ip_town` varchar(255), `ip_isp` varchar(255), `ip_long` varchar(255), `ip_lat` varchar(255), `ip_country` varchar(255), `ip_county` varchar(255), PRIMARY KEY(click_data_id) ); CREATE TABLE `creatives` ( `creative_id` int AUTO_INCREMENT, `type` int, `size` varchar(255), `code` text, `banner_url` text, `status` tinyint, PRIMARY KEY(creative_id) ); CREATE TABLE `players` ( `player_id` int AUTO_INCREMENT, `deal_id` int, `creative_id` int, `click_id` int, `sub_id` varchar(255), `ad_id` int, `status` int, `username` varchar(255), `user_id` int, `signup_date` timestamp NULL DEFAULT NULL, `first_deposit_amount` int, `ftd_matched` int, `ftd_matched_Date` timestamp NULL DEFAULT NULL, PRIMARY KEY(player_id,deal_id,creative_id,click_id) ); CREATE TABLE `players_stats` ( `player_stats_id` int AUTO_INCREMENT, `player_id` int, `deposits` decimal(10,2), `withdrawals` decimal(10,2), `bonus` decimal(10,2), `tax` decimal(10,2), `house_earnings` decimal(10,2), `stats_date` date NULL DEFAULT NULL, PRIMARY KEY(player_stats_id,player_id) ); CREATE TABLE `programs` ( `program_id` int AUTO_INCREMENT, `name` varchar(255), `url` varchar(255), `aff_url` varchar(255), `status` tinyint, `description` varchar(255), `image` varchar(255), PRIMARY KEY(program_id) ); ALTER TABLE `affiliates` ADD FOREIGN KEY (`affiliate_id`) REFERENCES `deal_type` (`affiliate_id`); ALTER TABLE `programs` ADD FOREIGN KEY (`program_id`) REFERENCES `deal_type` (`program_id`); ALTER TABLE `deal_type` ADD FOREIGN KEY (`affiliate_deal_id`) REFERENCES `affiliate_deals` (`affiliate_deal_id`); ALTER TABLE `players` ADD FOREIGN KEY (`player_id`) REFERENCES `players_stats` (`player_id`); ALTER TABLE `players` ADD FOREIGN KEY (`deal_id`) REFERENCES `deal_type` (`deal_type_id`); ALTER TABLE `players` ADD FOREIGN KEY (`creative_id`) REFERENCES `creatives` (`creative_id`); ALTER TABLE `players` ADD FOREIGN KEY (`click_id`) REFERENCES `clicks` (`click_id`); ALTER TABLE `clicks` ADD FOREIGN KEY (`clicks_data_id`) REFERENCES `clicks_data` (`click_data_id`); ALTER TABLE `clicks` ADD FOREIGN KEY (`creative_id`) REFERENCES `creatives` (`creative_id`); ALTER TABLE `clicks` ADD FOREIGN KEY (`program_id`) REFERENCES `programs` (`program_id`); ALTER TABLE `clicks` ADD FOREIGN KEY (`affiliate_id`) REFERENCES `affiliates` (`affiliate_id`); Error: I'm not sure i fully understand what that means so maybe the answer is right there. But as I'm still learning i don't understand it.

For some reason its not letting me add any text after mentioning a user so I'm having to finish my reply in a new one. What i was saying was barand has informed me to look into Data Normalisation so I'm looking into that too now.

I'm not sure which Query your wanting me to use. Did you mean this one? OR @Barand

Your kind of right, how ever all i really need is for the Query to work out how much the player_id has earned based off the affiliate_deal for program_id. So if i run this query: SELECT players.deal_id, COUNT(players.deal_id) AS TotalPlayersByDeal, SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings FROM players INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 GROUP BY players.deal_id, players.player_id Then that will display So as the result shows the records of each player that's earned the affiliate some money all i want is for the affiliate_cpa_earnings to be grouped together so my aim is to just have the 1 result showing something like this

Just so you know the correct way is echo 'Download <a href="LINK HERE">Here</a>'; Remember if you start your PHP coding with a double quote (") then your going to have to use a single quote for defining the URL, or the other way around like the example above.

Aaaaaaahhhh i see whats going on, I've done it so its calculating the total of the CPA that have the same deal_id value and then multiplying it by the total number of players. So its doing 15 x 8 x 8 which is 960. So all i have to do is remove the * COUNT(players.deal_id) part from the query and it works fine. Well that was simple enough.....

Thanks for thats, how ever I'm not getting the right result and I'm trying to find out whats actually happening. SELECT players.player_id, players.deal_id, affiliate_deals.cpa, COUNT(players.deal_id) AS TotalPlayersByDeal, SUM(affiliate_deals.cpa) * COUNT(players.deal_id) AS affiliate_cpa_earnings FROM players INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 GROUP BY players.deal_id RESULT: player_id deal_id cpa TotalPlayersByDeal affiliate_cpa_earnings 1 1 15 8 960 14 2 50 1 50 Not sure how 15 * 8 = 960?

I've already tried what you suggested but i get this error: SELECT players.player_id, players.deal_id, affiliate_deals.cpa, COUNT(players.deal_id) AS TotalPlayersByDeal, SUM(affiliate_deals.cpa * COUNT(players.deal_id)) as affiliate_cpa_earnings FROM players INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 GROUP BY players.deal_id

Hi again, Thanks again for the help you guys have all provided me so far and helping me learn and understand more from both PHP and MySQL. Based off previous help from MySQL I've decided to reduce the amount of PHP coding in my script by using JOIN queries. I'm a little stuck on the below query: SELECT players.player_id, players.deal_id, affiliate_deals.cpa, COUNT(players.deal_id) AS TotalPlayersByDeal, SUM(affiliate_deals.cpa * TotalPlayersByDeal) affiliate_cpa_earnings FROM players INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id WHERE players.affiliate_id=1 AND affiliate_deals.type = 'CPA' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 OR players.affiliate_id=1 AND affiliate_deals.type = 'Hybrid' AND players.program_id=1 AND players.status=1 AND DATE(players.ftd_matched_Date) BETWEEN '2019-02-01' AND '2019-02-28' AND players.ftd_matched=1 GROUP BY players.deal_id The issue is the SUM part, how do i go about multiplying the affiliate_deals.cpa by the number of players with the same players.deal_id value? I didn't think my example above would work and it sure didn't providing me with the error: My guess is i cant use the TotalPlayersByDeal part in the SUM query. As for a work-around I'm struggling to think of a way and this is where i need your help if possible. Thank you!

I'm such an idiot! Sorry for wasting your time just then and thanks again for helping me!

Thanks for that, ah, i thought there might of been more to it than that. Based of what you said I'm still getting the unknown column issue. SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type FROM players_stats INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id AND affiliate_deals.affiliate_deal_id=players.deal_id WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' EDIT: If i remove that bit from there where clause i then get this error:

I apologise, I'm still learning MySQL so unfortunately i didn't know . Can you provide an example of how you would do it please and leave some stuff for me to fill in/do so i can learn on the go instead of just having it done for me and not learning.

I think I've managed to do it, will do some testing but tell me you see anything wrong with it. SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type, players.status FROM players_stats INNER JOIN players ON players.player_id=players_stats.player_id INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28'

No as I'm getting an error saying: #1054 - Unknown column 'affiliate_deals.program_id' in 'on clause' SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type FROM players_stats INNER JOIN players ON players.program_id=affiliate_deals.program_id INNER JOIN affiliate_deals ON affiliate_deals.affiliate_deal_id=players.deal_id WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28'

Correct

I have program_id in both tables. How do i go about matching from the deal_id when i already have one matching the program_id which is needed in order to get the information from affiliate_deal based on the program ID of the affiliate? INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id the deal ID number is only stored in the players table as well as the affiliate_deals table which holds the information about that deals

Yes but there are only 4 rows of data in the player_stats table. Out of those 4 only 3 of them should be showing because the program_id value is 1. What I'm trying to do is the following: Select the data from table 'players_stats' where the column 'stats_date' is between the beginning of this month and the end of this month AND the 'program_id' value is 1 Then i need the query to match the player_id with the same value as what's in the table called 'players' and grab the values from the following columns (affiliate_id, deal_id WHERE status=1) Then i need the query to match the deal_id with same value as whats in the table called 'affiliate_deals" and grab the values from the following columns (revenue_share_percentage) WHERE 'start_date' is less than or equal to todays date and 'end_date' is greater or equal to todays date AND 'type' is RevShare OR Hybrid. Does that make sense? If you need to see the tables let me know and ill upload them for you.

Hi, I'm still learning MySQL using the JOIN statements in them, i cant seem to work out whats wrong with the following query: SELECT players_stats.player_id, players_stats.program_id, players_stats.house_earnings, players_stats.stats_date, affiliate_deals.affiliate_id, affiliate_deals.revenue_share_percentage, affiliate_deals.affiliate_deal_id, affiliate_deals.type FROM players_stats INNER JOIN players ON players.player_id=players_stats.player_id INNER JOIN affiliate_deals ON affiliate_deals.program_id=players.program_id WHERE affiliate_deals.type = 'RevShare' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' OR affiliate_deals.type = 'Hybrid' AND players_stats.program_id=1 AND players.status=1 AND DATE(players_stats.stats_date) BETWEEN '2019-02-01' AND '2019-02-28' The query runs but the result is not correct, below is the result: player_id program_id house_earnings stats_date affiliate_id revenue_share_percentage affiliate_deal_id type 1 1 8.92 2019-02-19 1 30 1 Hybrid 2 1 8.92 2019-02-19 1 30 1 Hybrid 2 1 8.92 2019-02-20 1 30 1 Hybrid 1 1 8.92 2019-02-19 1 30 3 RevShare 2 1 8.92 2019-02-19 1 30 3 RevShare 2 1 8.92 2019-02-20 1 30 3 RevShare So why is the result doubling and showing showing the 'type' for each of the WHERE queries? It should be one or the other based off the value of 'affiliate_deals.type' which is based off the 'affiliate_deals.affiliate_deal_id' record. Any help would be great and much appreciated. Thank you!

Thanks for the advice and guidance, I pretty much started coding my script again based off what you said and its much better now and easier to understand. I admit I've not followed ALL of them but I'm getting there and learning <?php error_reporting( error_reporting() & ~E_NOTICE ); session_start(); if ( isset( $_SESSION[ 'AffID' ] ) ) { $AffID = $_SESSION[ 'AffID' ]; include 'PHP/db.php'; //include database info include 'PHP/Affiliates/affinfo.php'; //include affiliate details //include 'PHP/Affiliates/dashboardsummary.php'; //include dashboard info ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Untitled Document</title> </head> <body> <table width="100%" border="1"> <tbody> <tr> <th width="10%" scope="row"><u>Menu:</u> </th> <td colspan="2" align="right">Welcome back <?php echo $AffFirstName. " " .$AffLastName; ?> </td> </tr> <tr> <th scope="row">Dashboard</th> <td colspan="2" align="center"> <table width="80%" border="1"> <tbody> <tr> <th scope="col">Brand</th> <th scope="col">Clicks</th> <th scope="col">Registrations</th> <th scope="col">FTDs</th> <th scope="col">Commission</th> </tr> <?php //BEGIN GETTING BRAND NAMES $connection = mysqli_connect( "$connectip", "$swiftuser", "$swiftpass", "$swiftdb" )or die( "Error " . mysqli_error( $connection ) ); $sql = "SELECT name, program_id FROM programs WHERE status=1"; /* Constants */ $startmonth = date( 'Y-m-d', strtotime( 'first day of this month' ) ); $endmonth = date( 'Y-m-d', strtotime( 'last day of this month' ) ); $todaysdate = date( 'Y-m-d' ); //$startmonth = "2019-03-01"; //$endmonth = "2019-03-31"; $result = mysqli_query( $connection, $sql )or die( "Error in Selecting " . mysqli_error( $connection ) ); if ( $result->num_rows > 0 ) { while ( $row = $result->fetch_assoc() ) { $programID = ( empty( $row[ "program_id" ] ) ) ? exit : $row[ "program_id" ]; //echo "<br> Got Program Numbers"; if ( !empty( $programID ) ) { //SqlClicks $sqlclicks = "SELECT COUNT(*) AS 'TotalMonth' FROM `clicks` WHERE affiliate_id = $AffID"; $sqlclicks .= " AND program_id = $programID AND DATE(timestamp) BETWEEN '$startmonth' AND '$endmonth'"; //echo "SQL Click:" . $sqlclicks . "<br>"; $resultclicks = mysqli_query( $connection, $sqlclicks )or die( "Error in Selecting " . mysqli_error( $connection ) ); } if ( $resultclicks->num_rows > 0 ) { while ( $rowclicks = $resultclicks->fetch_assoc() ) { $clicks = ( empty( $rowclicks[ "TotalMonth" ] ) ) ? "0" : $rowclicks[ "TotalMonth" ]; //echo "<br> Got Click Numbers"; } } if ( is_numeric( $clicks ) ) { //SqlReq $sqlreg = "SELECT COUNT(*) AS 'TotalReg' FROM `players` WHERE affiliate_id = $AffID"; $sqlreg .= " AND program_id = $programID AND DATE(signup_date) BETWEEN '$startmonth' AND '$endmonth'"; $resultreg = mysqli_query( $connection, $sqlreg )or die( "Error in Selecting " . mysqli_error( $connection ) ); //echo "SQL Reg:" . $sqlreg . "<br>"; } if ( $resultreg->num_rows > 0 ) { while ( $rowreg = $resultreg->fetch_assoc() ) { $reg = ( empty( $rowreg[ "TotalReg" ] ) ) ? "0" : $rowreg[ "TotalReg" ]; //echo "<br> Got Reg Numbers"; } } if ( is_numeric( $reg ) ) { //SqlFTDs $sqlftd = "SELECT COUNT(*) AS 'TotalFTDs' FROM `players` WHERE affiliate_id = $AffID"; $sqlftd .= " AND program_id = $programID AND ftd_matched =1 AND DATE(ftd_matched_date) BETWEEN '$startmonth' AND '$endmonth'"; $resultftd = mysqli_query( $connection, $sqlftd )or die( "Error in Selecting " . mysqli_error( $connection ) ); //echo "SQL FTD:" . $sqlftd . "<br>"; } if ( $resultftd->num_rows > 0 ) { while ( $rowftd = $resultftd->fetch_assoc() ) { $ftds = ( empty( $rowftd[ "TotalFTDs" ] ) ) ? "0" : $rowftd[ "TotalFTDs" ]; } } if ( is_numeric( $ftds ) ) { //Sqldeal $sqldeal = "SELECT deal_id AS `DealID`, SUM(CASE WHEN ftd_matched = 1 THEN 1 ELSE 0 END) AS `TotalFTDs` FROM players WHERE affiliate_id = $AffID"; $sqldeal .= " AND program_id = $programID AND `ftd_matched_Date` BETWEEN '$startmonth' AND '$endmonth' GROUP BY DealID"; $resultdeal = mysqli_query( $connection, $sqldeal )or die( "Error in Selecting " . mysqli_error( $connection ) ); //echo "SQL Deal:" . $sqldeal . "<br>"; } $dealid = array(); $dealftds = array(); $i = 0; $dealid[ $i ] = 0; $dealftds[ $i ] = 0; if ( $resultdeal->num_rows > 0 ) { while ( $rowdeal = $resultdeal->fetch_assoc() ) { $dealid[ $i ] = $rowdeal[ "DealID" ]; $dealftds[ $i ] = $rowdeal[ "TotalFTDs" ]; $i = $i + 1; } $totaldeals = count( $dealid ); } if ( $totaldeals > 0 ) { $sqlpayout = array(); $totalcpa = array(); for ( $tt = 0; $tt < count( $dealid ); $tt++ ) { //Sqlamount $sqlamount = "SELECT cpa AS `CPAAmount` FROM affiliate_deals WHERE affiliate_id = $AffID AND `affiliate_deal_id` = $dealid[$tt] AND `type` = 'CPA' AND `start_date` <= '$todaysdate' AND `end_date` >= '$todaysdate' OR affiliate_id = $AffID AND `affiliate_deal_id` = $dealid[$tt] AND `type` = 'Hybrid' AND `start_date` <= '$todaysdate' AND `end_date` >= '$todaysdate'"; //echo "SQL Amount:" . $sqlamount . "<br><br><br>"; $resultamount = mysqli_query( $connection, $sqlamount )or die( "Error in Selecting " . mysqli_error( $connection ) ); $sqlpayout[ $tt ] = 0; $totalcpa[ $tt ] = 0; if ( $resultamount->num_rows > 0 ) { while ( $rowamount = $resultamount->fetch_assoc() ) { $sqlpayout[ $tt ] = $rowamount[ "CPAAmount" ]; $totalcpa[ $tt ] = $sqlpayout[ $tt ] * $dealftds[ $tt ]; } } } } $totalcommission = array_sum( $totalcpa ); //echo "<br>"; //print_r( array_values( $sqlpayout ) ); //echo "<br>"; //print_r( array_values( $dealftds ) ); //echo "<br>"; echo '<tr> <td>' . $row[ "name" ] . '</td> <td>' . $clicks . '</td> <td>' . $reg . '</td> <td>' . $ftds . '</td> <td>£' . $totalcommission . '</td> </tr>'; //END GETTING BRAND NAMES } } ?> </tbody> </table> </td> </tr> <tr> <th scope="row">Account</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> <tr> <th scope="row">Statistics</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> <tr> <th scope="row">Creatives</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> <tr> <th scope="row">&nbsp;</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> <tr> <th scope="row">&nbsp;</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> <tr> <th scope="row">&nbsp;</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> <tr> <th scope="row">&nbsp;</th> <td>&nbsp;</td> <td>&nbsp;</td> </tr> </tbody> </table> </body> </html> <?php } else { ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Untitled Document</title> </head> <body> <p>Not Logged In</p> </body> </html> <?php } ?>

Hi everyone, I'm still kind of a noob when it comes to PHP although i am learning more when i can. I'm in the middle of creating an affiliate system in PHP/MySQL and as you'll be able to tell from looking at my code I'm no expert in it and no doubt what I've created can be done in a more efficient way. This is where i need your help, for 2 different reasons, one to guide/help me re-create my script the way you would do so. The second thing i need help with is (EDIT: i think we'll concentrate on re-creating the script first of all). //BEGIN GETTING BRAND NAMES $connection = mysqli_connect( "$connectip", "$swiftuser", "$swiftpass", "$swiftdb" )or die( "Error " . mysqli_error( $connection ) ); $sql = "SELECT Name, ProgramID FROM programs WHERE Status='1'"; $result = mysqli_query( $connection, $sql )or die( "Error in Selecting " . mysqli_error( $connection ) ); if ( $result->num_rows > 0 ) { while ( $row = $result->fetch_assoc() ) { $ProgramID = $row[ "ProgramID" ]; //BEGIN GETTING CLICKS THAT MONTH FOR THE BRAND $startmonth = date( 'Y-m-d', strtotime( 'first day of this month' ) ); $endmonth = date( 'Y-m-d', strtotime( 'last day of this month' ) ); $todaysdate = date( 'Y-m-d' ); $sqlclicks = "SELECT COUNT(*) AS 'TotalMonth' FROM `clicks` WHERE AffID = '$AffID' AND ProgramID = '$ProgramID' AND DATE(Timestamp) BETWEEN '$startmonth' AND '$endmonth' "; //echo $sqlclicks; $resultclicks = mysqli_query( $connection, $sqlclicks )or die( "Error in Selecting " . mysqli_error( $connection ) ); if ( $resultclicks->num_rows > 0 ) { while ( $rowclicks = $resultclicks->fetch_assoc() ) { $clicks = $rowclicks[ "TotalMonth" ]; } } else { $clicks = "0"; } //END GETTING CLICKS THAT MONTH FOR THE BRAND //BEGIN GETTING REGISTRATIONS THAT MONTH FOR THE BRAND $sqlreg = "SELECT COUNT(*) AS 'TotalReg' FROM `players` WHERE AffID = '$AffID' AND ProgramID = '$ProgramID' AND DATE(SignupDate) BETWEEN '$startmonth' AND '$endmonth' "; //echo $sqlclicks; $resultreg = mysqli_query( $connection, $sqlreg )or die( "Error in Selecting " . mysqli_error( $connection ) ); if ( $resultreg->num_rows > 0 ) { while ( $rowreg = $resultreg->fetch_assoc() ) { $reg = $rowreg[ "TotalReg" ]; } } else { $reg = "0"; } //END GETTING REGISTRATIONS THAT MONTH FOR THE BRAND //BEGIN GETTING FTDs THAT MONTH FOR THE BRAND $sqlftd = "SELECT COUNT(*) AS 'TotalFTDs' FROM `players` WHERE AffID = '$AffID' AND ProgramID = '$ProgramID' AND FTDmatched ='1' AND DATE(FTDmatchedDate) BETWEEN '$startmonth' AND '$endmonth' "; //echo $sqlftd; $resultftd = mysqli_query( $connection, $sqlftd )or die( "Error in Selecting " . mysqli_error( $connection ) ); if ( $resultftd->num_rows > 0 ) { while ( $rowftd = $resultftd->fetch_assoc() ) { $ftds = $rowftd[ "TotalFTDs" ]; } } else { $ftds = "0"; } //END GETTING FTDs THAT MONTH FOR THE BRAND //BEGIN CPA COMMISSION THAT MONTH FOR THE BRAND $sqldeal = "SELECT DealID AS `DealID`, SUM(CASE WHEN FTDmatched = '1' THEN 1 ELSE 0 END) AS `TotalFTDs` FROM players WHERE AffID = '$AffID' AND ProgramID = '$ProgramID' AND `FTDmatchedDate` BETWEEN '$startmonth' AND '$endmonth' GROUP BY DealID"; //echo $sqldeal; $resultdeal = mysqli_query( $connection, $sqldeal )or die( "Error in Selecting " . mysqli_error( $connection ) ); $dealid = array(); $dealftds = array(); $i = 0; if ( $resultdeal->num_rows > 0 ) { while ( $rowdeal = $resultdeal->fetch_assoc() ) { $dealid[ $i ] = $rowdeal[ "DealID" ]; $dealftds[ $i ] = $rowdeal[ "TotalFTDs" ]; $i = $i + 1; } } else { $commissionftd = "0.00"; } $totaldeals = count( $dealid ); //echo "<br>"; //echo "Total Deals for Affilaite is: " . $totaldeals; $sqlpayout = array(); $totalcpa = array(); for ( $tt = 0; $tt < count( $dealid ); $tt++ ) { $sqlamount = "SELECT CPA AS `CPAAmount` FROM affDeals WHERE AffID = '$AffID' AND `affDealID` = '$dealid[$tt]' AND `Type` = 'CPA' AND `StartDate` <= '$todaysdate' AND `EndDate` >= '$todaysdate' OR AffID = '$AffID' AND `affDealID` = '$dealid[$tt]' AND `Type` = 'Hybrid' AND `StartDate` <= '$todaysdate' AND `EndDate` >= '$todaysdate'"; //echo "<br>" . $sqlamount . "<br>"; $resultamount = mysqli_query( $connection, $sqlamount )or die( "Error in Selecting " . mysqli_error( $connection ) ); if ( $resultamount->num_rows > 0 ) { while ( $rowamount = $resultamount->fetch_assoc() ) { $sqlpayout[ $tt ] = $rowamount[ "CPAAmount" ]; $totalcpa[ $tt ] = $sqlpayout[ $tt ] * $dealftds[ $tt ]; } } else { $sqlpayout[ $tt ] = "0.00"; } } //END CPA COMMISSION THAT MONTH FOR THE BRAND $totalcommission = array_sum( $totalcpa ); echo '<tr> <td>' . $row[ "Name" ] . '</td> <td>' . $clicks . '</td> <td>' . $reg . '</td> <td>' . $ftds . '</td> <td>£' . $totalcommission . '</td> </tr>'; } } else { echo "0 results"; } //END GETTING BRAND NAMES ?> I've also attached the SQL tables, the table Affiliates is just the structure so you will need to add a record in there. All other tables have data in them for you to work with. EDIT: since i cant upload SQL files on here you can download them here: https://ufile.io/gnoqf I find it a lot easier for me to learn PHP by trying to create stuff myself, then if i need help i do some research on how to achieve what I'm trying to code and if i fail i keep trying to doing more research until it works. I also ask other PHP developers for advice and help and if they provide me with examples then again that helps me learn a lot better and quicker as i can see first hand how its supposed to look and how it works. I apologise for my messy coding but like i said I'm still learning PHP, if anyone could help me re-create my script more efficiently I'd very much appreciate that. Maybe I've done too much code and certain MySQL queries can be done in just 1 MySQL Query instead of multiple ones, i don't know and that's why I'm here asking for help/advice to help me learn more. Anyway thank you in advance and if you'd like to know any information please let me know.