Help with a statement query, Please...

Anon

The following statement gives me the last date a inspection was accomplished. Is there a way to this last date plus the next due date which is due in 90 days?

$sql = "
SELECT *
FROM insp, lsv
WHERE insp.vid = \"$vid\"
AND (insp.vid = lsv.vid)
ORDER BY insp.qrtrly DESC
LIMIT 1
";

Thanks for any help.
:o) Ivan

fitzbean

$sql = "
SELECT *
FROM insp, lsv
WHERE insp.vid = '$vid'
AND (insp.vid = lsv.vid)
ORDER BY insp.qrtrly DESC
LIMIT 1
";

Having trouble understanding this..

Which is the date/time or timestamp field?

It could be done with something like this though:

SELECT *, the_last_inspection + INTERVAL 90 DAY as next_inspection FROM etc

Anon

The qrtrly is the date. Here is the rest of the script:

$chk_id = "SELECT vid FROM insp 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\"";

echo "  Sorry, No Inspection Record Found!";

echo "  <a href=\"view_allunits.php\"><img src=\"images\back.gif\" border=\"0\"></a>";

echo " $author";

exit;

} else {

echo "$logo";

echo "<body bgcolor=\"$bg_color\" background=\"$background\" text=\"$tcolor\"";

$sql = "
SELECT *
FROM insp, lsv
WHERE insp.vid = \"$vid\"
AND (insp.vid = lsv.vid)
ORDER BY insp.qrtrly DESC
LIMIT 1
";

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

$the_insp = "

<tr>

<td bgcolor=#800000 width=100>Inspection Date:</td>

<td bgcolor=#800000 width=150>Performed By:</td>

</tr>

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

$id = $row['id'];

$vid = $row['vid'];

$qrtrly = $row['qrtrly'];

$tech = $row['tech'];

$reg = $row['reg'];

$sn = $row['sn'];

$org = $row['org'];

$vco_n = $row['vco_n'];

$vco_e = $row['vco_e'];

$the_insp .= "<tr>

<td bgcolor=#ECDC94 width=100>$qrtrly</td>

}

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

echo "

<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>$org</td>
</tr>
<tr>
<td>VCO:</td>
<td>$vco_n</td>
</tr>
<tr>
<td>Phone:</td>
<td>$vco_e</td>
</tr>
</table>

echo " <table width=\"700\" border=\"0\" cellpadding=\"2\" cellspacing=\"2\">
<tr>
<td width=\"250\" valign=\"top\"></td>
<td width=\"450\" valign=\"top\">
Last Inspection Completed On:   $qrtrly
 
It Was Performed By:   $tech
 
Next Inspection Due On or Before:    
 
$nextdue
 
</td>
</tr>
</table>
";

echo " $author";

exit;

}

Thanks for your inputs.

:o) Ivan

fitzbean

sql = "
SELECT *,insp.qrtrly + INTERVAL 90 DAY

FROM insp, lsv
WHERE insp.vid = \"$vid\"
AND (insp.vid = lsv.vid)
ORDER BY insp.qrtrly DESC
LIMIT 1
";

try that

fitzbean

opps.. forgot to put the "AS nextinspection"

sql = "
SELECT *,insp.qrtrly + INTERVAL 90 DAY as nextinspection
FROM insp, lsv
WHERE insp.vid = \"$vid\"
AND (insp.vid = lsv.vid)
ORDER BY insp.qrtrly DESC
LIMIT 1
";

so it would be accessable as:

$row['nextinspection'] for each row..

Anon

Thank You Fitzbean! It worked like a charm.

:o) Ivan