My apologies for my English. (sorry) I am struggling to make a query in a custom services 3 function in Drupal 7, which should make 3 sums. My knowledge in mysql and PDO, PPO or mySQLi are very poor. But I can not find documentation to learn how to do this. The theme is more or less like this:

In each table (3) there are 2 records saved with the following values:

The 3 tables:
field_data_field_monto_15pts with field [field_monto_15pts_value] data: 5 and 6
field_data_field_monto_14pts with field [field_monto_14pts_value] data: 3 and 4
field_data_field_monto_13pts with field [field_monto_13pts_value] data: 1 and 2

The services function that performs the query to DB mysql and returns a json:

$cantidad = db_query('SELECT SUM(field_monto_15pts_value) AS 15pts, SUM(field_monto_14pts_value) AS 14pts, SUM(field_monto_13pts_value) AS 13pts FROM {field_data_field_monto_15pts}, {field_data_field_monto_14pts}, {field_data_field_monto_13pts}')
 
 ->fetchAssoc();
return $cantidad;
}

And what I need is for the function to return (in json format) the sum of each field as follows:

{
    "15pts": "11", //adding 5 + 6 records in the field field_monto_15pts_value
    "14pts": "7", //adding 3 + 4 that are in the field field_monto_14pts_value
    "13pts": "3"  //adding 1 + 2 that are in the field field_monto_13pts_value
}

But what returns the function is this!

{
     "15pts": "44", // ???
     "14pts": "28", // ???
     "13pts": "12"  // ???
}

The -> fetchAssoc (); Is the one that gives me the json in the format that suits me best. What is failing here? What am I writing wrong here?

Thank you very much for all the help you can give me. I'm stuck with this

Comments

hondaman900’s picture

You obviously have a multiplier of 4 being introduced somewhere in your code. Check your other supporting code for any such reference.

Try fetching each sum value in it's own separate db_query() to see if the raw individual result returned is correct. Use dpm() to display the result right where it comes back (you'll need to have the Devel module installed) to check if the raw returned data is correct, and go from there.

Also, it helps reading your post if you mark code as CODE and place comments for your code like they show as PHP comments (using "//"), so then the reader doesn't have to figure out what's intended by you as a comment and and what's taken literally as your code. This reads better.

For example:

{
"15pts": "11", //adding 5 + 6 records in the field field_monto_15pts_value
"14pts": "7", //adding 3 + 4 that are in the field field_monto_14pts_value
"13pts": "3" //adding 1 + 2 that are in the field field_monto_13pts_value
}
dmpitu’s picture

Hi hondaman900. Thanks for replying and thanks for the advice (I have edited the post). I have tried with a query with a single sum and the result is correct. I think the problem is in the way of writing the query code.

$cantidad = db_query('SELECT SUM(field_monto_15pts_value) AS 15pts FROM {field_data_field_monto_15pts}')
->fetchAssoc();
return $cantidad;

And this is the request:

{
    "15pts": "11"
}
mmjvb’s picture

You are suffering from what is called Cartesian product. The number of rows is 2x2x2 because of how you specified the query. That explains the factor 4.

Suggest to try SQLFiddle to figure out the correct syntax. Something like below should work. Maybe the AS 15pts needs to move past the end of the inner SELECT.
SELECT (SELECT SUM(field_monto_15pts_value) AS 15pts FROM {field_data_field_monto_15pts}), (SELECT(SUM(field_monto_14pts_value) AS 14pts FROM {field_data_field_monto_14pts}), SUM(field_monto_13pts_value) AS 13pts FROM {field_data_field_monto_13pts}'

As an alternative you could use the UNION to get three rows of sums.

SELECT '15pts' AS pts, SUM(field_monto_15pts_value) AS total FROM {field_data_field_monto_15pts}
UNION SELECT '14pts', SUM(field_monto_14pts_value) FROM {field_data_field_monto_14pts}
UNION SELECT '13pts', SUM(field_monto_13pts_value) FROM {field_data_field_monto_13pts}'
dmpitu’s picture

Hi, mmjvb. Thanks for answering. I have tried with some modification the code that you have given, and it worked! The result is correct. It's already fixed for me! And thanks for the SQLFiddle tool. Fabulous !!! It has been very useful for me!

I have added some aliases in the sums to change the "key" of the json ... Is it well said? So is it said correctly? jajaja!

To change this:

{
     "(SELECT SUM (field_monto_15pts_value) AS 15pts FROM jg_field_data_field_monto_15pts)": "11",
     "(SELECT SUM (field_monto_14pts_value) AS 14pts FROM jg_field_data_field_monto_14pts)": "7",
     "13pts": "3"
}

For this :

{
     "15pts": "11",
     "14pts": "7",
     "13pts": "3"
}

Anyway, this is solved for me! A huge thank you!

The consult was as follows:

$lo_que_che= db_query('SELECT (SELECT SUM(field_monto_15pts_value) AS 15pts FROM {field_data_field_monto_15pts}) 15pts, (SELECT SUM(field_monto_14pts_value) AS 14pts FROM {field_data_field_monto_14pts}) 14pts, 
SUM(field_monto_13pts_value) AS 13pts FROM {field_data_field_monto_13pts}')

->fetchAssoc();
return $lo_que_che;
}

I'm going to celebrate. What a good site drupal.org