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!