Adding headings to table columns, and outputting a total from SQL

Question

I have this code,

$sqlstr = mysql_query(
    "SELECT * FROM sales where passport = '{$therecord['passport']}'");
if (mysql_numrows($sqlstr) != 0) {
    echo "<b>Sales for {$therecord['firstname']} {$therecord['lastname']}</b>";
    while ($row = mysql_fetch_array($sqlstr)) {
        echo "<table><tr>";
        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) FROM sales where passport = '{$therecord['passport']}'");
echo "<b>Total Owing: {$sqltotal}</b>";

I would like to add a heading to each table column, and maybe some spacing between them.

How would I go about that?

Additionally, I am trying to use the SQL SUM function and output a total, but at the moment it outputs something like #id24 instead...

Answer 1

Just echo it before you start the while loop:

echo '<tr><th>Name:</th><th>Quantity:</th><th>Cost:</th></tr>';

As for your sum problem, it's because you are echoing the resource ID. You also need to call mysql_fetch_array() (or similar function) on that resource as well like you did with the other one.

Answer 2

You could echo the table headings before the while loop;

<thead>
    <tr>
        <th>Name:</th>
        <th>Quantity:</th>
        <th>Cost:</th>
    </tr>
</thead>
<?php
$sqlstr = mysql_query("SELECT * FROM sales where passport = '{$therecord['passport']}'");
if (mysql_numrows($sqlstr) != 0) {
    ....
}
....
?>

This would sort your SUM issue (using mysql_result). Currently you are only outputting the resource.

$sqltotal = mysql_result(mysql_query("SELECT SUM(cost) FROM sales where passport = '{$therecord['passport']}'"),0);
echo "<b>Total Owing: {$sqltotal}</b>";

Answer 3

sqlstr = mysql_query(
    "SELECT * FROM sales where passport = '{$therecord['passport']}'");
if (mysql_numrows($sqlstr) != 0) {
    echo "<b>Sales for {$therecord['firstname']} {$therecord['lastname']}</b>";
    while ($row = mysql_fetch_array($sqlstr)) {
        echo "<table><tr>";
        echo "<td>{$row['product']}</td>";
        echo "<td>{$row['quantity']}</td>";
        echo "<td>{$row['cost']}</td>";
        echo "</tr>";
        echo "</table>";
    }
}
echo "<b>Total Owing: ".mysql_num_rows($sqlstr)."</b>";

Just have to run the number or rows as the query is the same as above!

Answer 4

Try this for the sum:

$sqltotal = mysql_query("SELECT SUM(cost) AS totalcost FROM sales WHERE passport = '" . mysql_real_esacpe_string($therecord['passport] . "';
$row = mysql_fetch_array($sqltotal);
echo "<b>Total Owing: {$row['totalcost']}</b>";

Answer 5

about the sum : after run query , you need to get the result

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

Answer 6

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

    $sqltotal = mysql_query( 
        "SELECT SUM(cost) AS Total FROM sales where passport = '{$therecord['passport']}'"); 
    $totalRow = mysql_fetch_array($sqltotal)) { 
    echo "<tr>"; 
    echo "<td colspan=\"2\"></td>"; 
    echo "<td><b>Total Owing: {$totalRow['Total']}</b></td>"; 
    echo "</tr>"; 

    echo "</table>"; 
}