Jump to content

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


Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

Posted (edited)

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 by maxxd
  • Great Answer 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.   

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • 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.