Dealing with ranges of numbers

[Evolver]

Hello,

 

First post and squeeky clean newb to mysql/php here.

 

MySQL 4.1 / PHP 5.2

 

I have a book i'm using for reference but i'm a little stuck on how to go about a project i have.

 

My client is printing out a bunch of numbered cards. 0000-9999 (well probably not all the way to 9999 but it's 4 digits)

 

She wants to be able to assign groups of 50 to a client as they get onboard. So client 1 would have 0000-0049 and then information would be broken down further based on that clients number range. It's possible in the future she may need to assign another ranger of 50 to the same client. So it could be that client 1 has 0000-0049 and 0150-0199 for example.

 

The best solution i have come up with is to create one table with one column for the number 0000-9999 and one column for the client ID's that will be assigned to a range of 50.

 

I can't figure out how to insert all of the rows into the database, 0000-9999. Is there a loop i can use?

 

I'll also have to be able to assign a range of 50 to a client ID through a form box, i think i may be able to figure this one out using PHP.

 

Do you think i am going about this correct? I'm open to your experienced suggestions.

 

Thanks, sorry for the newbness.

[AjBaz100]

I would do it in 3 tables,

Clients Table,

user_id | name

1        | userA

2        | userB

 

 

Cards Table

card_id | some_other_colum_for_cards.

0        | Somevalue   

1        | ..

...

9999

 

Clients_cards Table.

user_id | card_id

1        | 0

1        | 150

 

 

 

The above tables are saying that userA has a bunch of cards starting at 0 and 150.

 

In php, to insert

for( $i = 0; $i < 100000; $i++ )

{

  $query = " INSERT INTO Clients_cards ( ".$SomeUserId." , ".$SomeCardId." );

  $result = mysql_query($query);

}

 

 

for the user_id and cards_id in the other two tables, these columns should be auto increment and therefore the query would be such:

 

$query = " INSERT INTO Clients ( NULL, ".$SomeName." )

 

 

[AjBaz100]

Actually that insert query won't work, it should be

 

INSERT INTO Clients_cards VALUES( ".$SomeUserId." , ".$SomeCardId." );

 

 

[Evolver]

In regards to the Cards Table - how do i insert all of the rows for the card_id's?

[AjBaz100]

you could use a for loop as in the php snippet above.

[Evolver]

$conn = mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
for ($i = 0; $i <= 9999; $i++) {
  mysql_query("INSERT INTO table(fieldname) values('$i')");
}

 

Thanks i found what i needed, i figured it would be based off of that.

 

Do you guys normally just make a one time php script that you run to have some effect on the database?

 

Just curious how the workflow goes.

[fenway]

$conn = mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
for ($i = 0; $i <= 9999; $i++) {
  mysql_query("INSERT INTO table(fieldname) values('$i')");
}

PLEASE don't use this code!  You should only issue a single INSERT statement, with multiple VALUES().