Getting no data with if($stmt -> fetch())

[Adamhumbug]

I have the following function

function getBallInOver($gameId){
	include 'includes/dbconn.php';
	$sql = "SELECT MAX(id), over_number, ball_in_over from deliveries where game_id = ?";
	$stmt = $conn->prepare($sql);
	$stmt->bind_param('i', $gameId);
	$stmt->execute();
	$stmt->bind_result($overno, $ballinover);
	if($stmt->fetch()){
		$out = $overno;
	}
	$stmt -> close();
	return $out;
}

which is called from this function

function newDelivery($gameId, $runs, $fairDelivery){
	include 'includes/dbconn.php';
	$batterId = getOnStrikeBatter($gameId);
	$ballInformation = getBallInOver($gameId);

	$one = 1;
	$sql = "INSERT into deliveries (game_id, on_strike_batter_id, runs_scored, fair_delivery, over_number) values (?,?,?,?,?)";
	$stmt = $conn->prepare($sql);
	$stmt -> bind_param('iiiii', $gameId, $batterId, $runs, $fairDelivery, $ballInformation);
	$stmt -> execute();

	if($runs % 2 !== 0){
		include 'includes/dbconn.php';
		$zero = 0;
		$one = 1;
		$sql = "UPDATE batter_in set on_strike=(case when on_strike = ? then ? else ? end)";
		$stmt = $conn->prepare($sql);
		$stmt -> bind_param('iii', $zero, $one, $zero);
		$stmt -> execute();

	}
	return;
}

I am getting an error that the over_number cannot be null.

I dont understand why the value is null - when i run the sql directly on the server i get the following:

MAX(id) = 42, over_number = 1, ball_in_over = 1

Could someone point me in the right direction.

For clarity, the function above its call takes the same peramaters and that works fine so i know the function is getting the data it needs.

[gw1500se]

Did you print $ballInformation just before the execute to make sure it contains what you think?

[ginerjm]

YOu are returning the max of id from your function but then using it (it seems) as the overno value instead of the id value.

[Barand]

You need to turn the mysql error reporting.

Your function's query gets 3 result columns and binds ony 2 of them.

Quote

PHP Fatal error: Uncaught ArgumentCountError: Number of bind variables doesn't match number of fields in prepared statement

Also the over number and ballinover values will be meaningless in that query as it uses an aggregation function.

[maxxd]

I assume this is mysqli because insofar as I recall PDO doesn't have a `bind_result` method. Assuming I'm not off-base there, I'd recommend taking the plunge and switching to PDO if it's a possibility in your project - it's a much more fluent and easier to understand interface.

Edited May 6, 2022 by maxxd

[ginerjm]

We've presented our opinion on PDO already.  To no avail.

[Adamhumbug]

On 5/6/2022 at 8:02 PM, Barand said:

You need to turn the mysql error reporting.

Your function's query gets 3 result columns and binds ony 2 of them.

Also the over number and ballinover values will be meaningless in that query as it uses an aggregation function.

Yes, this seems to be the issue - i was only using 2 of the values - rookie.

I need to look more into the error reporting for sure - thanks all for your input as always.

 

PDO will be something that i will look into but wanted to start with it on a new project - it will come.

[ginerjm]

PDO is nothing to pick up.  It is real easy to use and simpler than mysqli.

(call your connection function to create the pdo handle)
$pdo = PDOConnect($db_name);	// do the connection and assign the database name

// write your query separately to make it easier to echo should you need to debug it
// This should happen outside of any loop you may be doing on your db 
$q = "insert into MyTable (order_number, column1, column2, column3)
		values(:ord_num, :col1, :col2, :col3)";
$qst = $pdo->prepare($q);

//  define the arguments you used in your query here which would be inside of any loop you are using to update the table
$parms = array(
		'ord_num'=>$sel_id,
		'col1'=>$value1,
		'col2'=>$value2,
		'col3'=>$value3
		);
//  now run the query with the current values assigned, testing for the result at the same time
if(!$qst=>execute($parms))
{
	(show an error message here)
	(leave script or whatever)
}
//  done with any loop doing the querying
//  continue with handling query results now

If you want I'll post my sample connection script.