Strange SQL Results

[CyberFuture]

This is very puzzling. Hopefully someone here can figure it out.

I have the following query

$sql2 = "SELECT rma.ID, rma.product_ID, status.status, rma.date_opened, rma.date_closed " .
"FROM rma, status_lookup, status " .
"WHERE (rma.ID = status_lookup.rma_ID) " .
"AND (status_lookup.status_ID = status.ID) " .
"ORDER BY rma.ID DESC";

Which I use with following code:

$result2 = mysql_query($sql2);
while($row2 = mysql_fetch_array($result2))
{
$rid = $row2["rma.ID"];
$product = $row2["rma.product_ID"];
$state = $row2["status.status"];
$odate = $row2["rma.date_opened"];
$cdate = $row2["rma.date_closed"];
if (!$cdate)
{
echo ("<tr bgcolor='#FFC4C4'>\n");
echo ("<td width='75' align='center'><b>". $rid . "</b></td>\n");
echo ("<td width='250' align='center'>" . $product . "</td>\n");
echo ("<td width='75' align='center'>" . $state . "</td>\n");
echo ("<td width='100' align='center'>" . $odate . "</td>\n");
echo ("<td width='100' align='center'>&nbsp;</td>\n");
echo ("</tr>\n");
}
else
{
echo ("<tr bgcolor='#AEFFAE'>\n");
echo ("<td width='75' align='center'><b>" . $rid . "</b></td>\n");
echo ("<td width='250' align='center'>" . $product . "</td>\n");
echo ("<td width='75' align='center'>" . $state . "</td>\n");
echo ("<td width='100' align='center'>" . $odate . "</td>\n");
echo ("<td width='100' align='center'>" . $cdate . "</td>\n");
echo ("</tr>\n");
}
}

The result is 17 blank rows. I have run several test and have found that all field values are NULL. If I take the SQL statement and run thequery in phpMyAdmin, I get 17 rows filled with data.

Any ideas on what's going on here?

One other question about this query. Each rma.ID goes through several status. I only want to return the last entry in the status_lookup table for a given rma.id ( or status_lookup.rma_ID). Currently the query is returning all entries in the status_lookup table.

Thanks for any help.

[freddydoesphp]

The problem with the blank rows is because you cannot use a . in the $row array, either assign your fields aliases in the query like

rma.ID as rid

and then call them it as $row["rid"] or make sure that the fields all have unique names and omit the tablename when you print them so rma.ID could just be printed as $row["ID"] as long as you are not selecting a field named ID from another table in your query.

[CyberFuture]

Thanks freddydoesphp, for the alias suggestion. My rows are now filled with data. This is the first time I heard of this row array limitation. Definitely something worth knowing.

Any takers on my second question?

[freakysid]

Lets assume that each time you update the status table you keep track of the time the update was made using a timestamp column called updatetime.

$sql2 = "SELECT rma.ID, 
               rma.product_ID, 
                status.status, 
                rma.date_opened, 
                rma.date_closed,
                MAX(status.updatetime)
         FROM rma, status_lookup, status 
         WHERE (rma.ID = status_lookup.rma_ID)
         AND (status_lookup.status_ID = status.ID)
         GROUP BY rma.ID
         ORDER BY rma.ID DESC"; 

I think that should work - the query tells MySQL to create a set and GROUP BY rma.ID and filter the set to just the MAX(status.updatetime) row of each grouping.

Also note how I let that SQL spill over several lines. No need to use string concatenation as MySQL ignores whitespace. line breaks, etc (just like HTML does).

[CyberFuture]

Worked like a charm. Thanks freakysid!!!

For anyone interested, this is what my SQL query now looks like after modifying it per freakysid's and freddydoesphp's suggestions.

$sql2 = "SELECT rma.ID AS col1,
rma.product_ID AS col2,
status.status AS col3,
rma.date_opened AS col4,
rma.date_closed AS col5,
MAX(status_lookup.status_date)
FROM rma, status_lookup, status
WHERE (rma.ID = status_lookup.rma_ID)
AND (status_lookup.status_ID = status.ID)
AND (rma.contact_ID = $user)
GROUP BY rma.ID
ORDER BY rma.ID DESC";