[SOLVED] sorting and cleaning array from 4 DB fields

[stmosaic]

I have 4 fields in my database that I am trying to combine and present as a unified dropdown. I've gotten the basic sort and display of the info to work, but don't quite know how to remove spaces caused by empty field nor how to make the dropdown unified.

 

Here's the code:

while ($row = mysql_fetch_array($result))
{
$City1=$row['City1'];
$City2=$row['City2'];
$City3=$row['City3'];
$City4=$row['City4'];

$ACity = array($City1, $City2, $City3, $City4); 
sort($ACity);
reset($ACity);
while (list($key, $val) = each($ACity)) {
echo "<option value=\"$val\">$val</option>";}

 

My output looks like this:

 

Asheville

Boone

Charlotte

 

 

Aberdeen

Pinehurst

Southern Pines

 

What I'm trying to get it to look like is:

Aberdeen

Asheville

Boone

Charlotte

Pinehurst

Southern Pines

 

Only one of the city variables will always be populated, 2-4 could be empty. I think this is where my empty lines are coming from, but I'm not sure how to account for that and remove them. Any ideas? I appreciate the input.

[lemmin]

You could check to see if it is blank:

 

while (list($key, $val) = each($ACity)) {
if ($val)
  echo "<option value=\"$val\">$val</option>";}

 

Assuming $val contains "" when it is blank.

[stmosaic]

Good thinking lemmin. That worked for clearing the spaces out of the list. Still have one small issue, the list needs to be in a pure alphabetical sort. Currently my output looks like this:

 

Asheville

Boone

Charlotte

Aberdeen

Pinehurst

Southern Pines

 

Any ideas, anyone?

[Jessica]

php.net/sort();

 

Put the $val s into an array and sort it.

[lemmin]

It is probably better to sort it using SQL. Generally it is just "ORDER BY Field," but it would be easier if you post your SQL code.

[stmosaic]

I thought I did. My first time using sort, so I may have the syntax or order messed up. I tried:

SQL code;

mysql_query("SELECT * FROM $DBTABLE WHERE State='$ReqState' ORDER BY City ASC");

 

There are 4 fields that I sort  below in the php code.

 

I tried:

 

$ACity = array($City1, $City2, $City3, $City4); 
sort($ACity);
reset($ACity);
while (list($key, $val) = each($ACity)) {
if($val){
sort($val);
echo "<option value=\"$val\">$val</option>";
}
}

 

but it still didn't work.

[lemmin]

What is happening is that you are creating more than one array and ordering them each individually (The first three are in order and the last three are in order relative to the three.) I would suggest doing it like this, instead:

while ($row = mysql_fetch_array($result))
{
    $ACity[]=$row['City1'];
    $ACity[]=$row['City2'];
    $ACity[]=$row['City3'];
    $ACity[]=$row['City4'];
}
sort($ACity);
foreach($ACity as $val)
    echo "<option value=\"$val\">$val</option>";

 

You actually don't need the ORDER BY clause in your SQL because it doesn't do anything in the end. You can't get SQL to order them because you are returning multiple lists and it can't sort the two lists together.

[stmosaic]

ok, it's starting to work, just a small issue in the output. Had to add the iff to clear blank fields.

 

while ($row = mysql_fetch_array($result))
{
$ACity[]=$row['City1'];
$ACity[]=$row['City2'];
$ACity[]=$row['City3'];
$ACity[]=$row['City4'];

sort($ACity);
foreach($ACity as $val)
   { 
   if($val){echo "<option value=\"$val\">$val</option>";}
}
}

 

Now my output is duplicating the 1st 3 cities, which is like the first row repeating.

Asheville

Boone

Charlotte

Asheville

Boone

Charlotte

Aberdeen

Pinehurst

Southern Pines

[stmosaic]

My error on the output, it's really:

Asheville

Boone

Charlotte

Aberdeen

Asheville

Boone

Charlotte

Pinehurst

Southern Pines

[lemmin]

It looks like your query is returning results that you don't necessarily need. Put a print_r($row) in the while loop and see how many times it shows stuff, and if the stuff is what you want it to be returning (if you view source it is easier to read.)

[stmosaic]

Lemmin, here's the print_r output. It looks kind of strange to me.

                    Array

(

    [0] => 1

    [iD] => 1

    [1] => Asheville

    [MarketCity1] => Asheville

    [2] => Boone

    [MarketCity2] => Boone

    [3] => Charlotte

    [MarketCity3] => Charlotte

    [4] =>

    [MarketCity4] =>

    [5] => NC

    [state] => NC

)

<option value="Asheville">Asheville</option><option value="Boone">Boone</option><option value="Charlotte">Charlotte</option>Array

(

    [0] => 3

    [iD] => 3

    [1] => Pinehurst

    [MarketCity1] => Pinehurst

    [2] => Southern Pines

    [MarketCity2] => Southern Pines

    [3] => Aberdeen

    [MarketCity3] => Aberdeen

    [4] =>

    [MarketCity4] =>

    [5] => NC

    [state] => NC

)

<option value="Aberdeen">Aberdeen</option><option value="Asheville">Asheville</option><option value="Boone">Boone</option><option value="Charlotte">Charlotte</option><option value="Pinehurst">Pinehurst</option><option value="Southern Pines">Southern Pines</option>   

[lemmin]

You didn't change the while loop, like I suggested. The while loop has to stop before you sort. I didn't even notice that in your previous post.

 

while ($row = mysql_fetch_array($result))
{
    $ACity[]=$row['City1'];
    $ACity[]=$row['City2'];
    $ACity[]=$row['City3'];
    $ACity[]=$row['City4'];
}
sort($ACity);
foreach($ACity as $val)
{
    if ($val)
        echo "<option value=\"$val\">$val</option>";
}

 

That should work.

[stmosaic]

You're a genius!!! It's working great. I changed it, but didn't realize the foreach was outside of the while. One little tweak to this, if I want to make sure the output is only unique cities, where and how do I integrate an array_unique?

[lemmin]

I think that array_unique just combines like values, so you would still be left with a single white space in the array. If you want to try it, though, you can do it anywhere after the while loop. Just do $ACity = array_unique($ACity);

[stmosaic]

lemmin, it works beautifully!! The array_unique didn't cause any white space. Thank you so very much for your help!!!!