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

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...