0

PHP 7.0

Server version: 10.0.34-MariaDB-0ubuntu0.16.04.1

This is probably the strangest issue I ever encountered.

My goal is to sum the values from a table if some conditions are met (specific type, type_no and positive value). Amount column is of type double.

This is the query:

SELECT SUM(amount) FROM gl_trans WHERE type='0' AND type_no='1' AND amount>0;

And this is the gl_trans table - it represents the depreciation of fixed asset:

# counter, type, type_no, tran_date, event_date, doc_date, account, memo_, amount, dimension_id, dimension2_id, person_type_id, person_id
'1', '0', '1', '2018-02-28', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'2', '0', '1', '2018-02-28', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'3', '0', '1', '2018-03-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'4', '0', '1', '2018-03-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'5', '0', '1', '2018-04-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'6', '0', '1', '2018-04-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'7', '0', '1', '2018-05-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'8', '0', '1', '2018-05-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'9', '0', '1', '2018-06-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'10', '0', '1', '2018-06-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'11', '0', '1', '2018-07-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'12', '0', '1', '2018-07-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'13', '0', '1', '2018-08-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'14', '0', '1', '2018-08-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'15', '0', '1', '2018-09-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'16', '0', '1', '2018-09-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'17', '0', '1', '2018-10-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'18', '0', '1', '2018-10-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'19', '0', '1', '2018-11-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'20', '0', '1', '2018-11-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'21', '0', '1', '2018-12-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'22', '0', '1', '2018-12-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'

So I did: (db_query and db_fetch_row are custom functions - they are 100% ok)

$sql = "SELECT SUM(amount) FROM gl_trans WHERE type=".db_escape($type)." AND type_no=".db_escape($type_no)." AND amount>0";
$result = db_query($sql, "query for gl trans total");     
$row = db_fetch_row($result);
return $row[0];

The result was null. While debuging I discovered that query for amount column... returns 0: 

$sql = "SELECT amount FROM gl_trans WHERE type=".db_escape($type)." AND type_no=".db_escape($type_no);
$result = db_query($sql, "blah blah blah");
while ($test = db_fetch_assoc($result)) {
  var_dump($test); // [amount] => 0 !!!
}

I pasted the same exact query: 

SELECT SUM(amount) FROM gl_trans WHERE type='0' AND type_no='1' AND amount>0

...into the Workbench and it gave me correct result (22) !

I tried to perform similar queries on different tables (just switching the table and column) and they were ok.
I also tried to sum up another field in this table (person_type_id) and the query was also successful.
Finally we have pasted the code to my coworker's laptop (same database, same system - Ubuntu, same code), and it worked.

Somehow on my computer, with my settings, just this one column in the whole database is always 0 and only if I query for it from the code.
Code is ok - it works on other tables and columns
Db is ok - queries from Workbench and console works.
I suspect some settings issue or possibly some kind of bug but I have no idea what could be wrong.

Improve this question
14
  • What do you get when you var_dump $sql in the last code chunk? Commented Jun 28, 2018 at 13:38
  • 1
    What is db_query() and db_fetch_assoc() and db-fetch_row() they are not vanilla PHP Commented Jun 28, 2018 at 13:39
  • Your script is wide open to SQL Injection Attack Even if you are escaping inputs, its not safe! Use prepared parameterized statements in either the MYSQLI_ or PDO API's Commented Jun 28, 2018 at 13:44
  • As @RiggsFolly mentioned, db_query() is not vanilla PHP, but it is a Drupal method. If you are working in Drupal this would be more appropriate for the Drupal stackexchange, but I can give you a hand writing this out the Drupal way. Just let me know if this is for 7 or 8. Commented Jun 28, 2018 at 13:49
  • 1
    how can we blindly trust your custom functions just because you said "they are ok"? Being skeptical is a a rule of thumb over here. The queries are ok. The rest of the PHP code as well. You say you receive correct result in workbench with the same query. So the custom functions might indeed be the root cause of your issues Commented Jun 28, 2018 at 13:50

2 Answers 2

Reset to default
0

It looks like you are missing single quotes in your query:

$sql = "SELECT SUM(amount) FROM gl_trans WHERE type='".db_escape($type)."' AND type_no='".db_escape($type_no)."' AND amount>0";

Your code is not safe against SQL Injection. You should use PDO or prepared statements instead of the custom functions you have. Read more about it here

Checking your $sql with $var_dump() is also helpful

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, but it's not that. db_escape adds quotes. I also tried this with vanilla mysqli_query and mysqli_fetch_row - same result.
Ah ok. Can you echo or do a var_dump of $sql to see what is being queried? Is it the same query as what you tried in the workbench?
Thanks, I finally found it - take a look at my answer.
0

Question can be closed. The problem was elsewhere - someone mistakenly left a function hidden deep in the code that was voiding all the amounts before my function queried for them. I haven't noticed that due to transaction failure somewhere else.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.