Help with more effiecient code...

Anon

Hello,

Im rather new to PHP and am trying to do something which gives me a mem allocation error so I was hoping that perhaps there is a more efficient way to do this...

What I am trying to do is grab a list of all 50 states from a database then put a list of cities in a variable ($the_state_name). I am using nested while loops to do this and it is giving me a memory error.

Here is my code....

$statelist = mysql_query("SELECT DISTINCT state FROM cities ORDER BY 'state'");

$result = mysql_query("SELECT * FROM cities ORDER BY 'city'");

$row = mysql_fetch_array($result);

while ( $staterow = mysql_fetch_array($statelist) )
{
$current_state = $staterow["state"];

while ($row["state"] == $current_state )
	${$current_state} .= "{$row["city"]} ";

Please help me make this code run a little better and be a little less strain on my server =]

Thanks in advance!
Kevin

trooper

Kevin what are you trying to do

Is it find out and print cities in each state ?

If so then let me know

Generally thought you haven't actually incremented your while loop. If you are going to use a loop stucture there needs to be a condition in it e.g while($a <4) or something along those lines

LEt me know what you're trying to do thoug and i'll lend you an hand

regards

Gary Mailer

Anon

Gary,

Thanks for your quick help....

Yea what I am trying to do is put all the cities for each state into a variable...I am going to try to explain what I want my code to do in play english
=]

It needs to grab all 50 states from the DB then for each state it needs to look in the database for records where the state field matches that state and then take the city field from that record and put it in a variable...

I changed my code around a little bit getting the code for the variable variable right this time I think...here it is:

$statelist = mysql_query("SELECT DISTINCT state FROM cities ORDER BY 'state'");

$result = mysql_query("SELECT * FROM cities ORDER BY 'city'");

$row = mysql_fetch_array($result);

while ( $staterow = mysql_fetch_array($statelist) )
{
$current_state = $staterow["state"];

while ($row["state"] == $current_state )
	$$current_state .= "{$row["city"]} ";

}

Thanks again for all your help!

trooper

OK i kynda understand it more now

So if say a city name is miami you want
it to be known as $miami ?

Am i correct ?

Let me know

Anon

Nope...close

say I have the state florida and it has Miami, Clearwater & Cape Coral. I want it to creat the variable

$florida = Miami Clearwater Cape Coral

then I will split it up and stuff later...

trooper

I get it now

By the way i'm not in the US so i'll have to believe you that there is a city called Cape Coral

Watch this space

Gary Mailer

Anon

Gary,

Allright....thanks again for your help!

Please let me know what you come up with...

-Kevin

Anon

If this is all coming from the same 'city' table, why do you need two queries? Just say

SELECT state, city from cities ORDER BY state;

or, if you want the cities sorted, too, then say ORDER BY state, city.

As far as gathering the variables, it might be simpler to use an associative array:

while (list($state,$city) = mysql_fetch_row(...))
  {
  $data[ $state ] .= $city . ' ';
  }

trooper

true Kirk true

Anon

Kirk or Gary,

Sorry I am very new to this...can you please elaborate on what you said because I do not quite understand it...

Thanks again,
Kevin

Anon

one more thing...the reason I am doing this using 2 queries is because I need to SELECT DISTINCT state from the database because there are more than one records with the state Florida but they all have different cities...

And I dont really understand what you were saying with the associative array...like I said I am new...

Thanks again!
Kevin

Anon

If you issue the single query

SELECT state, city from cities ORDER BY state, city;

Then you will get rows in sequence like this:

...
AZ | Phoenix
AZ | Tempe
...
WA | Seattle
WA | Tacoma
...

In other words, you are getting all the states in sequence, so you don't need a separate SELECT DISTINCT query. You can just detect the arrival of a new state when it happens as you read through the rows.

As far as the array, I'm just suggesting instead of creating a whole bunch of separate variables ($arizona, $florida, $washington) just create a single array indexed by the state names:

$data['arizona']

Each element of the array will contain the string of concatenated city names for the given state that indexes it.

Anon

Kirk,

Can you please give me an example of how to do this....

Thanks a lot!
Kevin