Jump to content

debugging, all seems fine - except that it returns an empty recordset!


Greywacke
 Share

Recommended Posts

it spits out the correct query string values, and the correct sql query - when testing in pma it gets the value i am selecting!this is supposed to return the price including vat, from the given product code:Retail Including VATand this is supposed to return the price including var - a % discount:Retail Including - Discountyet when i test the sql queries generated in PMA, it selects the value required!this is the faulty piece of code, and for the life of me - i cannot find the fault.

// some irrelevant linesif ($q < 50) {// some more irrelevant lines} else {	$tsql = "SELECT 		CASE ".$q." 		WHEN 50 THEN 									# Retail Including VAT			(28_pricelists.text_RetailExcluding * 1.14) 		WHEN 51 THEN 									# Retail Including VAT + Discount			((28_pricelists.text_RetailExcluding * 1.14) * (1 - ".$n." / 100)) 		END 		AS text_Result 	FROM 		28_pricelists 	WHERE 		28_pricelists.text_PartNumber = \"".$p."\" 	LIMIT 1;";	$result = mysql_query_errors($tsql , $conn , __FILE__ , __LINE__ , true );	if ($result) {		if ($row = mysql_fetch_array($result)) {			echo number_format(round($row["text_Result"],2),2);		}		mysql_free_result($result);	}}echo "\n\nERROR ".mysql_errno($conn).": ".mysql_error($conn)." (s=".$s."&q=".$q."&d=".$d."&p=".$p."&n=".$n.")\n";echo "	<sql>".join("\n",$sql)."</sql>\n";mysql_close($conn);?>

the last two echo's print the following text:

ERROR 0:  (s=141&q=50&d=#&p=PZN65D0005CC&n=0)	<sql>SELECT 		CASE 50 		WHEN 50 THEN 									# Retail Including VAT			(28_pricelists.text_RetailExcluding * 1.14) 		WHEN 51 THEN 									# Retail Including VAT + Discount			((28_pricelists.text_RetailExcluding * 1.14) * (1 - 0 / 100)) 		END 		AS text_Result 	FROM 		28_pricelists 	WHERE 		28_pricelists.text_PartNumber = "PZN65D0005CC" 	LIMIT 1;NOTICE #0500: An empty result set was returned by the query defined in /home/dwtphovu/public_html/intellisource.co.za/qtpq.php on line 213</sql>

and

ERROR 0:  (s=141&q=51&d=#&p=PZN65D0005CC&n=5)	<sql>SELECT 		CASE 51 		WHEN 50 THEN 									# Retail Including VAT			(28_pricelists.text_RetailExcluding * 1.14) 		WHEN 51 THEN 									# Retail Including VAT + Discount			((28_pricelists.text_RetailExcluding * 1.14) * (1 - 5 / 100)) 		END 		AS text_Result 	FROM 		28_pricelists 	WHERE 		28_pricelists.text_PartNumber = "PZN65D0005CC" 	LIMIT 1;NOTICE #0500: An empty result set was returned by the query defined in /home/dwtphovu/public_html/intellisource.co.za/qtpq.php on line 213</sql>

yet - as mentioned, in PMA it returns 13580.1930 for the first query and 12901.18335000 for the second. i think something might be amiss in my mysql_query_errors function, here it is:

function mysql_query_errors($sql, $conn, $doc, $line, $expret = false) {	$line--;	$err = "";	$result = mysql_query($sql);	if (mysql_errno() > 0) {		$err = "ERROR #".mysql_errno().": ".mysql_error()." in ".$doc." on Line ".$line;	} elseif ($expret) {		if (is_null($result)) {			$err = "NOTICE #0504: A null value was returned by the query defined in ".$doc." on line ".$line--;		} elseif ($result === false) {			$err = "NOTICE #0502: A false value was returned by the query defined in ".$doc." on line ".$line--;		} elseif ($result === true) {			$err = "NOTICE #0501: A true value was returned by the query defined in ".$doc." on line ".$line--;		} elseif (mysql_num_rows($result) < 1) {			$err = "NOTICE #0500: An empty result set was returned by the query defined in ".$doc." on line ".$line--;		}		array_push($GLOBALS["sql"],$sql."\n",$err."\n");	}	return $result;}

i am afraid i cannot see it - does anyone else perhaps see it?

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

yes. for some reason it seems to be getting a rowcount of 0 when there is 1 0o

} elseif (mysql_num_rows($result) < 1) {$err = "NOTICE #0500: An empty result set was returned by the query defined in ".$doc." on line ".$line--;}

ps: also please note that the partnumber is exactly the same on both requests for price incl vat and price excl vat. it is also spat out by the page, saying it returns no records - however it does return one when tested in PMA.

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

another interesting note, try request a different part number - such as RON504 instead of PZN65D0005CC with whatever discount value ($n) and it retrieves the values! however - PZN65D0005CC DOES exist in the table! :/Incl VAT - % DiscountorIncl VATfrustrating this is indeed...

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

for some reason it seems to be getting a rowcount of 0 when there is 1
Well, that's not true. The rowcount is in fact 0. If it were 1, it would report 1. The question is why is the rowcount 0. The only way the rowcount would be 0 is either if the table has no records, or the where clause is excluding all of them. If both of those weren't true then it would return at least one record. You need to identify the differences between when you run the query in PMA and when the site runs it. If the two methods have different results, then the cause is in one of the differences between the two.The only code suggestion I would make is with the $conn object. I assume that's the database connection, but you're not using it in your wrapper function. I would change that so that all of the mysql functions use the connection object.
Link to comment
Share on other sites

what i mean by rowcount is the amount of rows in the resultset from the query. there is one when run in PMA, but not from that php document qtpq.php...as can be seen here in these screenshots of Query A and Query B in PMA.qtpq_0.jpgandqtpq_1.jpg

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

yes i do. and even if i request other product codes, it works. it is supposed to limit to 1 in those requests - but looks more like its doing an involuntary limit 0 of sorts. that record exists in the database, as the screenshots prove to the % of the query you can see that was copied and pasted from the pages.unfortunately its bedtime for me, got a meeting first thing in the morning - will respond here when i get back - even if something comes to light :)what puzzles me though, is that it should be selecting the following record:qtpq_2.jpgand yet it does not. :/ i cannot see a problem with the mysql queries, however they are compiled...

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

oh my word! my focus and clarity was not where it should have been :/i was viewing a different db in pma! test instead of production!*writes note to take more ginkgo biloba and highten the water intake* :)this issue is now RESOLVED :)

Link to comment
Share on other sites

Always assume the computer is correct. If it's telling you there aren't any records when you think there should be, look at the details. Things like the database you're using and the proper spelling of the things you're searching for should be the first things to check.

Link to comment
Share on other sites

proper spelling was checked over and over and i never noticed i had the wrong db :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...