Jump to content

Adamhumbug

Members
  • Posts

    357
  • Joined

  • Last visited

Recent Profile Visitors

3,419 profile views

Adamhumbug's Achievements

Advanced Member

Advanced Member (4/5)

1

Reputation

6

Community Answers

  1. Amazing - thanks. I will try and avoid using them.
  2. Answered my own question. Apologies for my impatience. SELECT p.first_name , p.last_name , p.id as playerid , sum(runs_scored) as runs , count(runs_scored) as balls , (Select count(runs_scored) from deliveries where runs_scored = 4 and deliveries.on_strike_batter_id = p.id) as fours FROM game g JOIN player p ON g.batting_first = p.team_id JOIN deliveries d ON p.id = d.on_strike_batter_id AND g.id = d.game_id WHERE g.id = ? GROUP BY p.id
  3. Just as an extension to this. I am also wanting to count how many times each player has scored 4 runs. I thought the following sub qry might do it but alas it is giving me the same number for each player. SELECT p.first_name , p.last_name , p.id as playerid , sum(runs_scored) as runs , count(runs_scored) as balls , (Select count(runs_scored) from deliveries where runs_scored = 4) as fours FROM game g JOIN player p ON g.batting_first = p.team_id JOIN deliveries d ON p.id = d.on_strike_batter_id AND g.id = d.game_id WHERE g.id = ? GROUP BY p.id would you be so kind as to point me in the right direction for extending your original sql (you will see some extensions that are working per player)
  4. Absolutely perfect - as always, thank you very much!
  5. I am assuming that it is possible to have 100 or more players in a team. But the game can only have 11 per side. The squad is the 11 players that have been picked per team to play for that team in that game.
  6. apologies, i do have this. CREATE TABLE `team` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `team` -- INSERT INTO `team` (`id`, `name`) VALUES (1, 'Town CC 1XI'), (2, 'Other Test CC 1XI'); -- -- Indexes for dumped tables -- -- -- Indexes for table `team` -- ALTER TABLE `team` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `team` -- ALTER TABLE `team` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  7. Thanks for this Barand. CREATE TABLE `game_squad` ( `id` int(11) NOT NULL, `game_id` int(11) NOT NULL, `player_id` int(11) NOT NULL, `wicket_keeper` tinyint(1) NOT NULL, `captain` tinyint(1) NOT NULL, `batting_number` tinyint(2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `game_squad` -- INSERT INTO `game_squad` (`id`, `game_id`, `player_id`, `wicket_keeper`, `captain`, `batting_number`) VALUES (14, 51, 1, 0, 0, 0), (15, 51, 6, 0, 0, 0), (16, 51, 3, 0, 0, 0), (17, 51, 11, 0, 0, 0), (18, 51, 2, 0, 0, 0), (19, 51, 7, 0, 0, 0), (20, 51, 5, 0, 0, 0), (21, 51, 10, 0, 0, 0), (22, 51, 9, 0, 0, 0), (23, 51, 12, 0, 0, 0), (24, 51, 8, 0, 0, 0), (25, 51, 20, 0, 0, 0), (26, 51, 21, 0, 0, 0), (27, 51, 17, 0, 0, 0), (28, 51, 16, 0, 0, 0), (29, 51, 14, 0, 0, 0), (30, 51, 18, 0, 0, 0), (31, 51, 13, 0, 0, 0), (32, 51, 15, 0, 0, 0), (33, 51, 19, 0, 0, 0), (34, 51, 23, 0, 0, 0), (35, 51, 22, 0, 0, 0), (36, 52, 20, 0, 0, 0), (37, 52, 21, 0, 0, 0), (38, 52, 17, 0, 0, 0), (39, 53, 20, 0, 0, 0), (40, 53, 21, 0, 0, 0), (41, 53, 17, 0, 0, 0), (42, 56, 1, 0, 0, 0), (43, 56, 6, 0, 0, 0), (44, 56, 3, 0, 0, 0), (45, 56, 20, 0, 0, 0), (46, 56, 21, 0, 0, 0), (47, 56, 17, 0, 0, 0); -- -- Indexes for dumped tables -- -- -- Indexes for table `game_squad` -- ALTER TABLE `game_squad` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `game_squad` -- ALTER TABLE `game_squad` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; CREATE TABLE `game` ( `id` int(11) NOT NULL, `home_team` int(11) NOT NULL, `away_team` int(11) NOT NULL, `date` date NOT NULL, `result_id` int(11) DEFAULT NULL, `venue` text NOT NULL, `toss_won_by` int(11) DEFAULT NULL, `batting_first` int(11) DEFAULT NULL, `bowling_first` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `game` -- INSERT INTO `game` (`id`, `home_team`, `away_team`, `date`, `result_id`, `venue`, `toss_won_by`, `batting_first`, `bowling_first`) VALUES (51, 1, 2, '2022-02-01', NULL, 'Avenue Park', 1, 2, 1), (55, 1, 2, '2022-11-06', NULL, 'TEST', 2, 1, 2), (56, 1, 2, '2022-11-06', NULL, 'adam', NULL, NULL, 0); -- -- Indexes for dumped tables -- -- -- Indexes for table `game` -- ALTER TABLE `game` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `game` -- ALTER TABLE `game` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=57; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; CREATE TABLE `player` ( `id` int(11) NOT NULL, `team_id` int(11) NOT NULL, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, `player_type_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `player` -- INSERT INTO `player` (`id`, `team_id`, `first_name`, `last_name`, `player_type_id`) VALUES (1, 1, 'Adam', 'Hewitt', 4), (2, 1, 'Jack', 'Farmer', 4), (3, 1, 'Chuggy', 'Jones', 4), (4, 1, 'Sean', 'Baines', 3), (5, 1, 'James', 'Rist', 1), (6, 1, 'Brett', 'Lee', 1), (7, 1, 'James', 'Anderson', 1), (8, 1, 'Sean', 'Smith', 2), (9, 1, 'Ricky', 'Ponting', 2), (10, 1, 'Justin', 'Langer', 2), (11, 1, 'Ian', 'Bell', 2), (12, 1, 'Robert', 'Key', 2), (13, 2, 'John', 'Smith', 2), (14, 2, 'James ', 'Johnson', 3), (15, 2, 'Lawrence', 'Stroll', 4), (16, 2, 'Ian', 'Chapell', 2), (17, 2, 'Graham', 'Gooch', 2), (18, 2, 'Jaques', 'Kallis', 1), (19, 2, 'Maddison', 'Muttiah', 2), (20, 2, 'Brian', 'Lara', 1), (21, 2, 'Freddie', 'Flintoff', 4), (22, 2, 'Simon', 'Jones', 4), (23, 2, 'Richard', 'Bull', 4); -- -- Indexes for dumped tables -- -- -- Indexes for table `player` -- ALTER TABLE `player` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `player` -- ALTER TABLE `player` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; CREATE TABLE `deliveries` ( `id` int(11) NOT NULL, `game_id` int(11) NOT NULL, `on_strike_batter_id` int(11) NOT NULL, `runs_scored` int(11) NOT NULL, `fair_delivery` tinyint(1) NOT NULL, `over_number` int(11) NOT NULL, `ball_in_over` int(11) NOT NULL, `over_called_this_ball` tinyint(1) NOT NULL, `wicket` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `deliveries` -- INSERT INTO `deliveries` (`id`, `game_id`, `on_strike_batter_id`, `runs_scored`, `fair_delivery`, `over_number`, `ball_in_over`, `over_called_this_ball`, `wicket`) VALUES (126, 51, 21, 1, 1, 1, 1, 0, 0), (127, 51, 20, 1, 1, 1, 1, 0, 0), (128, 51, 21, 1, 1, 1, 1, 0, 0), (129, 51, 20, 2, 1, 1, 1, 0, 0), (130, 51, 20, 2, 1, 1, 1, 0, 0), (131, 51, 20, 2, 1, 1, 1, 0, 0), (132, 51, 20, 2, 1, 1, 1, 0, 0), (133, 51, 20, 3, 1, 1, 1, 0, 0), (134, 51, 21, 3, 1, 1, 1, 0, 0), (135, 51, 20, 1, 1, 1, 1, 0, 0), (136, 51, 21, 1, 1, 1, 1, 0, 0), (137, 51, 20, 3, 1, 1, 1, 0, 0), (138, 51, 21, 3, 1, 1, 1, 0, 0), (139, 51, 20, 6, 1, 1, 1, 0, 0), (140, 51, 20, 6, 1, 1, 1, 0, 0), (141, 51, 20, 1, 1, 1, 1, 0, 0), (142, 51, 21, 6, 1, 1, 1, 0, 0), (143, 51, 21, 6, 1, 1, 1, 0, 0), (144, 51, 21, 4, 1, 1, 1, 0, 0), (145, 51, 21, 4, 1, 1, 1, 0, 0), (146, 51, 21, 1, 1, 1, 1, 0, 0), (147, 51, 20, 4, 1, 1, 1, 0, 0), (148, 51, 20, 4, 1, 1, 1, 0, 0), (149, 51, 20, 4, 1, 1, 1, 0, 0), (150, 51, 20, 4, 1, 1, 1, 0, 0), (151, 51, 20, 4, 1, 1, 1, 0, 0), (152, 51, 20, 4, 1, 1, 1, 0, 0), (153, 51, 20, 4, 1, 1, 1, 0, 0), (154, 51, 20, 1, 1, 1, 1, 0, 0), (155, 51, 21, 4, 1, 1, 1, 0, 0), (156, 51, 21, 4, 1, 1, 1, 0, 0), (157, 51, 21, 4, 1, 1, 1, 0, 0), (158, 51, 21, 4, 1, 1, 1, 0, 0); -- -- Indexes for dumped tables -- -- -- Indexes for table `deliveries` -- ALTER TABLE `deliveries` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `deliveries` -- ALTER TABLE `deliveries` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=159; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; What i am wanting to get out of this as a structure is the following: First Name Last Name ID SumOfRunsScored I will be adding more to this such as number of 4's scored, number of 6's scored, number of deliveries faced, strike rate etc but happy to do that work myself following your assistance. This is for a cricket scoring app if that gives you any greater insight. Let me know if this doesnt give you what you need. Thanks Again
  8. Hi All, I have a query that selects people from my DB SELECT player.first_name, player.last_name, player.id FROM game_squad inner join player on game_squad.player_id = player.id inner join game on 51 = game.id where game_id = 51 and team_id = game.batting_first The ids that i get in this query, i need to use to do some sums on other data. I amended the suery to be SELECT player.first_name, player.last_name, player.id, (Select sum(deliveries.runs_scored) from deliveries where deliveries.on_strike_batter_id = 20) FROM game_squad inner join player on game_squad.player_id = player.id inner join game on 51 = game.id where game_id = 51 and team_id = game.batting_first the sub query value that is currently 20, needs to be the ID per row that has been selected from the first part of the query. Is this possible? If my explanation is not clear enough or more info required around table structure, i will be happy to provide.
  9. So if the user selects a client from a dropdown (named clientName) then there will be an id available, if the user selects add new client, the dropdown is replaced with an input named clientNameNew. So one of the two will be posted but there is no way of knowing which it will be. But ultimately, as set up, i dont know if there will be a post value of clientNewName and need to do something different if it is posted from if an existing client is selected from the list. The reading that i have done suggests that isset is the way to go and shouldnt produce a warning, but i am still getting one:
  10. ok this is interesting. On first try i am getting "Unknown column 'job_id' in 'from clause'" but there isnt a job_id in the from clause?
  11. I have a from that posts when i click submit, one of the inputs is created if you create a new client rather than picking one from a select. If creating a new client, an id will not be passed in and therefore will be undefined. I thought i had handled it with the following but i am getting a notice of undefined index on "clientNameNew" //if createing a new client name if ($_POST['clientNameNew']) { $clientName = $_POST['clientNameNew']; $clientId = createNewClient($pdo, $clientName); } else { $clientId = $_POST['clientName']; } Is there a better way of handling this than the way i have above?
  12. Thanks both for the explanations. **removing endless includes for db from code now**
  13. I have a php function that is called via ajax: function getItemAttributesById($itemId){ require 'includes/dbconn.php'; $sql = "SELECT id, name, type, price_uk, price_us, price_ca, price_au, price_eu, billing_freq from item where id = :itemId"; $stmt = $pdo->prepare($sql); $bindData = [ 'itemId' => $itemId ]; $stmt->execute($bindData); $item = $stmt->fetch(PDO::FETCH_ASSOC); echo json_encode($item); } when i echo the $item at the end of the function and console.log the response in ajax, i can see the array. When i use the return keyword instead of echo i just see the word "Array". From my understanding of what they do, i wouldnt have thought there would have been a difference in what got back to the ajax function. Am i using it wrong?
  14. for this do you mean that function getItemsForQuote($itemType, $currency, $qty = null, $itemId = null, $startDate = null, $endDate = null,$priceQuoted = null, $notes = null) should be replaced with one array containing all of this data rather than lots of individual elements?
×
×
  • 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.