Assigning random data to a database in PHP (PHP & MySQL)

How to assign names to 10,000 fictional stars in a databse using PHP?

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 2266 Views - Last Post: 16 May 2009 - 01:59 PM Rate Topic: -----

#1 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Assigning random data to a database in PHP (PHP & MySQL)

Posted 14 May 2009 - 06:42 PM

I'm working on an ambitious web game project that currently requires me to name 10,000 star systems that the player will be able to visit. Due to the large number of stars, I have written several procedural PHP scripts to automate the generation of the stars' X and Y coordinates within a two-dimensional fictional galaxy. The universe is a 5,000 units by 5,000 units square plot.

Now I want to name the stars, preferably with something better than "Star # 1,076."

I've decided that I can realistically come up with 1,000 names, and then assign each name to ten random stars in the database, using roman numerals I - X to differentiate between the 10 stars per set. (i.e. "Ursa I" "Ursa II" etc.)

Rather than just randomly assigning these names to the stars in the database, I'd like to keep the star names close together, in clumps or constellations. Assuming that the stars' positions are already determined, what would be the best method for doing this?

The rough idea I have in my mind would be to make a for loop, SELECT some stars from the database that fall within some limit like BETWEEN x_small AND x_big AND y_small AND y_big, then assign some of the names to those stars.

The first problem is that the stars are clumped heavily in the middle of the galaxy, and sparsely around the perimeter. So, at any given time the number of stars pulled from a 100x100 unit plot of space could be 3 stars or 200 stars. Even if I narrowed the scope of each iteration of the loop to something like 10x10 units, I'd still need to keep track of which names have been used already, especially if Ursa I, II, and III get applied to one section of space, and in the next section of space there are another 12 stars (who would be named Ursa IV-X and Therg I-V).

The second (more minor) problem is that if I pull a plot of space out of the database and that plot has 100 stars in it, then assign the names, those stars' names will be intermingled rather than clumped or segregated. The solution to this seems to be that I need to pull much smaller plots of space. But if I do that, the way I iterate through the database would have to be better than just starting at x = 0 and going to x=5000, because there will be a definete linear association to the naming process, (i.e. Ursa I-X appear from left to right along the top of the visual map, rather than spreading out from a single source).

Maybe I'm going about it all wrong. ;) What do you guys think?

Is This A Good Question/Topic? 0
  • +

Replies To: Assigning random data to a database in PHP (PHP & MySQL)

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4187
  • View blog
  • Posts: 11,847
  • Joined: 18-April 07

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 14 May 2009 - 07:18 PM

Well the big problem you are having here is that the stars are clumped together in the middle. So what you need to do is separate them enough to then be able to apply some x,y ranges to clump the stars as you want them.

To do this separating of the stars, just for processing, rescale your x-axis and y-axis by some factor and then replot your points using the same rescale factor.

So for instance, assume we have a universe which is 2 units by 2 units. In this universe we have to stars. One that sits at coordinates .4, 1.1 and one that sits at .8, 1.3... pretty close to one another if we try to chop up the universe in 1 unit segments. The two stars would be in the same chunk of space.

But if we apply a scale where we take the same 4sq unit of space and put it into a universe of 16 sq units (doubling the x-axis and the y-axis) and apply the same factor to the stars so that we have one that sits at .8, 2.2 and another at 1.6, 2.6.

If you plot these out on now a 16 sq unit grid you will notice that they sit in two different sections. The more we expand the grid, the more the stars move apart (because we are zooming in on them) and then it will make them breakable into clusters.

The one draw back is that you will have more empty sectors of space out there, but isn't that what half the universe is made up of? Remember the big bang is expanding and bodies move away from one another. So you can think the same thing. Stars were once cluttered in the beginning and now separated by long distances now.

I wouldn't worry about getting uniform clusters because that is not realistic. A constellation may have 2 stars or it may have 20. As long as the maximum isn't too large then I wouldn't worry about if you have 1 star in one constellation verses 20 in another.

Hopefully I am making my thought clear here.

:)
Was This Post Helpful? 1
  • +
  • -

#3 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 14 May 2009 - 08:11 PM

Thanks for your reply!

The problem that I have isn't with the positioning of the stars. I actually have a complex star layout already implemented in the game engine. As a better example, I've attached an image of the galaxy. This image is an actual star by star representation of the stars in the database (I wrote a PHP script that pulls all 10k stars' coordinates out of the database and makes white dots on a black background where the stars would be).

I can't change the positioning of the stars, because other code in the game relies on this information. Also, the stars' coordinates are all integers on a 5000x5000 grid, not floats or fractions. Example: x = 236, y = 1298.

So I have the ability to pull a plot of space 10 units by 10 units, and if there was a star in every unit (which they aren't that close together) I'd have an array of 100 stars. Also, there are areas of space where a 10x10 section would have no stars, or very very few.

The logistics of it--in regard to naming the stars in groups in a procedurally pleasing way--is what eludes me.

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 15 May 2009 - 04:51 AM

I found this today:

http://en.wikipedia...._points_problem

That should help me name in clusters, solving problem 2 that I had. Now I just need to figure out the best method of chopping up the space and looping, and keeping track of the names used.
Was This Post Helpful? 0
  • +
  • -

#5 grimpirate  Icon User is offline

  • Pirate King
  • member icon

Reputation: 149
  • View blog
  • Posts: 714
  • Joined: 03-August 06

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 15 May 2009 - 01:15 PM

If I may suggest an outside the box approach for your star naming. Assign the random names using a hash, something like md5 or sha. Any 32 bit hash should be good enough. Then what you do is as people play the game, those that first encounter or travel to a star assign it the name that they want. Like discoverers of the star. I grant you that you may get some names you don't want like WangOfTheCosmos or something lol, but I suppose if you let people also complain about a star name then you can assign it another name or wait for administrative approval of the name. Perhaps this will add a unique feature to your game wherein your actual users have named the universe they play in.

This post has been edited by grimpirate: 15 May 2009 - 01:17 PM

Was This Post Helpful? 0
  • +
  • -

#6 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 15 May 2009 - 01:30 PM

View Postgrimpirate, on 15 May, 2009 - 12:15 PM, said:

If I may suggest an outside the box approach for your star naming. Assign the random names using a hash, something like md5 or sha. Any 32 bit hash should be good enough. Then what you do is as people play the game, those that first encounter or travel to a star assign it the name that they want. Like discoverers of the star. I grant you that you may get some names you don't want like WangOfTheCosmos or something lol, but I suppose if you let people also complain about a star name then you can assign it another name or wait for administrative approval of the name. Perhaps this will add a unique feature to your game wherein your actual users have named the universe they play in.

I love the concept, but hate the probable outcome of leaving the naming in the hands of the masses. I think I'll name the stars myself, and add a 'subtitle' that identifies the username of the person who first identified the star. Thanks for the idea!

BTW: I'm working on a viable solution to the main issue, and for the sake of it I'll post the code I come up with later. Still open for suggestions, though!
Was This Post Helpful? 0
  • +
  • -

#7 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2834
  • View blog
  • Posts: 9,740
  • Joined: 08-August 08

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 15 May 2009 - 01:38 PM

How about a compromise? Let the users choose from a list of names when they discover a previously unvisited star.
Was This Post Helpful? 0
  • +
  • -

#8 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 08:19 AM

Okay, I've developed an array of 1000 star names, and I've got the clusters issue worked out in my head. Now I just have one final issue before attempting this:

Having exactly 10 stars per name seems too rigid. I'd like to have one name be associated with 6 stars, and another with 14, etc. etc.

How would I go about assigning a random number of stars to each name (5-15 stars per name) and still end up naming all 10k stars, and using all 1k names that I have developed? I'm going to write a loop to do the name assigning for me, so how would I keep track of the "total" named stars to ensure that I don't run out of names during the loop, or assign so many stars that I run out of stars and don't use all 1k names? :crazy:
Was This Post Helpful? 0
  • +
  • -

#9 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2834
  • View blog
  • Posts: 9,740
  • Joined: 08-August 08

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 08:32 AM

If you start by selecting a random number of stars (from 5 - 15 ) and assigning the first name, and continue doing this through out, then you should average about 10 stars per name in the long run. You can make small adjustment in the number used for say the last 20 groups so that if you've been averaging 9.9 stars per name you'll need more and if it's been 10.2 you'll need less.
Was This Post Helpful? 0
  • +
  • -

#10 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 08:34 AM

I seem to have solved the tracking with this code:

	$star_count = array();
	for ($i=0;$i<10000;$i++) {
		if (count($star_count) < 1) {
			$star_count = array('5', '6', '7', '8', '9',
							'11', '12', '13', '14', '15');
			shuffle($star_count);
		}
		$num_stars = array_shift($star_count);
	}



5+6+7+8+9+11+12+13+14+15 = 100

So, I've got random-ish star counts that will equal exactly 10k stars after the loop. I think. ;)
Was This Post Helpful? 0
  • +
  • -

#11 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2834
  • View blog
  • Posts: 9,740
  • Joined: 08-August 08

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 08:41 AM

Clever!
Was This Post Helpful? 0
  • +
  • -

#12 Wimpy  Icon User is offline

  • R.I.P. ( Really Intelligent Person, right? )
  • member icon

Reputation: 159
  • View blog
  • Posts: 1,038
  • Joined: 02-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 08:53 AM

I came up with this:
<?php
$num_loops = 500;
$opposites = Array();

$stars = 0;
$names = 0;

for($i = 0; $i < $num_loops; $i++)
{
	$rand = mt_rand(1, 19);
	$opposites[$i] = 20 - $rand;
	for($j = 0; $j < $rand; $j++)
	{
		// Name star
		$stars++;
	}
	$names++;
}

for($i = 0; $i < $num_loops; $i++)
{
	for($j = 0; $j < $opposites[$i]; $j++)
	{
		// Name star
		$stars++;
	}
	$names++;
}

echo "Stars: ".$stars."<br />";
echo "Names: ".$names;
?>


Which will give you exactly 10000 stars with 1000 different names with one disadvantage of 2 loops doing almost the same thing. But I'm too late anyway I guess! :P

And I also seem to have missed your requirement (1-15) stars per name.

This post has been edited by Wimpy: 16 May 2009 - 08:58 AM

Was This Post Helpful? 0
  • +
  • -

#13 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4187
  • View blog
  • Posts: 11,847
  • Joined: 18-April 07

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 09:00 AM

View Postyoung.steveo, on 14 May, 2009 - 07:11 PM, said:

Thanks for your reply!

The problem that I have isn't with the positioning of the stars. I actually have a complex star layout already implemented in the game engine. As a better example, I've attached an image of the galaxy. This image is an actual star by star representation of the stars in the database (I wrote a PHP script that pulls all 10k stars' coordinates out of the database and makes white dots on a black background where the stars would be).

I can't change the positioning of the stars, because other code in the game relies on this information. Also, the stars' coordinates are all integers on a 5000x5000 grid, not floats or fractions. Example: x = 236, y = 1298.

So I have the ability to pull a plot of space 10 units by 10 units, and if there was a star in every unit (which they aren't that close together) I'd have an array of 100 stars. Also, there are areas of space where a 10x10 section would have no stars, or very very few.

The logistics of it--in regard to naming the stars in groups in a procedurally pleasing way--is what eludes me.


I understood the problem and the decimals were only for example purposes. My approach will still work and I wasn't saying to change the coordinates in the database itself, just recalculate positions based on an expanded grid for name assigning only.

I also understood that pulling one section of space may yield too many or not enough stars, that is the reason I proposed the expansion of the stars to make that job easier.

I saw your graphic and it is exactly what I had envisioned you came up with which is why I gave you the solution above. So try to give it a second review, it may sound harder than it really is. ;)
Was This Post Helpful? 0
  • +
  • -

#14 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 09:58 AM

Sorry, Martyr2! I guess what I meant was that with or without scaling, some of my problems would still exist. Your post was helpful!
Was This Post Helpful? 0
  • +
  • -

#15 young.steveo  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 14-May 09

Re: Assigning random data to a database in PHP (PHP & MySQL)

Posted 16 May 2009 - 11:39 AM

Well, I've stumped myself. First of all, calculating distance between thousands of stars is so memory intensive that I needed to break the work into smaller chunks to prevent timing out... no problem there.

Here is the code that I wrote to use the first 100 names in my .csv list and name a few stars. The results were not as I anticipated. It seems that there's a bug somewhere that overwrites some of the names already assigned in the database with new names, essentially causing far fewer names to be assigned, and causing breaks in the numbering as well (I end up with "name II", "name IV" and "name XII" on three stars rather than seeing 12 stars with the name).

In the database, I started by naming all the stars 'test'

So during the loop below, the sql statements should be pulling only those stars with the name 'test' and not stars that have already been named in previous iterations of the loop.

function get_csv($filename){
	$handle = fopen($filename, "r");
	$hog = array();
	while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		$hog[] = $data[0];
	}
	fclose($handle);
	return $hog;
}

//connect to database
$db = mysql_connect("***", "***", "***");
mysql_select_db("name",$db);

$master_name_array = get_csv('star_list.csv');
$name_array = array_chunk($master_name_array, 100); //break the array into chunks of 100 for memory reasons

$star_count = array('5', '6', '7', '8', '9', '11', '12', '13', '14', '15');
shuffle($star_count);

$star_array = array();
$roman = array(' I', ' II', ' III', ' IV', ' V', ' VI', ' VII', ' VIII', ' IX', ' X', ' XI', ' XII', ' XIII', ' XIV', ' XV');

foreach ($name_array[0] as $name) {
 	// the LIMIT 200 is here so that the code need not try to find the distances between
	// every single star on each iteration.
	$sql_check = "SELECT * FROM locator_systems WHERE name = 'test' LIMIT 200";
	$result = mysql_query($sql_check);

	//load stars into array
	while ($star = mysql_fetch_array($result)) {
		$star_array[] = $star;
	}

	// reload star count if empty (for tracking)
	if (count($star_count) < 1) {
		$star_count = array('5', '6', '7', '8', '9', '11', '12', '13', '14', '15');
		shuffle($star_count);
	}
	$num_stars = array_shift($star_count);

	//get coordinates from the first star in the array,
	//and the closest ones to it
	$active_star = $star_array[0];
	foreach ($star_array as $second_star) {
		$id = $second_star['id_systems'];
		$close_list[$id] = pow(($second_star['x'] - $active_star['x']), 2) + pow(($second_star['y'] - $active_star['y']), 2);
	}

	// Sort the stars by distance.  The astute among you will notice that I have
	// not completed the distance formula by finding the square root.  Since literal
	// distance is not important, I've skipped this step for memory reasons.
	// The list should still be in the order of closest to farthest without the sqrt()

	asort($close_list);

	// break the list into chunks of length $num_stars, since that is how many
	// stars that really need to be named in this iteration, not all 200.  I'm preserving
	// keys so that I can find the key values (which should be the id of the stars in the
	// database, unless I've done something wrong)

	$close_list_chunk = array_chunk($close_list, $num_stars, true);
	$holder = $close_list_chunk[0];
	$stark_list = array_keys($holder);

	// this should name the current set of stars
	$i = 0;
	foreach ($stark_list as $id_systems) {
		// create the name with the roman numeral
		$new_star_name = $name . $roman[$i];

		$sql_update = "UPDATE locator_systems SET name = '$new_star_name' WHERE id_systems = '$id_systems'";
		$result = mysql_query($sql_update);
		$i++;
	}

	// clear the star array for the next round.  Once the loop starts again,
	// star array should be empty, and the new set returned from the SELECT
	// query should not include the stars that have just been named, since
	// their name is no longer 'test'

	unset($star_array);
	$star_array = array();
}



I hope it's just something simple that I've missed.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2