Get Max Value Question

[KateMacwil]

Hi

In my tenders table, I have a field called "id". The format of this field is "NNN-YYYY".
Each tender that is added for the current year, 1 is added to the first 3 digits of the id field.
Example: 100,2018, 101-2018, 102-2018, etc.

Every year, the tender # is reset to 100 and the year is changed to current year.
Example: 100-2019, 101-2019 etc.

I have written the following statement and run the query below and it doesn't work ($sYear = current year)
SELECT MAX(LEFT(id,3)) AS iMax FROM $table_name WHERE RIGHT(RTRIM(id), 4) = $sYear

If I remove the LEFT(id,3), it does.
SELECT MAX(id) AS iMax FROM $table_name WHERE RIGHT(RTRIM(id), 4) = $sYear

So my MAX code isn't working and I am not able to :
1) Figure out what is wrong with MAX(LEFT(id,3))
2) Display and assign the iMax variable to a PHP variable
 

Thanks.

 

 

 

Below is my code:

function GetNextTenderID()
{
    global $wpdb;
    $table_name  	= $wpdb->prefix . "tenders";
    $sYear                   = date("Y");
    $sSql 		= "SELECT MAX(id) AS iMax FROM $table_name WHERE RIGHT(RTRIM(id), 4) = $sYear";
    $rows 		= $wpdb->get_results($sSql);
    $rowcount 	= $wpdb->num_rows;
					
    if ($rows)
    {
	    echo "Test to see if id displays" . $row->id;
       $old_num = $row->id;
        $new_num = (how do I get iMax) + 1;
    }

   return $sTemp;
}

 

 

Thanks.

 

 

 

 

 

 

 

Edited June 4, 2018 by KateMacwil

[justsomeguy]

What value does it return when you use left?

[KateMacwil]

When I run this:
SELECT LEFT(id,3) FROM $table_name WHERE RIGHT(RTRIM(id), 4) = $sYear
 

and then echo out the $row->id;

nothing displays

 

 

[justsomeguy]

You don't have a column called id, you need to use an alias to tell it what to call that.  As is, the column name would be "LEFT(id,3)".