Problem with second query ...

Discussion in 'MySQL' started by pepe_lepew1962, Nov 16, 2011.

0
  1. #1
    Hello:

    I am having a problem and I am not sure my method is correct. I know my sub-query isn't. What I need is to get the Max-Autonum, or last record for a specific record. Please see the example for a better understanding:



    part_id_num part_manufac_code part_number
    1 36 18547
    2 36 57847
    3 35 79811
    4 36 57914
    5 35 74992


    If I am looking for part_manufac_code 36, then the last record (part_id_num) for that is 4. Here is the code that I have:
    //
    //
    //
    require("connect.php");
    //
    //
    //
    $query1 = "SELECT part_id_num, part_manufac_code, part_number FROM tblparts WHERE (part_manufac_code = '$frmfindpart')";
    $result1 = mysql_query($query1) or die(mysql_error());
    //
    //
    $query2 = "SELECT MAX(part_id_num) FROM '$result1'";
    $result2 = mysql_query($query2) or die(mysql_error());
    if(mysql_num_rows($result2) > 0)
    {
    while($row = mysql_fetch_assoc($result2))
    {
    $names[] = $row['name'];
    }
    }
    else
    {
    echo 'query returned no results';
    }
     
    pepe_lepew1962, Nov 16, 2011 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    It's whatever the database gives you if you don't have an ORDER BY. The records don't necessarily sit in the database in the order in which they were entered. The order of records returned isn't guaranteed. If you have no way of determining which was the last record (max part number, latest date of entry [you'd have to add a date of entry field]) you have no way of getting the last record returned.

    Assuming that the max part number is the last one entered,

    "SELECT max(part_num), part_id_num, part_manuf_code FROM tblparts WHERE (part_manufac_code = '$frmfindpart') GROUP BY part_id_num, part_manuf_code";
     
    Rukbat, Nov 16, 2011 IP