[SOLVED] increment and input data on SQL

[Jay2391]

Okay here is the deal ... I have ann ID colum on this table call...

result_id

so when i Insert new data I need that id field to auto increment and put a number

so first i get the acending  last number ...

         $id = $rowr['result_id'];

and then i add 1 so when it gets inserted you the number PLUS ONE ..
    

                      $result_id = $id + 1;

what happends is that the [color=red]first time I insert the record [/color] get the number PLUS ONE

if the last record is 100 I get 101...

[color=red]BUT WHEN I DO IT AGAIN WITH A NEW RECORD IT TELLS ME THAT IS A PRIMARY
KEY (CORRECT I DID THAT) AND THAT IT ALREDY EXIST... WHAT I DO NOT GET IS WHY
IT WORKS ONCE AND THEN IT DOSENT WORK ANYMORE. WHY IF THE LAST NUMBER IS 101 IT
DOSENT INCREASE TO 102????[/color]

error.... Caused the following error: Duplicate entry '101' for key 1

???

CODE*****************************

<?php

include("DB.php");
$table = 'md_user_main';
$tablet = 'tourney';
$tablea = 'md_user_main';
$tabler = 'results';
$tourneyn = $_SESSION['tourneyn'] ;

      ini_set('display_errors', 1);
      error_reporting(E_ALL & ~E_NOTICE);

         $tc = mysql_connect ($host, $user, $pass);
         $db = mysql_select_db ($database, $tc);

     $query = "SELECT * FROM $table WHERE (first_name=\"$fname\") ";
     $result = mysql_query($query);
     $row = mysql_fetch_array($result);

                 $uname= $row['user_name'];

                  $queryr = "SELECT * FROM $tabler ORDER BY result_id ASC";
     $resultr = mysql_query($queryr, $tc);
     $rowr = mysql_fetch_array( $resultr );
       
         $id = $rowr['result_id'];
    

                      $result_id = $id + 1;
                      $win = 0;
                      $loss = 0;
                      $tie = 0;
                      $scor1 = 0;
                      $scor2 = 0;
                      $scor3 = 0;
                      $scor4 = 0;
                      $scor5 = 0;
                      $scor6 = 0;
                      $scor7 = 0;
                      $eliminated = 0;
                      $final_result = 0;


        $sql = "INSERT INTO $tabler ( result_id, tourney_name, user_name, win, loss, tie, scor1, scor2, scor3, scor4, scor5, scor6, scor7, eliminated,
       final_result ) VALUES ( \"$result_id\", \"$tourneyn\", \"$uname\", \"$win\", \"$loss\", \"$tie\", \"$scor1\", \"$scor2\", \"$scor3\",
\"$scor4\", \"$scor5\", \"$scor6\", \"$scor7\", \"$eliminated\", \"$final_result\")";
        $resultf = mysql_query($sql, $tc) OR die ("The query:".$sql."Caused the following error: ".mysql_error());

         if($sql){

      echo "$result_id<br>";
  echo "$tourneyn<br>";
      echo "$uname<br>";
              echo "$win<br>";
              echo "$loss<br>";
              echo "$tie<br>";
              echo "$scor1<br>";
              echo "$scor2<br>";
              echo "$scor3<br>";
              echo "$scor4<br>";
              echo "$scor5<br>";
              echo "$scor6<br>";
              echo "$scor7<br>";
              echo "$eliminated<br>" ;
              echo "$final_result<br>" ;
}

?>

[effigy]

Why are you not letting MySQL handle the auto increment? If you want to stay manual, try using [tt]SELECT result_id FROM $tabler ORDER BY result_id DESC LIMIT 1[/tt].

[Jay2391]

Okay you made a point that i do not understand how do I make SQL handle that
may be and example i didnt get is what you said was manual or SQL AUTO
???

[effigy]

Read the manual:

[url=http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html]Using AUTO_INCREMENT[/url]
[url=http://dev.mysql.com/doc/refman/5.0/en/create-table.html]CREATE TABLE syntax[/url]

[Jay2391]

BTW THAT WORK I JUST WANT to LEARN HOW to MAKE SQL HANDLE THAT REQUEST ...