Doing an SQL query in PHP where a field from a different table is referenced.

Question

OK,

In this query I am displaying the sales for a particular person, matching the passport number from the current form I am working on.

What I want to do however, is to sum the total sales and display it, excluding records which have been marked as paid.

I am having trouble because "paid" does not existent in the current form I am working on as a variable, nor the table it relates to.

I can´t use row['paid'] as I need to do this query outside of the while loop.

What should I do in this situation?

$sqlstr = mysql_query(
        "SELECT * FROM sales where passport = ".
        "'{$therecord['passport']}'");
if (mysql_numrows($sqlstr) != 0) {
    echo "<b>Sales for {$therecord['firstname']} ".
            "{$therecord['lastname']}</b><br />";
    echo "<table><tr>";
    echo '<tr><th align="left">Name</th><th align="left">Quantity</th>".
            "<th align="left">Cost</th></tr>';
    while ($row = mysql_fetch_array($sqlstr)) {
        echo "<td>{$row['product']}</td>";
        echo "<td>{$row['quantity']}</td>";
        echo "<td>{$row['cost']}</td>";
        echo "</tr>";
    }
}

echo "</table>";
$sqltotal = mysql_query(
        "SELECT SUM(cost) as total FROM sales where passport = ".
        "'{$therecord['passport']} AND {$therecord['paid']} <> 1'");
$row = mysql_fetch_array($sqltotal);
echo "<br /><b>Total Owing: {$row['total']}</b>";

Answer 1

you've misplaced simple quote in :

"'{$therecord['passport']} AND {$therecord['paid']} <> 1'"

it must be :

"'{$therecord['passport']}' AND {$therecord['paid']} <> 1"

Answer 2

You could either create a MySQL view, of look at SQL joins. I'm not sure on your database structure, but you should have a SQL query like this:

SELECT SUM(sales.cost) AS total
FROM sales, table2
WHERE sales.passport = '$passport_id'
AND sales.passport = table2.passport
AND table2.paid = '1'

Not sure as that was wrote off-hand. Again, it'd be better if we knew the structure of your tables.