improve code

Desbrina

Currently i have a script ran via cron, which does what i want, but according to my host its running around 290,000,000 a day, which i don't want

This is the scripts

cron.php

<?php

include "/home/calona/calona/includes/db_config.php";

// Get all users
$csql = "SELECT ID FROM users";
$cresult = mysql_query($csql) or die(sql_error($csql));
while($cron = mysql_fetch_array($cresult)) {
	// Run the eggs update;
	include "/home/calona/calona/includes/function_eggs.php";
}	
?>

function_eggs.php

<?php

// Set up variables
$common = $num = $count = 0;
$uncommon = 80;
$rare = 90;
$urare = 96;
$ids = array();

// Set the user id
$uid = $cron['ID'];

// Get the eggs
while($count < 5)
{
	$num = rand(1,100);
	$type = '';

	if($num < $uncommon) // Common
	{
		$type = 'Common';
	}
	elseif($num < $rare) // Uncomon
	{
		$type = 'Uncommon';
	}
	elseif($num < $urare) // Rare
	{
		$type = 'Rare';
	}
	else // Ultra-Rare
	{
		$type = 'Ultra-Rare';
	}

	$sql = "SELECT c.ID
	FROM creatures AS c
	RIGHT JOIN user_access AS u ON u.cid = c.ID AND u.uid = '".$uid."'
	WHERE c.rarity = '".$type."'
	AND c.type = 'Egg'
	ORDER BY RAND( )
	LIMIT 1";

	$result = mysql_query($sql) or die(sql_error($sql));
	while($row = mysql_fetch_array($result))
	{
		$ids[] = $row['ID'];
		$count++;
	}
}

// Insert these into the database		
$sql = "INSERT INTO eggs (uid, id1, id2, id3, id4, id5) VALUES 
(".$uid.", ".$ids[0].", ".$ids[1].", ".$ids[2].", ".$ids[3].", ".$ids[4].")";
mysql_query($sql) or die(sql_error($sql));

?>

What this is supposed to do is select 5 random creatures that the user has access to depending on the types

The problem occurs if the user doesn't have any of the specific type, it reruns the query until it gets 5

Can any one help me improve this

jumpdesign

I wasn't quite sure why you were using a random number to detect if it was less than 5 - maybe I have misunderstood?

My understanding is the following:

1) For every user record in the users table you need to:
2) Get 5 random records from the creatures table that are associated to the user id
3) And only if 5 have been found, insert them into the Eggs table

If that is correct, then this should get you there (or therabouts as I can't test it)

// Get all users
$csql 		= "SELECT ID FROM users";
$cresult 	= mysql_query($csql) or die(sql_error($csql));
$ccount		= mysql_num_rows($cresult);

// if we have more than one user  from the query above, then carry on...
if ($ccount > 0) {
	// setup the insert query string
	$insertqry		=	"INSERT INTO eggs (uid, id1, id2, id3, id4, id5) VALUES ";

// for each record from the users query, we loop...
while($row = mysql_fetch_array($cresult)) {
	$uid		=	$row['ID'];	// user id

	// now get 5 random records from the creatures table that are linked to the user id
	$sql	=	"	SELECT c.ID
					FROM creatures AS c
					INNER JOIN user_access AS u ON u.cid = c.ID AND u.uid = '".$uid."'
					WHERE c.type = 'Egg'
					ORDER BY RAND()
					LIMIT 5";
	$res	=	mysql_query($sql);
	$cnt	=	mysql_num_rows($res);

	// if we have exactly 5 for that user, then insert them into the eggs table
	if ($cnt == 5) {
		while ($datarow = mysql_fetch_array($res)) {
			// store id's into array
			$id_array[]	=	$datarow['ID'];
		}
		mysql_free_result($res);

		// finish off the insert query
		$insertqry 		.= "(".$id_array[0].", ".$id_array[1].", ".$id_array[2].", ".$id_array[3].", ".$id_array[4].", ".$id_array[5].")";	
		$insertresult 	= mysql_query($insertqry);
	}
}
mysql_free_result($cresult);
} else {
	echo "<p>There are no records..</p>";
}

You may need to tweak the inster query to suit your needs but it should be close enough.

jumpdesign

I wasn't quite sure why you were using a random number to detect if it was less than 5 - maybe I have misunderstood?

My understanding is the following:

If that is correct, then this should get you there (or therabouts as I can't test it)

// Get all users
$csql 		= "SELECT ID FROM users";
$cresult 	= mysql_query($csql) or die(sql_error($csql));
$ccount		= mysql_num_rows($cresult);

// if we have more than one user  from the query above, then carry on...
if ($ccount > 0) {
	// setup the insert query string
	$insertqry		=	"INSERT INTO eggs (uid, id1, id2, id3, id4, id5) VALUES ";

// for each record from the users query, we loop...
while($row = mysql_fetch_array($cresult)) {
	$uid		=	$row['ID'];	// user id

	// now get 5 random records from the creatures table that are linked to the user id
	$sql	=	"	SELECT c.ID
					FROM creatures AS c
					INNER JOIN user_access AS u ON u.cid = c.ID AND u.uid = '".$uid."'
					WHERE c.type = 'Egg'
					ORDER BY RAND()
					LIMIT 5";
	$res	=	mysql_query($sql);
	$cnt	=	mysql_num_rows($res);

	// if we have exactly 5 for that user, then insert them into the eggs table
	if ($cnt == 5) {
		while ($datarow = mysql_fetch_array($res)) {
			// store id's into array
			$id_array[]	=	$datarow['ID'];
		}
		mysql_free_result($res);

		// finish off the insert query
		$insertqry 		.= "(".$id_array[0].", ".$id_array[1].", ".$id_array[2].", ".$id_array[3].", ".$id_array[4].", ".$id_array[5].")";	
		$insertresult 	= mysql_query($insertqry);
	}
}
mysql_free_result($cresult);
} else {
	echo "<p>There are no records..</p>";
}

You may need to tweak the inster query to suit your needs but it should be close enough. I have also noticed there is no association in your Eggs table with users which seems strange.

Good luck.

Desbrina

Basically, for every user, it gets 5 eggs that the user has access to. Eggs can be duplicated in the eggs table, so they could have the same egg appear twice, or even 5 times if they only have access to 5 eggs

the eggs table has the user id in (uid) and the id of the 5 eggs (cid1-cid5)