Simple SQL select query not pulling any data

[Adamhumbug]

i have the following function

function rebuildQuote($quoteId, $version){
	include 'includes/dbconn.php';
	$sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items
			where quote_id = ':quoteId' and version = ':version'";
	$stmt = $pdo->prepare($sql);
	$bindData = [
		'quoteId' => $quoteId,
		'version' => 1
	];
	$stmt->execute($bindData);
	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
	$out = "";
	if($items){
		foreach($items as $item){
			$out .= "this";
		}
	}else{
		$out .= "no items";
	}

	return $out;
	
}

when i return $quoteId and $version i can see 19,4 so i know that vars are coming into this function.

When i run the SQL manually in the db i get 2 rows of data.

When i run the function as is i get "no items"

I cant see what i am doing wrong here.

Edited June 21, 2022 by Adamhumbug

[ginerjm]

Try dropping the quotes on the args in the query.  The PDO prepare will correct what is necessary

And what are you outputting when there are rows?  It looks like a string of the word 'this' repeatedly.

Edited June 21, 2022 by ginerjm

[Adamhumbug]

6 minutes ago, ginerjm said:

Try dropping the quotes on the args in the query.  The PDO prepare will correct what is necessary

i updated to this with no change

function rebuildQuote($quoteId, $version){
	include 'includes/dbconn.php';
	$sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items
			where quote_id = :quoteId and version = :version";
	$stmt = $pdo->prepare($sql);
	$bindData = [
		'quoteId' => $quoteId,
		'version' => 1
	];
	$stmt->execute($bindData);
	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
	$out = "";
	if($items){
		foreach($items as $item){
			$out .= "this";
		}
	}else{
		$out .= "no items";
	}

	return $out.$version;
	
}

 

[Adamhumbug]

1 minute ago, Adamhumbug said:

i updated to this with no change

function rebuildQuote($quoteId, $version){
	include 'includes/dbconn.php';
	$sql = "SELECT item_id as itemid, quantity, from_date, to_date, price_quoted from quote_items
			where quote_id = :quoteId and version = :version";
	$stmt = $pdo->prepare($sql);
	$bindData = [
		'quoteId' => $quoteId,
		'version' => 1
	];
	$stmt->execute($bindData);
	$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
	$out = "";
	if($items){
		foreach($items as $item){
			$out .= "this";
		}
	}else{
		$out .= "no items";
	}

	return $out.$version;
	
}

 

Actually, that did seem to do the trick - i had been playing with the version definition in the $bindData - when i set that back to $version it worked.

Thanks for that, i had a feeling it would be something small.

Do you know why that has been causing the issue, i have tons of other queries with quotes around them that seem to work ok?

[ginerjm]

I don't know what you were doing differently as per 'version definition'.

And why (again) the strange output of the word 'this' over and over?

[kicken]

9 minutes ago, Adamhumbug said:

Do you know why that has been causing the issue, i have tons of other queries with quotes around them that seem to work ok?

When you use the quotes like in

quote_id = ':quoteId'

You're asking mysql to search for rows where the column quote_id is equal to the value :quoteId, literally.  The quotes prevent it from being used as a parameter placeholder and cause it to be a literal value in the query. 

Removing the quotes allows the :quoteId to be seen as a parameter placeholder and replaced correctly with the bound value when the query is executed.

[mac_gyver]

the version of your query with quotes around the place-holders should have been producing php/sql errors, about the number of bound parameters not matching the query...

do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, and have you set the PDO error mode to use exceptions? also, the error response you get can change depending on an emulated vs true prepared query. have you set the PDO emulated prepared setting to false?

edit: which was mentioned in the replies in your previous PDO based thread - https://forums.phpfreaks.com/topic/314929-pdo-uncaught-error-call-to-a-member-function/

 

Edited June 21, 2022 by mac_gyver

[Barand]

Why are you passing the  $version to the function but always setting the parameter for :version to 1 ?

[Adamhumbug]

16 hours ago, Barand said:

Why are you passing the  $version to the function but always setting the parameter for :version to 1 ?

This was a mistake that was corrected on my last posting.

I origionally did it to check that the issue was not with the params that were passed into the function.