Code not working

[dudejma]

I'm trying to find the highest number and add one to it so that it creates a new pilot ID, but what happens with this code is it gets the same number every time. Anyone know why?

 

PS. Sorry for all the notes in the code. I like to do that to keep organized. I'm very new to PHP.

 

<?php

$hub = "1";

// Connects to database.
require "db_connect.php";

// Checks that database connects.
if (!$current_db) {
   die("An error has occured. Please contact the webmaster with error code: #A02.");
}

// Syntax to find last Pilot ID.
$sql = "SELECT MAX(pilotnum) FROM users WHERE hub=$hub";

// Runs above syntax.
$result = mysql_query($sql);

// Checks to see if syntax above finds an answer.
if (!$result) {
   die("An error has occured. Please contact the webmaster with error code: #A03.");       
}

// Gives results placeholder.
$pilotnum = mysql_result($result, pilotnum);

// Checks to see if a Pilot ID is found.
if (!pilotnum) {
   die("An error has occured. Please contact the webmaster with error code: #A04.");
}

// Adds one to the last Pilot ID.
++$pilotnum; 

echo $pilotnum;

?>

[Pikachu2000]

If you're trying to do what it looks like you're trying to do (insert a new record and know what the primary key's value is), and pilotnum is an autoincrement index field, you can just get the value of the newly inserted record after it's been inserted by using mysql_insert_id.

[dudejma]

It's not auto increment. I don't want it to be auto increment because there are six different hubs and each pilot is in a different hub. Each hub has it's own beginning number in their pilots' IDs. For example, a pilot in hub 1 would have a pilot ID in the one thousand range, a pilot in hub 2 would have a pilot ID in the two thousand range.

[PFMaBiSmAd]

So, what happens in your current scheme when a pilot relocates to a different hub?

[dudejma]

Their pilot ID would change I guess.

[PFMaBiSmAd]

So, you would need to go through all the data you have stored that is related to that pilot and update the id in it just because his hub changed?

 

That doesn't sound like an very efficient RDBMS design.

 

Wouldn't you rather just assign a fixed permanent id to each pilot and have a column in the pilot information table that holds the id of the hub he is based at?

 

^^^ Doing that would make ALL your code and queries simpler and faster and if the hub changed, all you would need to do is update one piece of data, the 'hub' column for that pilot.

[dudejma]

I thought of going that way but then I thought that it'd be nice to have it all organized this way where each hub has a different starter. But yea, that'd work better now that I think about it. Thanks! So what exactly would the code need to do to assign each new pilot a ID?