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
lehrlize
Forum Newbie
Posts: 2 Joined: Tue Dec 16, 2003 8:17 am
Post
by lehrlize » Tue Dec 16, 2003 8:17 am
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))
{
$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";
}
}
}
}
----------------------------------------------------------------
Look at this scenario:
Countries: 150
States: 50 (each Country)
Cities: 100 (each State)
Stores: 25 (each City)
Thanks in advance.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Tue Dec 16, 2003 9:15 am
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 » Tue Dec 16, 2003 9:44 am
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
Post
by lehrlize » Fri Dec 19, 2003 8:10 am
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 » Fri Dec 19, 2003 10:06 am
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";
}
?>