Page 1 of 1

Loops

Posted: Sat Dec 03, 2005 6:25 pm
by InnerShadow
i have this code to make updates to my database:

Code: Select all

<?php
$conn = mysql_connect("localhost", "root", "");
	mysql_select_db("countries", $conn);

	$result = mysql_query("SELECT SmallPlantation, SmallPlace, MediumPlantation, MediumPlace, LargePlantation, LargePlace, BasicRailSystem, ModerateRailSystem, IntricateRailSystem FROM improvements WHERE country = 'United States'") 
	  or die(mysql_error()); 
	while($row = mysql_fetch_assoc($result)) 
	{ 
  
  	 $SmallPlantation = $row['SmallPlantation'];
	 $SmallPlace = $row['SmallPlace'];
  	 $MediumPlantation = $row['MediumPlantation'];
	 $MediumPlace = $row['MediumPlace'];
  	 $LargePlantation = $row['LargePlantation'];
	 $LargePlace = $row['LargePlace'];
  	 $BasicRailSystem = $row['BasicRailSystem'];
  	 $ModerateRailSystem = $row['ModerateRailSystem'];
  	 $IntricateRailSystem = $row['IntricateRailSystem'];
	}
$conn = mysql_connect("localhost", "root", "");
	mysql_select_db("countries", $conn);

	$result = mysql_query("SELECT SmallPlantation, MediumPlantation, LargePlantation FROM bonuses WHERE country = 'United States'") 
	  or die(mysql_error()); 
	while($row = mysql_fetch_assoc($result)) 
	{ 
  	 $SLocation = $row['SmallPlantation'];
	 $MLocation = $row['MediumPlantation'];
	 $LLocation = $row['LargePlantation'];
	}
	if($SmallPlantation == "yes"){
		if($SmallPlace == "no"){
			$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
			mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
			$sql = "update baseadd set '$SLocation' = '$SLocation' + 1 where convert(country using utf8) = 'United States' limit 1"; 
			$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');

			$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
			mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
			$sql = "update bonuses set SmallPlace = 'yes' where convert(country using utf8) = 'United States' limit 1"; 
			$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');
		}//end if
	}//end if
	if($MediumPlantation == "yes"){
		if($MediumPlace == "no"){
			$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
			mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
			$sql = "update baseadd set '$MLocation' = '$MLocation' + 2 where convert(country using utf8) = 'United States' limit 1"; 
			$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');
		
			$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
			mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
			$sql = "update bonuses set MediumPlace = 'yes' where convert(country using utf8) = 'United States' limit 1"; 
			$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');
		}//end if
	}//end if
	if($LargePlantation = "yes"){
		if($LargePlace == "no")
			$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
			mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
			$sql = "update baseadd set '$LLocation' = '$LLocation' + 3 where convert(country using utf8) = 'United States' limit 1"; 
			$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');
			
			$conn = mysql_connect("localhost", "root", "") or die('Could not connect to the database: '.mysql_error());
			mysql_select_db("countries", $conn) or die('Could not select the database: '.mysql_error()); 
			$sql = "update bonuses set LargePlace = 'yes' where convert(country using utf8) = 'United States' limit 1"; 
			$res = mysql_query($sql) or die(mysql_error().'<p>SQL:<br />'.$sql.'</p>');
		}//end if
	}//end if
?>
The code there updates fields where country = 'United States'
What i want it to do is run once for united states, then again for Great Britain, and then for France. Would i use a loops for that, or something else?

Posted: Sat Dec 03, 2005 6:28 pm
by Jenk

Code: Select all

<?php

$countries = array('UnitedStates', 'GreatBritain', 'etc');

foreach ($countries as $country) {
    $sql = "SELECT * FROM table WHERE Country = '{$country}'";
    //etc..
}

?>

Posted: Sat Dec 03, 2005 6:49 pm
by John Cartwright
I personally would avoid using a loop to loop over queries.. (performance issues)

Instead I would rather have a query something along the lines

Code: Select all

$sql = 'SELECT * FROM `improvements` 
        WHERE (
                `country` = \'United States\' OR 
                `country` = \'Great Britain\' OR
                `country` = \'France\'
        )
        ORDER BY `country`';

Posted: Sun Dec 04, 2005 9:56 am
by trukfixer
why not just:

Code: Select all

$sql = "SELECT * FROM `improvements`
        WHERE country in ('United States','Great Britain','France' )
        ORDER BY `country`";