Jump to content

micky007

Members
  • Content Count

    29
  • Joined

  • Last visited

Community Reputation

0 Neutral

About micky007

  • Rank
    Member

Profile Information

  • Gender
    Male
  • Location
    Standish, Lancashire, UK
  • Age
    28
  1. I've gone ahead and Indexed the Primary Keys too and i still get the error.
  2. 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.
  3. 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.
  4. I'm not sure which Query your wanting me to use. Did you mean this one? OR @Barand
  5. 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
  6. 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.
  7. 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.....
  8. 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?
  9. 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
  10. 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!
  11. micky007

    MySQL JOIN

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

    MySQL JOIN

    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:
  13. micky007

    MySQL JOIN

    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.
  14. micky007

    MySQL JOIN

    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'
  15. micky007

    MySQL JOIN

    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'
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.