SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: Strange SQL Results
-
May 7, 2001, 11:59 #1
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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'> </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.
-
May 7, 2001, 16:22 #2
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Please don't PM me with questions.
Use the forums, that is what they are here for.
-
May 7, 2001, 17:02 #3
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
May 7, 2001, 23:39 #4
- Join Date
- Jun 2000
- Location
- Sydney, Australia
- Posts
- 3,798
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
PHP Code:$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";
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).
-
May 8, 2001, 10:16 #5
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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";
Bookmarks