SQL related problem

[catgurl_ashley]

Okie im working on this RPG based game for a forum...

It uses MySQL Tables, I have it set up so each charecter has a different ID.... 1, 2, 3, 4,5.... and so on

Now my problem is, the way I have it coded if a user deleats thier charecter, then there will be a problem, Because I have it set up to count the number of rows then add one to get the new ID...

Im trying to figure out a way to count the number of rows add one, AND THEN CHECK IF THAT NUMBER IF IS ALREADY IN USE, and if its not use it, if it is, add one and try again.....

i was thinking i could use sum kind of loops to try and do this, but Ive come up blank....

here is sum of the code im dealing with

$DB->query("SELECT id FROM ibcharecter");
$Num = $DB->get_num_rows();
$Count = $Num + 1;

$DB->query("INSERT INTO rpWcharecter VALUES ($Count, $othervariables)");

I tried this test code to see if I could explode the $Row array to check each one, but it didnt work

$DB->query("SELECT id FROM ibcharecter");
$Row = $DB->fetch_row();
$Bob = explode("\\", $Row);

if ($Bob['0'] == 1) {
	echo "True<Br>";
} else {
	echo "False<br>";
}
if ($Bob['1'] == 2) {
	echo "True<Br>";
} else {
	echo "False<br>";
}
if ($Bob['2'] == 3) {
	echo "True<Br>";
} else {
	echo "False<br>";
}
if ($Bob['3'] == 4) {
	echo "True<Br>";
} else {
	echo "False<br>";
}

I know about the autoincrement feature in mysql...... that seems to be a good idea, but I really dont know how to get it to work.....

I tried to change my tables stuff via phpmyadmin to autoincrement...but it came up with an error.....

Any ideas guys?

[laserlight]

Read the MYSQL manual for the query needed to change your table to AUTO_INCREMENT, maybe your syntax for ALTER TABLE is wrong.

Actually, if you arent already in production phase (and you shouldnt be), just drop the table and create a new one.

[catgurl_ashley]

okie dokie....

say that I get the autoincrement on....

I still dont know how to use it.... what would be the query I would execute to make auto increment work???

[catgurl_ashley]

anyone know how to make autoincrement work???

I hate to bump topics, but this is slowing my work, and i have a release date :p so i gotta be bumpy if you know what i mean 😉

[ducey]

To use the autoincrement when inserting data into a database, do the following:

include("connection.php");
$value1 = "some value";
$value2 = "more values";
$value3 = "some more values";
// Now create a variable called id that has no value. This is needed for the insert, but as it autoincrements for you, we don't give it a value.
$id;

$query = "insert into myTable values('$value1','$value2','$value3','$id');
mysql_query($query,$connection) or die("Could not insert");
echo("Successfully added data to the table");

Hope this helps,
Nathan

[tbach2]

Are you using phpMyAdmin? It makes things a lot easier.

You want want to make sure that id is the primary key (which also means that they are unique). if you have trouble making it the primary key, then you most likely already have duplicates to get rid of.

Once id is the primary key, then you can make it an autoincremented field.

using it is easy: when you insert records, just don't refer to it. it AUTOincrements 🙂

ex:

table user:
id
name
dob

INSERT INTO user (name,dob) VALUES ('$name','$dob');

...that's all there is to it 🙂
BTW it's good practice to always name the variables you are inserting, as above. What if you add or delete a field? you don't have to worry about it this way. And you don't have to worry about what the exact order in the database is.

[catgurl_ashley]

thanx guys now i have that autoincrement down.....

gotta get the table to convert to it tho... wonder how im gonna do that.... I may have to deleat and restart just like lazerlight said...unless sumone can give me deep instructions how to make it primary key, i dont got a clue.....so its kewl.... i can just redo the tables

THANX GUYS YOUVE BEEN A REAL HELP!!!

[tbach2]

one way you can do that is to create another table with same structure + autoincrement, then

INSERT INTO new_table (field1,field2,etc) SELECT field1,field2,etc FROM old_table