CODEWITHSUNDEEP
mysqlpdo
zac1987
zac1987

Reputation: 2777

mysql join two tables select and records problems

I want to show the below info onto my webpage :
Date - Personal expense (Business expense)
06-June-2012 - usd555.00 (usd3022.00)
05-June-2012 - usd666.00 (usd0.00)
04-June-2012 - usd0.00 (usd444.40)
04-June-2012 - usd333.00 (usd2333.40)

Currently my code is :

$sql = "SELECT payment_date, sum(price) FROM expense WHERE category=? spender_id=? GROUP BY payment_date";
    $q = $conn->prepare($sql);
    $result = $q->execute(array("Personal", $user_id));
    while($r = $q->fetch(PDO::FETCH_ASSOC)){
        $payment_date_db = $r['payment_date'];
        $payment_date_db2 = date("d-F-Y", strtotime($payment_date_db));
        $price_db = $r['sum(price)'];

        echo $payment_date_db2 . " - " . $money_currency . $price_db . "<br />";
    }

I don't know how to select and display the business expense record and show it into the blanket sign (). Any idea?

Upvotes: 0

Views: 276

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

You should really provide more details in regards to your table schema, but I think I can infer from your query that it's something like this:

expense(payment_id [PK], spender_id [FK], category [FK??], price, payment_date)

Where the category field contains either "Personal" or "Expense".

So if you want to get the sum of both personal and business expense for a particular spender for each date, try this query:

SELECT 
    payment_date,
    SUM(IF(category = 'Personal', price, 0)) AS personal_expense_total,
    SUM(IF(category = 'Business', price, 0)) AS business_expense_total
FROM 
    expense
WHERE 
    spender_id = ?
GROUP BY 
    payment_date

So in your PHP code, you can do this:

$sql = '
    SELECT
        payment_date,
        SUM(IF(category = "Personal", price, 0)) AS personal_expense_total,
        SUM(IF(category = "Business", price, 0)) AS business_expense_total
    FROM expense
    WHERE spender_id = :spender_id
    GROUP BY payment_date';
$stmt = $conn->prepare($sql);
$stmt->bindParam(':spender_id', $user_id, PDO::PARAM_INT); // Assuming $user_id contains an integer
$stmt->execute();

while($r = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $date = date('d-F-Y', strtotime($r['payment_date']));
    $personal = 'usd' . number_format($r['personal_expense_total'], 2);
    $business = 'usd' . number_format($r['business_expense_total'], 2);

    echo "$date - $personal - ($business)<br />";
}

Upvotes: 1

Related Questions