Mysql 500 + queries?

[gurpreet]

Ok so I need to put in some IDS into my 'free_ids' table. I found the mod on CE but it only started from the 2000's and I have about 500 or so under 2000 that are missing. I really need help with this because I don't want to sit there typing "insert into free_ids values 5" then 6 then 7 etc. BTW There are stuff like 20-30, 36-67, 89-199 so I need a way to do that. Just something like in excel it's D6:D10

Screenshot added.

[Zeggy]

Use a for loop between the values you want to insert for (eg. 1-2000)

Use your regular insert query for each ID, and add this to the end of the query:

WHERE NOT EXISTS(SELECT `ID` FROM `players` WHERE `id`=$i)

So something like this:

INSERT INTO `free_ids` (`id`) VALUES ($id) WHERE NOT EXISTS(SELECT `ID` FROM `players` WHERE `id`=$i);

Where $i is the ID that you are inserting.

This isn't very efficient as it will still be querying the database for every single ID even if nothing's being inserted. But it's a lazy way to do it, and if you don't mind that it takes some time.

[seanybob]

What Zeggy said.

Or, for the more mysql illiterate:

$x=HighestIDYouWantToGoTo;

$i=0;

while($i<$x)

{

$fullID=$db->num_rows($db->query("SELECT userid FROM users WHERE userid=$i"));

if(!$fullID){$db->query("INSERT INTO free_ids VALUES($i)");}

$x++;

}

Not nearly as efficient as Zeggy's would be, but as you're only running it once shouldn't really matter.

[CrazyT]

If you want to give users on sign up a lower ID, if there is one there is no need for all that for/while loops.

Nyna, made a SQL on here that does it. Search the forum ;D

If you don't no how to make it work, i say go back to reading the manuals.

[gurpreet]

What Zeggy said.

Or, for the more mysql illiterate:

$x=HighestIDYouWantToGoTo;

$i=0;

while($i<$x)

{

$fullID=$db->num_rows($db->query("SELECT userid FROM users WHERE userid=$i"));

if(!$fullID){$db->query("INSERT INTO free_ids VALUES($i)");}

$x++;

}

Not nearly as efficient as Zeggy's would be, but as you're only running it once shouldn't really matter.

This just inserted about 200,000 id 0's

[Zeggy]

LOL

Change the $x++ to $i++

[a_bertrand]

All that is not very smart... If you have an auto_increment table primary key (as I suspect) you may set the value you want for the next row, and it will then continue from there. Without the need to have empty or useless rows in betwee:

[mysql]

ALTER TABLE tbl AUTO_INCREMENT = 100;

[/mysql]

BTW that was a 10 sec google search ;)

[Magictallguy]

If you don't have an auto_increment (though why you wouldn't is beyond me), you can use LAST_INSERT_ID().

It's a good MySQL function that works similar, in principal, to an auto_increment

[Zeggy]

All that is not very smart... If you have an auto_increment table primary key (as I suspect) you may set the value you want for the next row, and it will then continue from there. Without the need to have empty or useless rows in betwee:

[mysql]

ALTER TABLE tbl AUTO_INCREMENT = 100;

[/mysql]

BTW that was a 10 sec google search ;)

If you set the auto_increment value to something lower than MAX (`id`)+1, it will automatically be reset to MAX (`id`)+1. Auto_increment doesn't fill up gaps from deleted rows. It starts counting from at least the largest auto_increment value in your table.

 

If you don't have an auto_increment (though why you wouldn't is beyond me), you can use LAST_INSERT_ID().

It's a good MySQL function that works similar, in principal, to an auto_increment

Actually last insert ID requires an auto_increment column, and only returns the ID from the last inserted row during that connection. Meaning it will only be useful if you've just inserted a row on the same page.

[Magictallguy]

Ah, I didn't know that.

Well, you learn something everyday xD

[a_bertrand]

Then I didn't understood the problem :D