Quick Question

ivains01

What am I doing wrong? The first query works fine, but the second won't. I am missing something? Any help would be gratefully appreciated.

$sql = "
SELECT *
FROM maint, lsv
WHERE maint.vid = '$vid'
AND maint.vid = lsv.vid
ORDER BY maint.date DESC
";

$sql .= "SELECT SUM(maint.cost) as totalcost FROM maint, lsv WHERE maint.vid = '$vid' AND maint.vid = lsv.vid";

$result = @($sql,$connection) or die("Couldn't execute query.");

kamy99

The line:

WHERE maint.vid = '$vid' AND maint.vid = lsv.vid

should be:

WHERE maint.vid = '$vid' AND maint.vid = 'lsv.vid'

Also, it would help if said what the problem is!

ivains01

Sorry. The problem is it is not giving me the total from the cost column. When I try it by itself it works fine except I won't get the other information just the total cost. I am trying to get the information and the total cost. I made the change you recommended, here is the rest of it:

$chk_id = "SELECT vid FROM maint WHERE vid = \"$vid\"";

$chk_id_res = @($chk_id,$connection) or die("Couldn't execute query.");

$chk_id_num = mysql_num_rows($chk_id_res);

if ($chk_id_num == "0") {

echo "$logo";

echo "<body bgcolor=\"$bg_color\" background=\"$background\" text=\"$tcolor\" link=\"#ffffff\" alink=\"#ffffff\" vlink=\"#ffffff\"";

echo "  Sorry, No Records Found!";

echo "    <a href=\"show_addmaint.php?vid=$vid\"><img src=\"images/sm_plus.gif\" border=\"0\" alt=\"Add A Maintenance Record\">Add A Maintenance Record</a>";

echo " $author";

exit;

} else {

echo "$logo";

echo "<body bgcolor=\"$bg_color\" background=\"$background\" text=\"$tcolor\" link=\"#FFFFFF\" vlink=\"#FFFFFF\" alink=\"#FFFFFF\"";

$sql = "
SELECT *
FROM maint, lsv
WHERE maint.vid = '$vid'
AND maint.vid = 'lsv.vid'
ORDER BY maint.date DESC
";

$sql .= "SELECT SUM(maint.cost) as totalcost FROM maint, lsv WHERE maint.vid = '$vid' AND maint.vid = lsv.vid";

$result = @($sql,$connection) or die("Couldn't execute query.");

$the_maint = "

<tr>

<td bgcolor=#800000>Maintenance Performed:</td>

<td bgcolor=#800000>Technician:</td>

</tr>

while ($row = mysql_fetch_array($result)) {

$id = $row['id'];

$vid = $row['vid'];

$date = $row['date'];
$date=date('d M Y',strtotime($date));

$mdisc = $row['mdisc'];

$mtype = $row['mtype'];

$cost = $row['cost'];

$tech = $row['tech'];

$reg = $row['reg'];

$sn = $row['sn'];

$org = $row['org'];

$vco_n = $row['vco_n'];

$vco_e = $row['vco_e'];

$totalcost = $row['totalcost'];

$the_maint .= "<tr>

<td bgcolor=#808080>$mdisc</td>

<td bgcolor=#808080>$mtype</td>

}

$the_maint .= "</tr></table>";

echo "

<table width=300 cellpadding=3 cellspacing=3 border=0><tr><td>
<a href=\"view_singleunit.php?org=$org\"><img src=\"images/sm_goback.gif\" border=0 alt=\"Go Back To $org Thread\"></a>
  Go Back To $org Thread 
</td></tr></table>

<table width=450 cellpadding=2 cellspacing=2 border=0>
<tr>
<td>Cart Plate Number:</td>
<td>$reg</td>
</tr>
<tr>
<td>Cart Serial Number:</td>
<td>$sn</td>
</tr>
<tr>
<td>Organization:</td>
<td><a href=\"view_singleunit.php?org=$org\">$org</a></td>
</tr>
<tr>
<td>VCO:</td>
<td>$vco_n</td>
</tr>
<tr>
<td>Phone:</td>
<td>$vco_e</td>
</tr>
<tr>
<td></td>
<td><a href=\"show_addmaint.php?vid=$vid\"><img src=\"images/add_maint.gif\" border=\"0\" alt=\"Add Maintenance Record\"></a>   Add Maintenance Record</td>
</tr>
</table>

echo " $the_maint";

echo " Total Spent On Maintenance: $$totalcost";

echo " $author";

exit;

}

tomhath

You need to run the two selects individually, instead of running them together without even a separator between DESC and SELECT. Or do some background reading on the sql keywords "JOIN" and "GROUP BY"