php - Find the largest number in the database and create new variable

631

So, i have this table on the database and i have characters like this:

A102
A897
B234
B23
C9823
C786
D345 etc...

What i need to achieve is: If the user has enterd A the function should look all the variables that start with A i.e I have : A102 and A897, i should remove the first character and there remains 102 and 897. The largest of them is 897, so i should create a new variable A(897+1)=A898 and so on . The query:

while( $row = mysql_fetch_assoc( $result)){
    $value[] = $row['id']; // Inside while loop
}

I guess it should be something like this:

   if ($variable==A) {
    $items = array();
    foreach($value as $value) {
     $items[] = substr('$value', 1);
    max($items); // to find max
    }
   }

Approach

$result = "SELECT * FROM formas WHERE 'id_f' LIKE '%A%'" ;  
$res = odbc_exec($connection, $result) or die(odbc_error());



    $biggest = 0;
    while( $row = odbc_fetch_array($res))
    {
         $current_value = substr($row['id_f'], 1); // return: 102, 897;
         if( $current_value > $biggest )
         {
            $biggest = $current_value; // in the last looping you should get 897.
         }
    }
    echo $result = "A(" . ($biggest + 1) . ")"; // return A(898)

816

Answer

Solution:

I am not sure about passing parameter. Try this SQL

SELECT DER_NAME ||CHAR(COUNTER + 1)
FROM 
(
SELECT SUBSTR(NAME, 1) AS DER_NAME, MAX(INT(SUBSTR(2, NAME))) AS COUNTER
FROM TABLE-NAME
WHERE NAME LIKE 'A%' --SUBSTITUE YOUR PARAMETER
group by SUBSTR(NAME, 1)
) A

929

Answer

Solution:

(SELECT MAX(N) as maxid FROM (
SELECT CAST(SUBSTR(name, 2) AS UNSIGNED INTEGER) as N 
From `mytable` 
) t)

UNION ALL

(SELECT N as maxid FROM (
SELECT CAST(SUBSTR(name, 2) AS UNSIGNED INTEGER) as N 
From `mytable` 
ORDER BY N DESC) t LIMIT 1)

SEE SQL Fiddle DEMO

You can simply use one query:

SELECT MAX(N) as maxid FROM (
    SELECT CAST(SUBSTR(name, 2) AS UNSIGNED INTEGER) as N 
    From `mytable` 
    ) t

313

Answer

Solution:

I think it can be solved by this procedure, correct me if I was wrong. (1). You should create a function that take the first character from the user input. Example:ABC233, the function shouldreturn "A" (2)Once you get the character from step 1, you can query it to table. Here is the complete source code:

> <?php  
> mysql_connect("localhost", "root","");
> mysql_select_db("stackoverflow");
> 
> 
> /* First chars */ 
> function first_char( $str ) 
> {
>     return substr($str, 0, 1); 
> } 
> ?>
> 
> <form action="home.php" method="post"> 
>      Input String : <input type="text" name="txtstring"><br> 
>      <input type="submit" value="Submit">
> </form>
> 
> <?php 
> if( $_POST['txtstring'] != '' ) 
> {    
>    $char = first_char($_POST['txtstring'] );    
>    $sql = mysql_query("SELECT * FROM mytablWHERE my_field LIKE '%" . $char . "%'") or die(mysql_error());
>  
>    
>     $biggest = 0;
>     while( $row = mysql_fetch_array($sql))
>     {
>          $current_value = substr($row['my_field'], 1); // return: 102, 897;
>          if( $current_value > $biggest )
>          {
>             $biggest = $current_value; // in the last looping you should get 897.
>          }
>     }
>     $result = "A(" . ($biggest + 1) . ")"; // return A(898)
>      
>     echo $result;
> 
> } ?>

I assume have database named "stackoverflow" and table named "mytable fields: (ID, my_field)" And the result something like this:

enter image description here

Let me know if it works!

People are also looking for solutions to the problem: codeigniter - PHP Fatal error allowed memory size exhausted

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question