SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist CyberFuture's Avatar
    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'>&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.

  2. #2
    Dumb PHP codin' cat
    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.

  3. #3
    SitePoint Evangelist CyberFuture's Avatar
    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?

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    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"

    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).

  5. #5
    SitePoint Evangelist CyberFuture's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •