[Solved]Code question (involves MySQL sorta)

[Zephius]

Hello everyone. I'm tinkering around with MySQL a little bit here and ran into snag not really with MySQL itself but it involves it. What I want to do is write a chunk of code that when a person purchases a backpack it will create a new item in the database in numerical order but when it reaches a certain number ex. 500 when the next item is created it will go back to number 1 and go back through the numbers so I don't have an excessive number title for the backpack.

example

person 1 buys 3 backpacks it will create 3 backpacks and their inventory in the database in numerical order backpack 1, 2, and 3

person 2 buys a backpack it will create backpack 4 in the database

but when it reaches backpack 500 it will restart the labeling at 1 and check to see if that number exists until it reaches 500 again. (if they are all taken it will continue at 501 until 600 or something like that)

Confused? I am 0o

PLEASE HELP :' (

[Matt]

I get what you mean, you want it to go back through and check the 500 to make sure there is a need or no need to go further with the numbers stored.

Sadly, I know nothing about mysql. Sorry.

[Zephius]

I get what you mean, you want it to go back through and check the 500 to make sure there is a need or no need to go further with the numbers stored.

Sadly, I know nothing about mysql. Sorry.

Exactly!

and

BUMMER >.

[Cruzn]

Are you really worried about having 500 entries into a table? Pointless to try and reuse numbers. It would be more work then just finding the last entry of the table and adding 1. Think about it. . . "Last number is 500, let me go back through each and every one to see if there is one I can reuse. . ." or "Last number is 500, 501 kthxbai." Why would you reuse numbers? Unless you constantly look through to find the IDs or stuff, you don't have do that. Just call the backpack data by their ID, which would be stored in their account information, no?

[Zephius]

Are you really worried about having 500 entries into a table? Pointless to try and reuse numbers. It would be more work then just finding the last entry of the table and adding 1. Think about it. . . "Last number is 500, let me go back through each and every one to see if there is one I can reuse. . ." or "Last number is 500, 501 kthxbai." Why would you reuse numbers? Unless you constantly look through to find the IDs or stuff, you don't have do that. Just call the backpack data by their ID, which would be stored in their account information, no?

There would be no limit as to how many backpacks one could own so those with higher levels that were allowed to carry more weight could carry 20 backpacks all filled with items. Eventually the database would get gigantic and I would like to recycle the titles to prevent bloat come backup time. Obviously this isn't an immediate issue but why wait for something to go wrong?


[Edit]

After re-reading your comment I see that this could be used for malicious attacks also... perhaps I will rethink this system

[Edit]

Ok I think I got it this time! Tell me what you think. Come daily server reset it would run code that would rename all the backpacks in the database to a lower number and continue from the last one. That way I don't need to worry about an oversized variable (was my main concern). Unless I'm totally missing an easier way to do this. =/

[Cruzn]

Ok I think I got it this time! Tell me what you think. Come daily server reset it would run code that would rename all the backpacks in the database to a lower number and continue from the last one. That way I don't need to worry about an oversized variable (was my main concern). Unless I'm totally missing an easier way to do this. =/

Yes, if you really feel the urge to do keep the entries low, this would be the best way.

I would do it as such: (in php)

Code:

$sql = "SELECT * FROM `backpacks`";
$result = mysql_query($sql);

while( $row = mysql_fetch_array($result) ){
    if( $row['used'] != 0 ){
        $cleaned[] = $row;
    };
};

// You'd drop the information alre(ady in the database by this point.

foreach( $cleaned as $key => $value){
    foreach( $cleaned[$key] as $k => $v){
        // in here all the inserting into the database would happen.
    };
};

for
. . . blah blah

So, basically you take every entry in the database and cycle through it. If the variable "used" is set to 0 (mean it is no longer used) it is not written to the $cleaned array. Then, drop all the data in the table and finally you would take that array and put it into the database using new IDs.

Still, pretty pointless to do this anyway. You really won't see a huge difference unless your users will end up having 1,000,000,000,000s of backpacks. (That is, of course, if you are looping through every entry in the table to find which backpacks belong to each user; as opposed to having the user's backpack IDs stored in a file and just look up those IDs in the backpack table.) If your concern is about having more than an integers worth of IDs, then you could assign sub-IDs. Every 500, the next set will be given a sub ID 1 higher than before: ID[500], SubID[1] and then ID[1], SubID[2]. =P

[Zephius]

Thanks for the help Cruzn! You rock!

[Spodi]

If it did become a problem, say like a year down the line and your numbers were in the millions, you could easily write a script that will find all the unused numbers, and move the highest indexes down into those unused slots. It'll require just turning down the server for a minute or two, running the script, then you're set for another year.

[Zephius]

If it did become a problem, say like a year down the line and your numbers were in the millions, you could easily write a script that will find all the unused numbers, and move the highest indexes down into those unused slots. It'll require just turning down the server for a minute or two, running the script, then you're set for another year.

Thats what I was thinking : )