need help here...how to get the largest value from an array

[7m25]

i know this seems to be a stupid question but i am really having a hard time trying to figure things out. I'm a PHP newbie, any help will i really really appreciate...

$query = "SELECT projCode FROM tbl_project";
$result = @($query);

while ($projectCodeRows = mysql_fetch_array($result, MYSQL_NUM)) {

 $codeChunks = explode("-", $projectCodeRows[0]); //isolate the numbers
 $showrecords = $codeChunks[3]; //get the numbers       
 echo $showrecords; // display the numbers 
 echo '<br>';

my code is working just right but what i really wanted to do is to display only the value of the highest number...

i know this is just a piece of cake for you guys..thanks in advance! 🙂

[sfullman]

IF all you're looking for is a a max value just run this query:

SELECT MAX(numer_field) from table [WHERE .. if you need to select]

If you need the data for that record too, you could also do:

Select * from table order by prodCode DESC LIMIT 1

which would do the same thing, but give you the entire record also.

Note: if the table is large, you could add an index to prodcode and it'd be lightning fast.

Sometimes it's best to do things in multiple queries to the database..

Sam

[7m25]

thanks for the immediate reply.. i guess i failed to relay my question right..the real score is I will not be able to run a query for the number field since my project code format is like this: prj-SDFE-2004-256...I used the $codeChunks = explode("-", $projectCodeRows[0]); command to get the value of the last part of the project code (256).

Since my query is 'SELECT projectCode FROM tbl_Projects', It will display all the project codes. What am i going to do if i only want to display the one with the highest number?

[tekky]

actually you should be able to use something like...

//prj-SDFE-2004-256
SELECT prjcode FROM table ORDER BY max(substring(prjcode,15)) DESC LIMIT 1
//essentially, take the MAX of the string AFTER the 3rd - and order by that value... never tested... but theoretically should work w/o using any PHP code to do it...

[7m25]

it does not have an specific length. I declared the type of the projectCode as VARCHAR with a length of 50.

[tekky]

after reading your example # i amended my previous post... I believe it should solve your problem.... unless the WHOLE prjcode identifier isnt a static charset + a incrementing value...

[7m25]

Alright...

          prj-SDFE-2004-256 - is the exact data stored on the database.  I used the explode command to isolate the last info of the project code. 

                 *prj - is limited into 3 chars only.

                 *SDFE - may consist of up to 20 chars.

                 *2004 - is the current year

                *256 - is the key number. I use this to monitor how many projects are already on my database.

How do i extract it using 'SELECT prjcode FROM table ORDER BY max(substring(prjcode,15)) DESC LIMIT 1'? I think this will only work if the length of the strings are static.

[findapollo]

call me crazy, but wouldn't it just be easier if you held each of those four data items in different fiields?
if you're interested in modifying your database I can give you a few lines of code to make this a lot easier.

other than that, this is what I came up with (mind you it ain't pretty, bro):

$sql  = 'SELECT projCode,'
       .' REVERSE( LEFT( REVERSE( projCode ) , INSTR( REVERSE( projCode ) ,  "-"  )  - 1  )  )  AS highest'
       . 'FROM tbl_project'
       . 'ORDER  BY highest ASC  ';

(guess that class in database design is useful for something.)

so, this returns two items:
the first is the projCode with the highest key number....the second is just just that key value which was necessary to do the work

happy PHPing,
-findapollo

[tekky]

Originally posted by 7m25
.....How do i extract it using 'SELECT prjcode FROM table ORDER BY max(substring(prjcode,15)) DESC LIMIT 1'? I think this will only work if the length of the strings are static.

You are correct, as I did state, that I assumed the first portion of your ID was static and you then added an incremental value... consequently...

SELECT prjcode FROM table ORDER BY MAX(RIGHT(prjcode,LENGTH(prjcode) - LOCATE('-',prjcode,LENGTH(prjcode)-6))) ASC LIMIT 1
//take prjcode... get current length... (17 in this case - 6 (picked 6 out of my head as im guessing you wont go OVER 5 digit key #'s)) = 11
//LOCATE the first - after the 11th char... returns 14
//take LENGTH (17) - 14 = 3
//return RIGHTmost 3 characters of prjcode = 256

Maybe that will work for ya...

[dopey]

Maybe a bit easier to use substring_index() e.g.

select MAX(SUBSTRING_INDEX(prjcode,'-',-1)) from table;

The -1 tells the function to return the string up until the first instance of '-', but starting at end of string and working backwards.

[tekky]

Originally posted by dopey
Maybe a bit easier to use substring_index() e.g.

select MAX(SUBSTRING_INDEX(prjcode,'-',-1)) from table;

The -1 tells the function to return the string up until the first instance of '-', but starting at end of string and working backwards. [/B]

had i known of that function, Yah would have been much easier and less confusing to write 😉

[7m25]

Originally posted by findapollo
call me crazy, but wouldn't it just be easier if you held each of those four data items in different fiields?
if you're interested in modifying your database I can give you a few lines of code to make this a lot easier.

other than that, this is what I came up with (mind you it ain't pretty, bro):

$sql  = 'SELECT projCode,'
       .' REVERSE( LEFT( REVERSE( projCode ) , INSTR( REVERSE( projCode ) ,  "-"  )  - 1  )  )  AS highest'
       . 'FROM tbl_project'
       . 'ORDER  BY highest ASC  ';

(guess that class in database design is useful for something.)

so, this returns two items:
the first is the projCode with the highest key number....the second is just just that key value which was necessary to do the work

happy PHPing,
-findapollo [/B]

thanks for the code bro...i really agree on ur suggestion about separating those four data items in different fields but then i still have to ask for the approval of the analyst to be able to do that. Anyway, thanks again!

[7m25]

Originally posted by dopey
Maybe a bit easier to use substring_index() e.g.

select MAX(SUBSTRING_INDEX(prjcode,'-',-1)) from table;

The -1 tells the function to return the string up until the first instance of '-', but starting at end of string and working backwards. [/B]

I tried using this code but it has an error on it. My database rows has values of the ff:

    prj-ITDI-2004-1
    prj-PCARRD-2004-5
    prj-STII-2004-11
    prj-STII-2004-7

The result is 7. I think the code is just comparing the 1st numbers because when i change 11 to 91 or whatever number with it's first number higher than 7, it is displaying the said number.

[findapollo]

I was assuming you were the owner of the database and had complete control over it. So, you didn't design it that way? Please tell me that this field is not the only item in each row of the database.

I misread the definition for substring_index(), thinking a -1 would only return 1 character.
I tried dopey's code too and got some strange results.

Try this:

$sql  = 'SELECT prjCode'
     . 'FROM tbl_project'
     . 'ORDER  BY SUBSTRING_INDEX( prjCode,  \'-\',  - 1  )  LIMIT 0, 1';             

that will give you just the highest one. if you want them all in descending order just take off the 'LIMIT 0,1'

[dopey]

Oooops... it's doing a string comparison. Try this instead:

select MAX(SUBSTRING_INDEX(prjcode,'-',-1)+0) from table;

The +0 converts it to a number