Problem with second query ...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pepe_lepew1962
Forum Commoner
Posts: 44
Joined: Thu Nov 20, 2008 10:29 am

Problem with second query ...

Post by pepe_lepew1962 »

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:



[text]part_id_num part_manufac_code part_number
1 36 18547
2 36 57847
3 35 79811
4 36 57914
5 35 74992[/text]


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:

Code: Select all

//
//
//
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'; 
}
Last edited by Benjamin on Wed Nov 16, 2011 5:30 pm, edited 1 time in total.
Reason: Added [syntax=php||htm||css||javascript||sql||etc] - Please use [syntax] tags when posting code in the forums! Thanks.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problem with second query ...

Post by Celauran »

pepe_lepew1962 wrote:

Code: Select all

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.

Code: Select all

SELECT part_id_num, part_number
FROM tablename
WHERE part_manufac_code = 36
  AND part_id_num = (SELECT MAX(part_id_num)
                     FROM tablename
                     WHERE part_manufac_code = 36)
Post Reply