PHP - Move to the last (max) id

[cjkeane]

Hi everyone.

 

I have a records table and a communications table. i have a query insert a new CaseActionNumber in the communications table and increase the value by 1. i need the page to show information from the records table with the autonumber, but also find the last (max) value for CaseActionNumber for that person based on the $idnumber. for ex.

 

in the records table i'll have:

idnumber namelast_1 namefirst_1 etc

1 john smith

2 peter parker

 

communications table

commid idnumber caseactionnumber etc

1 1 001

2 1 002

3 2 001

4 2 002

5 2 003

 

I need the query below to find the idnumber plus the max(casactionnumber) for that person. sometimes my query finds the max caseactionnumber, othertimes it displays the idnumber with 001 not the max caseactionnumber. i'm not sure what i'm doing wrong so i'd appreciate some help on this issue. thank you.

 

 

 

 

 
<?php
// query db
$IDNumber = $_GET['IDNumber'];

    $result = mysql_query("SELECT records.NameFirst_1, records.NameLast_1, Max(Communications.CaseActionNumber) AS MaxCaseActionNumber, records.IDNumber, records.CaseOwnerSelect, records.CompanyName, records.CompanyReferenceNumber, records.CompanyBranch, Communications.ConversionType, Communications.Contact, Communications.ContactFrom, Communications.ContactTelephone, Communications.ContactEmail, Communications.ContactFax, Communications.CallDate, Communications.CallTime, Communications.ActionTextField, Communications.CallTime, Communications.ActionTextField
FROM records right JOIN Communications ON records.IDNumber = '$IDNumber'
GROUP BY records.NameFirst_1, records.NameLast_1, records.IDNumber, records.CaseOwnerSelect, records.CompanyName, records.CompanyReferenceNumber, records.CompanyBranch, Communications.ConversionType, Communications.Contact, Communications.ContactFrom, Communications.ContactTelephone, Communications.ContactEmail, Communications.ContactFax, Communications.CallDate, Communications.CallTime, Communications.ActionTextField, Communications.CallTime, Communications.ActionTextField")
?>

 

[btherl]

The query looks ok to me, assuming you're grouping by every column you are not aggregating (I haven't checked since there's so many).

 

You say that "sometimes" it doesn't return the maximum id number - is it random, or does it depend on the input?  If it's specific input, please give all details for one of the inputs it doesn't work for.