Jump to content
KateMacwil

Get Max Value Question

Recommended Posts

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 by KateMacwil

Share this post


Link to post
Share on other sites

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

 

 

Share this post


Link to post
Share on other sites

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)".

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×