CODEWITHSUNDEEP
phpmysql
Vhal
Vhal

Reputation: 21

Getting the total for two queries in PHP

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();
 ?>

Upvotes: 1

Views: 169

Answers (2)

outis
outis

Reputation: 77400

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
;

Upvotes: 2

Derk Arts
Derk Arts

Reputation: 3460

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?

Upvotes: 0

Related Questions