Getting the total for two queries in PHP

Question

I'm tracking costs to clients by session and by items specific to each session. I'm trying to get the total session costs and session item costs (cost * count from tbl_sessionitem). But when I check the results, the code outputs the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Here are my tables:

CREATE TABLE tbl_session (
    `clientid` INT UNSIGNED NOT NULL,
    `sessioncost` DECIMAL(6,2) NOT NULL,
    `datetoday` DATETIME NOT NULL,
);

CREATE TABLE tbl_sessionitem (
    `clientid` INT UNSIGNED NOT NULL,
    `cost` DECIMAL(6,2) NOT NULL,
    `count` INT UNSIGNED NOT NULL,
    `datetoday` DATETIME NOT NULL
);

Here is my php code:

 <?php

 $date=$_POST['date'];
 mysql_connect("localhost","root","");
 mysql_select_db("database");
 $sql=mysql_query("
     SELECT id
          , SUM(tbl_session.sessioncost) AS 'totalcost'
          , SUM(tbl_sessionitem.count) * SUM(tbl_sessionitem.cost) AS 'totalquantitycost'
       FROM (
             SELECT clientid
                  , sessioncost
               FROM tbl_session
               WHERE datetoday = ('$date')
           UNION ALL
             SELECT clientid
                  , cost
              , count
               FROM tbl_sessionitem
             WHERE datetoday = ('$date')
         )
     GROUP BY id");


 while($row = mysql_fetch_array($sql))
 {
     echo $row['totalcost'];
     echo $row['totalquantitycost'];
 }
 mysql_close();
 ?>

but the second table name you have mention above is "tbl_sessioncost" just try with that — Manigandan Arjunan, Dec 28 '11 at 07:29
@Ampere my mistake sir. i already modified it to tbl_sessionitem — Vhal, Dec 28 '11 at 07:34
The sample code is vulnerable to [SQL injection](http://unixwiz.net/techtips/sql-injection.html), which is a very serious [security risk](http://bobby-tables.com/). To fix this hole, switch from the outdated mysql extension to [PDO](http://php.net/PDO) and use [prepared statements](http://www.php.net/PDO.prepared-statements), passing values as parameters to the statement rather than interpolating them directly into the string. If you need a PDO tutorial, try ["Writing MySQL Scripts with PHP and PDO"](http://www.kitebird.com/articles/php-pdo.html). The site you save may just be your own. — outis, Dec 28 '11 at 09:02
Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and (when giving sample data) `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Dec 28 '11 at 09:13
Columns clientid and datetoday in table tbl_sessionitem are redundant. Instead, the two tables should have their own [surrogate keys](http://en.wikipedia.org/wiki/Surrogate_key), and tbl_sessionitem should have a column that's a foreign key to tbl_session. Then, use joins rather than unions (and subselects) to get data from both tables. — outis, Dec 28 '11 at 09:17
possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias), [mysql union question](http://stackoverflow.com/questions/912285/), [Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource](http://stackoverflow.com/questions/938959/), [mysql query occasionally returns nothing](http://stackoverflow.com/questions/1158576/) and many, many others. — outis, Dec 28 '11 at 10:04

outis · Answer 1 · 2011-12-28T09:47:37.227

The warning means what it said: the value passed to mysql_fetch _array isn't a result. mysql_query returns a mixed value; when the query fails, it returns false. You need to perform error checking. mysql_error will give you an error message from MySQL, though be careful never to output database error messages to non-admins.

If you had done that, you would have seen a number of problems:

the subselect result must be given an alias.
the selects being UNIONed have a different number of columns
there's no column named "id" in the subselect results.
the aggregate functions reference the tables from the subselect, but the outer select can only access the result table (the one missing an alias).

Even if you fix those SQL errors, the query itself won't give the results you're looking for, due to the way grouping and aggregate functions work.

There's a much better approach. Session items are associated with sessions, but in the schema this association is loose, via the datetoday column. As a result, you have the odd use of unions. Instead, create surrogate keys for the tables and give the session items table a column that refers to the session table. While you're at it, drop the redundant "tbl_" prefix.

CREATE TABLE sessions (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    client INT UNSIGNED NOT NULL,
    cost DECIMAL(5,2),
    `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    FOREIN KEY (client) REFERENCES clients (id)
) Engine=InnoDB;

CREATE TABLE session_items (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    session INT UNSIGNED NOT NULL,
    cost DECIMAL(5,2),
    `count` INT UNSIGNED,
    FOREIN KEY (session) REFERENCES sessions (id)
) Engine=InnoDB;

To get the total session cost and quantity cost for a given day, you can use a subquery to get the quantity cost for a session (necessary to prevent including session costs multiple time in the totalcost sum), then sum the session and quantity costs in an outer query for each client's total costs for a given day.

SELECT client, 
       SUM(cost) AS totalcost, 
       SUM(quantitycost) AS totalquantitycost
  FROM (
        SELECT client, 
               sessions.cost,
               SUM(session_items.`count`) * SUM(session_items.cost) AS quantitycost
          FROM sessions
            JOIN session_items ON sessions.id=session_items.session
          WHERE sessions.`date` = NOW()
          GROUP BY sessions.id
  ) AS session_invoices
  GROUP BY client
;

score 0 · Answer 2 · answered Dec 28 '11 at 07:26

0

COUNT is not to be used as a Column name, it's a function, it's used like this:

Select COUNT(id) as countOfId FROM table

Also, I would recommend doing all those calculations in PHP, much easier to maintain and probably better performance, MySql isn't meant as a calculator.

If you want to use reserved keywords as column names, you need to add backticks and don't write them in capitals because that decreases readability in this case:

Select `count` from table

And what is COST?

answered Dec 28 '11 at 07:26

Derk Arts

3,432
2
18
36

Sir. i need to change the column name "COUNT" into a new name – Vhal Dec 28 '11 at 07:29
Like for example cost is 20.00, need to be multiplied with the count. For example. 20.00 * 5 so the total cost is 100.00. Count is the quantity. – Vhal Dec 28 '11 at 07:30
Please rewrite this query and only select the data, do not do any calculations. In PHP this will be easy. – Derk Arts Dec 28 '11 at 07:40
From the [documentation](http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html): "Names of built-in functions are permitted as identifiers but may require care to be used as such. For example, COUNT is acceptable as a column name." Despite this, it's still a good idea to quote such column names with backticks. – outis Dec 28 '11 at 09:09
@saratis Are you suggesting instead of asking MySQL to count the number of rows in a table, you should fetch all these rows? – Michael Mior Dec 28 '11 at 10:24

Getting the total for two queries in PHP

2 Answers2