Jump to content

Simple SQL select query not pulling any data


 Share

Go to solution Solved by ginerjm,

Recommended Posts

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.

64057530_Screenshot2022-06-21at17_04_22.thumb.png.516f987f6ce42c7657a34510cca25b9e.png

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

I cant see what i am doing wrong here.

Edited by Adamhumbug
Link to comment
Share on other sites

  • Solution
Posted (edited)

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 by ginerjm
Link to comment
Share on other sites

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;
	
}

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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.

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.