nested / subqueries

[rycore]

Hey guys am new to this forum i've been trying to resolve this issue for a while now

 

I recently changed my mysql database so i can display payment history to customers

 

Am having issue's resolving the code for the admin side.

 

my original code goes like this

$Query = mysql_query("SELECT `sheet`.`Id`, `sheet`.`Name`, `sheet`.`Last Payment`,
            (SELECT 
            
            SUM(`Amount`/`Weeks`*4)
            
            FROM `item` WHERE `Sheet Id` = `sheet`.`Id` AND `Amount` != `Paid` AND `Date` <= $Date) AS `Monthly`,
            
            (SELECT 
            SUM(`Amount`-`Paid`)
            FROM `item` WHERE `Sheet Id` = `sheet`.`Id` AND `Amount` != `Paid` AND `Date` <= $Date) AS LeftToPay,
            
            (SELECT
            SUM(`Paid`) 
            FROM `item` WHERE `Sheet Id` = `sheet`.`Id` AND `Amount` != `Paid` AND `Date` <= $Date) AS TotalPaid,
            
            (SELECT 
            MAX((`Amount`-`Paid`)/(`Amount`/`Weeks`))
            FROM `item` WHERE `Sheet Id` = `sheet`.`Id` AND `Amount` != `Paid` AND `Date` <= $Date) AS Max
            
            FROM `sheet` GROUP BY `sheet`.`Id`")  or die(mysql_error()); 

Now this worked no problem but now i changed my mysql db to the fallowing

 

//Customers Details
Table: customer
  Field: Id
  Field: Name
  Field: Password

//Item information
Table: item
  Field: Id
  Field: Customer Id
  Field: Date
  Field: Name
  Field: Weeks
  Field: Price

//payment history
Table: payment
Field: Id
Field: Customer Id
Field: Item Id
Field: Date
Field: Amount

 

At first i thought perhaps a subquery where i nest the (SELECT) in itself

I've tried multiple ways of perhaps resolving this and this below code is the closest i've got.

mysql_query("
SELECT

`C`.`Id` AS CID, 
`C`.`Name` AS CNAME, 

(SELECT (SELECT MAX(`Date`) FROM `payment` WHERE `Item` = `item`.`Id`) FROM `item` WHERE `Customer` = `C`.`Id`) AS `Last Payment`,

(SELECT SUM(`Price`/`Weeks`*4) FROM `item` WHERE `Customer` = `C`.`Id`) AS `Monthly`,

(SELECT SUM((`Price`)-(SELECT SUM(`Amount`) FROM `payment` WHERE `Item`=`item`.`Id` GROUP BY Item)) FROM `item` WHERE `Customer` = `C`.`Id`) AS `Left To Pay`, 

(SELECT (SELECT SUM(`Amount`) FROM `payment` WHERE `Item` = `item`.`Id` GROUP BY `Item`) FROM `item` WHERE `Customer` = `C`.`Id`)  AS `Total Paid`,

(SELECT MAX((`Price`-(SELECT `Amount` FROM `payment` WHERE `Item` = `item`.`Id` GROUP BY `Item`))/(`Price`/`Weeks`)) FROM `item` WHERE `Customer` = `C`.`Id`) AS `MaxWeeks`

FROM 

`customer` AS C

GROUP BY `C`.`Id`

") 

 

Now the above code will display information but the equations within it wont work but the thing is the original script that i had before i change my mysql tables around worked no problem using this..

 

my main issue is that I'm not 100% sure how to nest the three tables. if you look at the code above and the original you will notice that the item table relies on the customer table and payment table relies on item table. But i don't know how to script it i have looked up information on Nesting Select and the join function "INNER JOIN, LEFT JOIN" and so on but i cant seem to figure out a way to code this.

 

If anyone help me with this i would appreciate it i've looked through a lot of tutorials as examples on how to achieve what i want along with submiting the same problem in two other forums so far i've got nothing in return.

 

I've also been trying to sort this issue for a couple of days now and its becoming very frustrating i know what needs to be done i just dont know how to do it.

 

anyways.. thanks in advance

[ignace]

I haven't been able to test it but try and experiment with this:

 

SELECT
  cust.id,
  cust.name,
  (SELECT date
   FROM payment AS t1
   WHERE t1.item_id = item.id AND t1.customer_id = cust.id
   ORDER BY date DESC
   LIMIT 1)
  AS last_payment,
   
  (SELECT sum(amount)
   FROM payment AS t2
   WHERE t2.customer_id = cust.id AND t2.item_id = item.id)
  AS total_paid
  
  ((item.price / item.weeks) * 4)
  AS monthly,
  
  (item.price - total_paid)
  AS left_to_pay,
  
  item.weeks AS max_weeks

FROM customer AS cust
JOIN item ON customer.id = item.customer_id
JOIN payment AS pay ON customer.id = payment.customer_id

[rycore]

hey ignace thanks for replay..

 

okay i've went through your code example and i still get the same issue so i've now narrowed it down to this part of the code.

 

(item.Price - (SELECT SUM(Amount) FROM payment AS t3 WHERE t3.Item = item.Id) AS left_to_pay,
MAX((item.Price - (SELECT sum(Amount) FROM payment AS t4 WHERE t4.Item = item.Id)) / (item.Price/item.Weeks)) AS max_weeks

 

Basicaly what happens is that if a customer hasn't made a payment yet then the lef to pay and max weeks would show up as 0..

 

but what sapose to happen is the left to pay should show the amount LEFT TO PAY so if Price = 100 and Paid = 0 then the left to pay amount would be 100 unless a payment is made then it would be deducted and because of this it is causing Max weeks to do the same.

 

At first i assumed it was the entire script in general but now am sure its to do with the equation.

 

any ideas as to why the equations in my old script work and the ones in this dont??

Evan though they are the same formulas.

 

yet again thanks in advance

[ignace]

Can you provide us with some test data (create table, insert into) so we can experiment to get the correct values?

[rycore]

Sure thing

 

MYSQL TABLE

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 05, 2010 at 09:39 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer`
--

CREATE TABLE IF NOT EXISTS `customer` (
  `Id` mediumint( NOT NULL AUTO_INCREMENT,
  `Name` varchar(10) NOT NULL,
  `Level` enum('1','0') NOT NULL,
  `Email` varchar(40) NOT NULL,
  `Password` varchar(32) NOT NULL,
  `Enabled` enum('1','0') NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`Id`, `Name`, `Level`, `Email`, `Password`, `Enabled`) VALUES
(1, 'Admin', '1', 'admin@email.com', 'admin', '1'),
(2, 'Mark', '0', 'cus@email.com', 'passgoeshere', '1');

-- --------------------------------------------------------

--
-- Table structure for table `item`
--

CREATE TABLE IF NOT EXISTS `item` (
  `Id` mediumint( NOT NULL AUTO_INCREMENT,
  `Customer` mediumint( NOT NULL,
  `Date` varchar(100) NOT NULL,
  `Name` varchar(155) NOT NULL,
  `Weeks` mediumint( NOT NULL,
  `Price` varchar(10) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `item`
--

INSERT INTO `item` (`Id`, `Customer`, `Date`, `Name`, `Weeks`, `Price`) VALUES
(1, 1, '1291584959', 'ItemName', 52, '200'),
(2, 2, '1291584959', 'ItemName2', 20, '100');

-- --------------------------------------------------------

--
-- Table structure for table `payment`
--

CREATE TABLE IF NOT EXISTS `payment` (
  `Id` mediumint( NOT NULL AUTO_INCREMENT,
  `Customer` mediumint( NOT NULL,
  `Item` mediumint( NOT NULL,
  `Date` varchar(100) NOT NULL,
  `Amount` varchar(10) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `payment`
--

INSERT INTO `payment` (`Id`, `Customer`, `Item`, `Date`, `Amount`) VALUES
(1, 1, 1, '1291584959', '50'),
(2, 1, 1, '1291584959', '50');

 

Price / Weeks - will give me the amount each customer pay’s per week then multiply that by 4 gives me the monthly amount.

 

Item Price – Total Amount Paid for that item - will display the amount left to pay.

 

(Item Price – Total Amount Paid for that item) / (Item Price / Item Weeks) – this one will get the Amount customer has left to pay then divide it by the amount the customer is paying per week giving me the total of weeks left. But because the customer can order more than one item once the equation is complete I need to grab the Highest out of all the Weeks left for each customer so wrapping max() around this sum will provide me with highest number out of the equation

 

This works when there is an amount already paid but for some reason it won’t display the correct sum for customers who haven’t made payments yet.

 

thanks again

 

(edited: the reason there is a customer id in payment history is so i can call the amount of payments from each customer so they can view it when they login.)

[rycore]

SCRAP THIS i found the problem

 

the problem was never the code that any one provided me examples of it was within the db itself..

 

in my old db design for when the amount customer paid was also apart of the item table I had a default value set in for Amount paid so it would be set to

 

"0.00" <- this was the issue the whole time because in the new table i created for payments didn't display any values for customers who didn't make payments. this caused it to fail..

 

At this moment in time i feel like a complete noob sorry for wasting time lol...

 

I completely forgot to count for the missing value from that payment table ill be fixing that.