Adamhumbug

I believe that it is php 7.3.

$opsrch['srch'] contains "(CONCAT(TRIM(op_firstname),' ',TRIM(op_middlenames),' ',TRIM(op_lastname)) like '".apos($sterm)."%' or CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) like '".apos($sterm)."%' or CONCAT(TRIM(op_middlenames),' ',TRIM(op_lastname)) like '".apos($sterm)."%' or op_lastname like '".apos($sterm)."%')";

If it is numeric i get this. select op_id, CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) as op_fullname, op_role from prs_op where (CONCAT(TRIM(op_firstname),' ',TRIM(op_middlenames),' ',TRIM(op_lastname)) like '9999%' or CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) like '9999%' or CONCAT(TRIM(op_middlenames),' ',TRIM(op_lastname)) like '9999%' or op_lastname like '9999%') or op_id='9999' or op_id like '9999%' order by op_firstname, op_lastname limit 40

HI All, I am going through some old code and trying to sanitise sql injection problems and need some help on the best method here. The project does come with some caveats, which i cant change. I have to use Mysqli and cannot use PDO. Because the project is so large, i am going to need to deal with all code changes inside the function and not changing or removing other functions. I always appreciate the help on my personal stuff that i am doing and you guys are amazing but please on this one can we move past the fact that the code is rubbish and old and full of problems. I have a sql query that looks like this - as you can see there are variables in here as well as functions "select op_id, ".$opsrch['op_fullname'].", op_role from prs_op where ".$opsrch['srch'].(is_numeric($srch) ? " or op_id='".$srch."' or op_id like '".$srch."%'" : "")." order by ".$opsrch['sort']." limit 40"; I know how to write a prepared statement but i cannot do this simply as some of the variables contain more sql rather than just a simple variable. Once the sql and all of the variables and functions have been evaluated, this is what i get select op_id, CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) as op_fullname, op_role from prs_op where (CONCAT(TRIM(op_firstname),' ',TRIM(op_middlenames),' ',TRIM(op_lastname)) like 'adam h%' or CONCAT(TRIM(op_firstname),' ',TRIM(op_lastname)) like 'adam h%' or CONCAT(TRIM(op_middlenames),' ',TRIM(op_lastname)) like 'adam h%' or op_lastname like 'adam h%') order by op_firstname, op_lastname limit 40 All references to adam have been passed in from the front end. I have been looking at this -https://stackoverflow.com/questions/56135017/building-a-dynamic-php-prepared-statement-from-user-entry but that is putting everything in one place and im not sure if it can work here. I would really appreciate any help with info to tidy this mess - thanks in advance for helping me with crappy code.

Thanks both, appreciate the point in the right direction.

Hi All, I have a very simple piece of php that is set when a user logs on - it sets a sessin var. At the top of the page i have the following: if (session_status() == PHP_SESSION_NONE) { session_start(); } if(empty($_SESSION['logged-in-user'])){ header("Location: index.php"); exit; } If i comment out the header line the page loads fine, if i dont comment it i get the error message that the page is not redirecting properly. This seems too simple to be wrong but apparently it is - can anyone offer any light on this. For clarity the page that i am redirecting to is the page that this code is on, i am redirecting to the same page (the log on page) Thanks as always.

Hi all, I hope this is the right place to post this. I am trying to learn node.js and i am really struggling to get going. I have a mysql database hosted with ionos and i am trying to just start with a connection. I have the following which is the connection file and i am running it from the command line. var mysql = require('mysql'); var con = mysql.createConnection({ host: "xxxxxxx.hosting-data.io", user: "xxxxx", password: "xxxxx" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); }); I am getting the error: ❯ node db_conn.js /Users/xxxxxxx/Documents/xxxx/Sites/Node/first/db_conn.js:11 if (err) throw err; ^ Error: getaddrinfo ENOTFOUND xxxxxx.hosting-data.io at GetAddrInfoReqWrap.onlookupall [as oncomplete] (node:dns:118:26) -------------------- at Protocol._enqueue (/Users/xxxx/Documents/xxxx/Sites/Node/first/node_modules/mysql/lib/protocol/Protocol.js:144:48) at Protocol.handshake (/Users/xxxx/Documents/xxxx/Sites/Node/first/node_modules/mysql/lib/protocol/Protocol.js:51:23) at Connection.connect (/Users/xxxx/Documents/xxxx/Sites/Node/first/node_modules/mysql/lib/Connection.js:116:18) at Object.<anonymous> (/Users/xxxx/Documents/xxxx/Sites/Node/first/db_conn.js:10:5) at Module._compile (node:internal/modules/cjs/loader:1267:14) at Module._extensions..js (node:internal/modules/cjs/loader:1321:10) at Module.load (node:internal/modules/cjs/loader:1125:32) at Module._load (node:internal/modules/cjs/loader:965:12) at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:83:12) at node:internal/main/run_main_module:23:47 { errno: -3008, code: 'ENOTFOUND', syscall: 'getaddrinfo', hostname: 'xxxxx.hosting-data.io', fatal: true } Node.js v20.0.0 I appreciate this may be difficult to work on and i am sure there is info needed that i have not supplied but i am really struggling to get set up to work. If anyone can help or can suggest resources to help me that would be very much apprecitated. Thanks, As always.

Amazing - thanks. I will try and avoid using them.

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

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)

Absolutely perfect - as always, thank you very much!

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.

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 */;

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

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.

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:

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?

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?

Thanks both for the explanations. **removing endless includes for db from code now**

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?

great advice, thank you for this.

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?

All good points here - you are correct and i have ammended my code.

I think i need to read this a few more times and see if i can make that work. I may be back for this comment. Thanks for taking the time, it is appreciated.

Thank you, this was the answer! 🥱