Challenging code

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
lehrlize
Forum Newbie
Posts: 2
Joined: Tue Dec 16, 2003 8:17 am

Challenging code

Post by lehrlize »

I need a dynamic generated list of countries, states, cities and stores. This is my code and is V-E-R-Y slow.

Can you optimize this code? Any Thought?

Code: Select all

$e_Deposit = "<option selected value=''>All</option>\n";
$get_countries=mysql_query("SELECT * from countries ORDER BY NameCountry", $db);
while ($mycountry = mysql_fetch_array($get_countries))
&#123;
	$e_Deposit.= "<option style="color: blue;" value=c" . $mycountry&#1111;"IDCountry"] . ">" . chop($mycountry&#1111;"NameCountry"]) . "</option>\n";
	$get_states=mysql_query("SELECT * from states WHERE Country=$mycountry&#1111;IDCountry] ORDER BY NameState", $db);
	while ($mystate=mysql_fetch_array($get_states))
	&#123;
		$e_Deposit.= "<option style="color: red;" value=s" . $mystate&#1111;"IDState"] . ">&nbsp;" . chop($mystate&#1111;"NameState"]) . "</option>\n";
		$get_citys=mysql_query("SELECT * from citys WHERE State=$mystate&#1111;IDState] ORDER BY NameCity", $db);
		while ($mycitys= mysql_fetch_array($get_citys))
		&#123;
			$e_Deposit.= "<option style="color: darkgreen;" value=c" . $myvitys&#1111;"IDCity"] . ">&nbsp;&nbsp;" . chop($mycitys&#1111;"NameCity"]) . "</option>\n";
			$get_store = mysql_query("SELECT * FROM stores WHERE City= $mycitys&#1111;IDCity] ORDER BY NameStore ASC",$db);
			while ($mystore= mysql_fetch_array($get_store))
			&#123;
				$e_Deposit.= "<option value=t" . $mystore&#1111;"IDStore"] . ">&nbsp;&nbsp;&nbsp;" . chop($myrow&#1111;"NameStore"]) . "</option>\n";
			&#125;
		&#125;
	&#125;
&#125;
----------------------------------------------------------------
Look at this scenario:

Countries: 150
States: 50 (each Country)
Cities: 100 (each State)
Stores: 25 (each City)

Thanks in advance.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

can you please explain what this code is supposed to do?
As I understand it, it creates one string with all options for country,city and stores in it. What do you do with this string?
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

no changes just making it easier to read

Code: Select all

$e_Deposit = "<option selected value=''>All</option>\n"; 
$get_countries=mysql_query("SELECT * from countries ORDER BY NameCountry", $db); 
while ($mycountry = mysql_fetch_array($get_countries)) 
{ 
   $e_Deposit.= "<option style="color: blue;" value=c" . $mycountry["IDCountry"] . ">" . chop($mycountry["NameCountry"]) . "</option>\n"; 
   $get_states=mysql_query("SELECT * from states WHERE Country=$mycountry[IDCountry] ORDER BY NameState", $db); 
   while ($mystate=mysql_fetch_array($get_states)) 
   { 
      $e_Deposit.= "<option style="color: red;" value=s" . $mystate["IDState"] . "> " . chop($mystate["NameState"]) . "</option>\n"; 
      $get_citys=mysql_query("SELECT * from citys WHERE State=$mystate[IDState] ORDER BY NameCity", $db); 
      while ($mycitys= mysql_fetch_array($get_citys)) 
      { 
         $e_Deposit.= "<option style="color: darkgreen;" value=c" . $myvitys["IDCity"] . ">  " . chop($mycitys["NameCity"]) . "</option>\n"; 
         $get_store = mysql_query("SELECT * FROM stores WHERE City= $mycitys[IDCity] ORDER BY NameStore ASC",$db); 
         while ($mystore= mysql_fetch_array($get_store)) 
         { 
            $e_Deposit.= "<option value=t" . $mystore["IDStore"] . ">   " . chop($myrow["NameStore"]) . "</option>\n"; 
         } 
      } 
   } 
}
lehrlize
Forum Newbie
Posts: 2
Joined: Tue Dec 16, 2003 8:17 am

Code explained

Post by lehrlize »

This code make an HTML List Box (Select) for Countries, States, Citys and Stores.

Example:

USA <--- Country
New Jersey <--- State
Newark <--- City
John Store <--- Store
Mary Store <--- Store

I hope this helps you to understand the purpose of the code.
choppsta
Forum Contributor
Posts: 114
Joined: Thu Jul 03, 2003 11:11 am

Post by choppsta »

There are a lot of SQL queries being executed there...

Also each one is a SELECT * even tho you're only using the name and id. I presume there's other information in those tables.

Here's how it could be done with just one query:

Code: Select all

<?php

$query = mysql_query("SELECT C.IDCountry, C.NameCountry, S.IDState, S.NameState, Ci.IDCity, Ci.NameCity, St.IDStore, St.NameStore FROM countries AS C, states AS S, citys AS Ci, stores AS St WHERE (C.IDCountry = S.Country) AND (S.IDState = Ci.State) AND (Ci.IDCity = St.City) ORDER BY C.NameCountry, S.NameState, Ci.NameCity, St.NameStore");
while ($result = mysql_fetch_array($query)) {

	if ($result["IDCountry"] != $currentCountry) {
		$drop .= "<option style="color: blue;" value=c".$result["IDCountry"].">".chop($result["NameCountry"])."</option>\n"; 
		$currentCountry = $result["IDCountry"];
	}

	if ($result["IDState"] != $currentState) {
		$drop .= "<option style="color: red;" value=s".$result["IDState"]."> ".chop($result["NameState"])."</option>\n";
		$currentState = $result["IDState"];
	}

	if ($result["IDCity"] != $currentCity) {
		$drop .= "<option style="color: darkgreen;" value=c".$result["IDCity"] . ">  ".chop($result["NameCity"])."</option>\n";
		$currentCity = $result["IDCity"];
	}

	$drop .= "<option value=t".$result["IDStore"].">   ".chop($result["NameStore"])."</option>\n"; 

}

?>
Post Reply