micky007 Posted February 20, 2019 Share Posted February 20, 2019 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 20, 2019 Share Posted February 20, 2019 here's a list of things i saw in the posted code - 1. don't put php variables inside of double-quotes if they are the only thing in the string. 2, don't use or die() for error handling. use exceptions and in most cases let php catch the exception. note: your use of mysqli_error(...) in the connection code won't work because there's no connection to use. 3. don't unconditionally output database errors onto a web page (this will go away when you get rid of the or die() logic.) 4. don't run queries inside of loops. learn to do JOIN queries. 5. if your code is tabbed that far over because it is located inside your html document, you need to put the php code that's responsible for getting/producing data before the start of your html document, fetch the data into appropriately named php variable(s), then use those variable(s) in the html document. 6. handling the negative/failure case is usually shorter then the positive/successful case. if you invert the logic tests and handle the negative/failure condition first, your code will be clearer. you won't have logic for the negative/failure case 10's/100's of lines later in the code. 7. don't use loops to fetch what will be at most one row of data. just directly fetch the single row of data. 8. don't put static calculations inside of loops. the various date values shouldn't change during one report (where they are at now, they will if the request spans midnight.) put them before the start of the loop. 9. don't put quotes around numbers. 10. don't selected things that are not used and don't create variables that are not used (given the amount of code, the cases i saw of this may not be accurate.) 11. if you are looping to do something (should go away with JOINed queries), don't evaluate count() statements in the loop. determine the loop count, once, before the start of the loop. 12. doing some of these things will simplify variable naming. you won't have to think up unique names for variables because you will only have one instance in the code. 13. the $AffID is probably from external/unknown data. you should NOT put eternal/unknown data directly into an sql query statement. use a prepared query, with a place-holder for each data value, then supply the data when the query gets executed. switching to the much simpler php PDO extension will make using prepared queries easy compared to the php mysqli extension. 2 Quote Link to comment Share on other sites More sharing options...
micky007 Posted February 25, 2019 Author Share Posted February 25, 2019 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> </td> <td> </td> </tr> <tr> <th scope="row">Statistics</th> <td> </td> <td> </td> </tr> <tr> <th scope="row">Creatives</th> <td> </td> <td> </td> </tr> <tr> <th scope="row"> </th> <td> </td> <td> </td> </tr> <tr> <th scope="row"> </th> <td> </td> <td> </td> </tr> <tr> <th scope="row"> </th> <td> </td> <td> </td> </tr> <tr> <th scope="row"> </th> <td> </td> <td> </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 } ?> 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.