Problem with second query ...

[pepelepew1962]

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'; 
}

[requinix]

The main problem is you're not using the right code. After a mysql_query() comes one of the mysql_fetch_*() functions, like mysql_fetch_array. That's the function that gives you the actual data; mysql_query() only returns an identifier - no data.

 

The second problem is in your query, as you guessed. All you're doing now is getting the highest value - what you want is to retrieve the entire row. Use an ORDER BY + LIMIT instead.

SELECT * FROM tblparts WHERE part_manufac_code = '$frmfindpart' ORDER BY part_date DESC LIMIT 1

 

The third problem is that you're assuming the highest auto_increment value is the latest. Don't do that. Include a date column for when the record was added and do the ORDER BY on that instead. I did so in the query above using the name "part_date".

[fenway]

You can't concatenate queries like that -- and use code tags next time.