Why are prepared statement variables not binding?

Question

I've written a query using the SQL box in PHPMyAdmin (my tool of choice) and the query works well. I've now come to load this into a PHP page, and using the below, I'm getting zero results.

echo $_SESSION['ownerAC']; // 2
echo $_SESSION['versionNo']; // 1    

if ($stmt = $con->prepare("SELECT node.id, node.line_type, (COUNT(parent.id) - 1) AS depth,
        node.visible_index, node.printed_no, node.line_text, node.value_type, node.left_index,
        node.right_index,
        (SELECT COUNT(*) FROM bk_accounts a WHERE a.balance_and_income_line_id = node.id)
        FROM bk_balance_and_income_lines AS parent
        LEFT JOIN bk_balance_and_income_lines AS node ON node.left_index
                BETWEEN parent.left_index AND parent.right_index 
                WHERE node.ownerID = ? AND node.version_no = ? AND parent.ownerID = node.ownerID AND parent.version_no = node.version_no
        GROUP BY node.id ORDER BY node.left_index")) {
        
            $stmt->bind_param('ii', $_SESSION['ownerAC'], $_SESSION['versionNo']);
            
            $stmt->execute();
        
            $tAccountQ = $stmt->get_result();   
            
            $tAccountList = $tAccountQ->fetch_all(MYSQLI_ASSOC); }

However, if I 'hardcode' the variables, in this case '2 and '1' into my query I get a full result set.

Is there a reason why this shouldn't work? Or am I being a bit dumb?

If I throw the following into command line or PHPMyAdmin SQL Console I get results:

SELECT node.id, node.line_type, (COUNT(parent.id) - 1) AS depth,
        node.visible_index, node.printed_no, node.line_text, node.value_type, node.left_index,
        node.right_index,
        (SELECT COUNT(*) FROM bk_accounts a WHERE a.balance_and_income_line_id = node.id)
        FROM bk_balance_and_income_lines AS parent
        LEFT JOIN bk_balance_and_income_lines AS node ON node.left_index
                BETWEEN parent.left_index AND parent.right_index 
                WHERE node.ownerID = '2' AND node.version_no = '1' AND parent.ownerID = node.ownerID AND parent.version_no = node.version_no
        GROUP BY node.id ORDER BY node.left_index

Edit: it's now throwing this error:

PHP Fatal error:  Uncaught mysqli_sql_exception: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'contract_contractr.node.line_type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /home/coop/pages/chartOfAccounts.php:12
Stack trace:
#0 /home/coop/pages/chartOfAccounts.php(12): mysqli->prepare('SELECT node.id,...')
#1 /home/coop/coxxxxxxxxxxx.com/index.php(140): include('/home/coop...')
#2 {main}
  thrown in /home/coop/pages/chartOfAccounts.php on line 12

Which i'm more confused by, as i'm not getting the same error (or any error, i'm just getting results) when in PHPMyAdmin, or when the ? and ? are replaced with '2' and '1'

They're session variables normally, but for ease of displaying here I've just used two integers that are stored in the session variables and should produce a result - no errors being displayed, it just returns to result. my error log is empty. — ryanj, Oct 06 '21 at 15:57
Try using `'ss'` instead. I remember there was a bug in MySQL, maybe you found it too. — Dharman, Oct 06 '21 at 16:05
Try `$stmt->bind_param('ss', ...[2,1]);` and tell me what you get — Dharman, Oct 06 '21 at 16:10
Nothing at all, no error, just the same, no record returned, but as if it's a valid query. I am so baffled by this. — ryanj, Oct 06 '21 at 16:15
Did you try putting them in another variable? Like $a = $_SESSION['whatever'], then use $a in your `bind_param` instead of $_SESSION? — Saphir, Oct 06 '21 at 16:18
Tried both, as it's not throwing anything I'm confused as to what it's doing. It's unique to this statement, too. Which is very odd, as it's suggesting there's something astray with the SQL, but command line is letting it slide, perhaps. — ryanj, Oct 06 '21 at 16:20
tried new variables in bind_param too. $a and $b. Every usual debug test has left me even more confused. I'm actually starting to think I might have found a bug... — ryanj, Oct 06 '21 at 16:22
Make sure you have mysqli error reporting enabled. I see you have an `if` statement so you probably don't have errors enabled. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 06 '21 at 16:24
I feel like there's something you are not telling us. Remove temporarily `bind_param` line and replace `?` with the numbers in your SQL. Do you get the right results then? — Dharman, Oct 06 '21 at 16:27
Yes, removed the ? and put the numbers into SQL and it works right away. — ryanj, Oct 06 '21 at 16:27
This is why it is so confusing, as if the statement has the numbers in the SQL, and bind_param is removed, the whole page works seemlessly and the data is returned. — ryanj, Oct 06 '21 at 16:31
"it's now throwing this error:" Why was it not throwing an error before? What did you change? Did you just enabled error reporting as my first comment said? Do you get this error also when you hardcode the values? — Dharman, Oct 06 '21 at 16:44
There was an include that was disabling error reporting after it was being enabled which I didn't realise. — ryanj, Oct 06 '21 at 16:46
See https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc — ADyson, Oct 06 '21 at 16:52
I just don't understand how it throws an error when using bind_param, but not when the numbers are plugged straight into the SQL. Either its a valid query or it isn't? — ryanj, Oct 06 '21 at 16:57
Thanks for your help @Dharman - I think I'm going to have to go back to the drawing board on this one. — ryanj, Oct 06 '21 at 17:07
mysql> SET GLOBAL sql_mode = ''; CHANGE YOU DB SERVER SQL MODE TO NONE, Hope this will works — Kashif Solangi, Nov 30 '21 at 07:44

Why are prepared statement variables not binding?

0 Answers0