Greywacke Posted January 12, 2011 Share Posted January 12, 2011 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? Link to comment Share on other sites More sharing options...
justsomeguy Posted January 12, 2011 Share Posted January 12, 2011 It really sounds like the where condition is excluding the rows that you think should be returned. You're saying you copy and paste the exact query that gets printed and then it does return a result? Link to comment Share on other sites More sharing options...
Greywacke Posted January 12, 2011 Author Share Posted January 12, 2011 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. Link to comment Share on other sites More sharing options...
Greywacke Posted January 12, 2011 Author Share Posted January 12, 2011 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... Link to comment Share on other sites More sharing options...
justsomeguy Posted January 12, 2011 Share Posted January 12, 2011 for some reason it seems to be getting a rowcount of 0 when there is 1Well, 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 More sharing options...
Greywacke Posted January 12, 2011 Author Share Posted January 12, 2011 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.and Link to comment Share on other sites More sharing options...
justsomeguy Posted January 12, 2011 Share Posted January 12, 2011 I understand what you're saying, but if a query doesn't return results the only possibility is because the query is not matching any rows. If you remove the where clause, do you get results? Link to comment Share on other sites More sharing options...
Greywacke Posted January 12, 2011 Author Share Posted January 12, 2011 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:and yet it does not. :/ i cannot see a problem with the mysql queries, however they are compiled... Link to comment Share on other sites More sharing options...
Greywacke Posted January 12, 2011 Author Share Posted January 12, 2011 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 More sharing options...
justsomeguy Posted January 12, 2011 Share Posted January 12, 2011 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 More sharing options...
Greywacke Posted January 13, 2011 Author Share Posted January 13, 2011 proper spelling was checked over and over and i never noticed i had the wrong db Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.