Loops

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
InnerShadow
Forum Commoner
Posts: 37
Joined: Thu Nov 10, 2005 10:44 pm
Location: US

Loops

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

<?php

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

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

?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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`';
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

why not just:

Code: Select all

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