Displaying a field from a DB only once but...

[phaedrusGhost]

Hi all,

I am implementing a dropdown list that finds people from a "State" and "City". The states and cities are stored in the DB when a user registers. Since there are several times "California" exists in the DB, how can I display "California" in my dropdown list once and then have all cities show from the same DB.

My table is like this:

id state city
1 California Los Angeles
2 California San Fransisco
3 California San Fransisco
4 California San Fransisco
5 California San Diego
6 California San Diego
7 California San Diego
8 California San Diego
9 California San Diego
..

My SQL looks like this right now...

if ($data=='states') {  // first dropdown
          echo "<select name='state' onChange=\"dochange('cities', this.value)\">\n";
          echo "<option value='0'>==== choose state ====</option>\n";
          $result=mysql_db_query($dbname,"SELECT id, state FROM comments ORDER BY state");
          while(list($id, $name)=mysql_fetch_array($result)){
               echo "<option value=\"$id\" >$name</option> \n" ;
          }
     } else if ($data=='cities') { // second dropdown
          echo "<select name='cities' >\n";
          echo "<option value='0'>====choose cities ====</option>\n";                           
          $result=mysql_db_query($dbname,"SELECT `id`, `city` FROM comments WHERE `id` = '$val' ORDER BY `city` ");
          while(list($id, $name)=mysql_fetch_array($result)){       
               echo "<option value=\"$id\" >$name</option> \n" ;
          }
     } 
     echo "</select>\n";

It seems a GROUP BY clause will only display one city from the state. Perhaps I am doing something wrong here. I am not sure how to write the SQL to perform this action.

Any help is totally appreciated...

[svilla]

Since your ID does not uniquely identify a state or a city but rather a user's comment you will have to change your thinking a bit. You should actually read about normalizing a database, but you could get away with the following code:

try

SELECT DISTINCT state FROM comments ORDER BY state

for you first drop down box.

I am the assuming that once the user selects a state you want to show a list of the cities in that state.

For the Second drop down you should then use:

SELECT DISTINCT city FROM comments WHERE state = '$state'

Once the user has selected the appropriate state and city you can then display a list of comments by using the following query

SELECT DISTINCT city FROM comments WHERE state = '$state' and city = '$city'

[phaedrusGhost]

Much appreciated svilla... the syntax doesn't seem to be giving me proper results. it will show the NULL fields but doesn't show me the states now. I took your advice and am now reading up on normalization of databases. Perhaps this will help me get ahead. Thank you for the suggestion!

[svilla]

make sure that you are filling the drop down box correctly it should look like this now:

while(list($name)=mysql_fetch_array($result)){               
echo "<option value=\"$name\" >$name</option> \n" ;  }

// or
while($row=mysql_fetch_array($result)){               
echo "<option value=\"$row['state']\" >$row['state']</option> \n" ;  }

[vaibhav1983]

make sure that you are filling the drop down box correctly it should look like this now:

while(list($name)=mysql_fetch_array($result)){               
echo "<option value=\"$name\" >$name</option> \n" ;  }

// or
while($row=mysql_fetch_array($result)){               
echo "<option value=\"$row['state']\" >$row['state']</option> \n" ;  }

Your query will do little to help,
What if you change your dropdown of states, then???

The solution to this is AJAX, this is a very common AJAX problem.

@phaedrusGhost
If you need I can send a sample application to help with this in AJAX. If you are already similiar then please go ahead and implement it. AJAX will make your application run faster.

[phaedrusGhost]

Hi Vaibhav, I would totally appreciate that. As always, I am extremely appreciative of the help.

Much thanks...

[P0lT10n]

Hello, you code is not well coded... i mean, the logic of the code. you dont have to use an if in this case. You must use SWITCH.

switch($data){
    case "states": // $data == "states"
        echo "<select name='state' onChange=\"dochange('cities', this.value)\">\n";
        echo "<option value='0'>==== Choose State ====</option>\n";
        $result=mysql_db_query($dbname,"SELECT id, state FROM comments ORDER BY state");
        while($row=mysql_fetch_array($result)){
            echo "<option value=\"$row['state']\" >$row['state']</option> \n";
        }
    break;
    case "cities" // $data == "cities"
        echo "<select name='cities'>\n";
        echo "<option value='0'>==== Choose City ====</option>\n";
        $result=mysql_db_query($dbname,"SELECT `id`, `city` FROM comments WHERE `id` = '$val' ORDER BY `city` ");
        while($row=mysql_fetch_array($result)){
            echo "<option value=\"$row['state']\" >$row['state']</option> \n";
        }
    break;
}
echo "</select>\n";

This is more efficient, because you only read $data and then you will have 2 options in this case. If you used IF, It would be a mess. It will work the same but if you dont understand why to use this, you will learn badly. You dont use IF because it's more faster to use swtich because IF go to ELSE and do another IF and it is a wast of memory for load that, use CASE.

[phaedrusGhost]

Hi Poit,

I like the idea. Made a couple of changes to your syntax due to some minor errors but I am able to display the states now however I am unable to catch the cities.

here is what I have:

switch($data){
    case "states": // $data == "states"
        echo "<select name='state' onChange=\"dochange('cities', this.value)\">\n";
        echo "<option value='0'>==== Choose State ====</option>\n";
        $sql = "SELECT id, state FROM users ORDER BY state";
		$result = $db->query($sql);
        while ($row = $db->fetch($result)) {
			$state = $row['state'];
            echo "<option value=\"". $state . "\">" . $state . "</option> \n";
        }
    break;
    case "cities": // $data == "cities"
        echo "<select name='cities'>\n";
        echo "<option value='0'>==== Choose City ====</option>\n";
        $sql = "SELECT id, city FROM users WHERE id = '$val' ORDER BY city ";
		$result = $db->query($sql);
        while ($row = $db->fetch($result)) {
			$state = $row['state'];
            echo "<option value=\"". $state . "\">" . $state . "</option> \n";
        }
    break;
}
echo "</select>\n";

Vaibhav: I am still very interested in your AJAX script. Please send at your earliest convenience.

Thanks all...

[P0lT10n]

But in the last code you edited you wrote:

while ($row = $db->fetch($result)) {
     $state = $row['state'];
     echo "<option value=\"". $state . "\">" . $state . "</option> \n";
}

And it's:

while ($row = $db->fetch($result)) {
     $state = $row['city']; // Here the change, you wrote state and is city, isn't it ?
     echo "<option value=\"". $state . "\">" . $state . "</option> \n";
}

Thanks for the change, i forget it :P

[phaedrusGhost]

Totally correct Poit, thanks... And I didn't make this clear before but I am using an AJAX script with this and perhaps this is where I am getting my problem from. I thought by taking care of the PHP script it would handle all but it doesn't seem so.

I was interested to see Vaibhav's solution and compare it with mine. Here is everything I have now.

Again, my apologies for not sharing the entire script before....

This is on the root page:

<?     
     echo "<form name=sel>\n";
     echo "States : <font id=states><select>\n";
     echo "<option value='0'>============</option> \n" ;
     echo "</select></font>\n";
     
     echo "Cities : <font id=cities><select>\n";
     echo "<option value='0'>=== none ===</option> \n" ;
     echo "</select></font>\n";
?>

<script language=Javascript>
function Inint_AJAX() {
   try { return new ActiveXObject("Msxml2.XMLHTTP");  } catch(e) {} //IE
   try { return new ActiveXObject("Microsoft.XMLHTTP"); } catch(e) {} //IE
   try { return new XMLHttpRequest();          } catch(e) {} //Native Javascript
   alert("XMLHttpRequest not supported");
   return null;
};

function dochange(src, val) {
     var req = Inint_AJAX();
     req.onreadystatechange = function () { 
          if (req.readyState==4) {
               if (req.status==200) {
                    document.getElementById(src).innerHTML=req.responseText; //retuen value
               } 
          }
     };
     req.open("GET", "state.php?data="+src+"&val="+val); //make connection
     req.setRequestHeader("Content-Type", "application/x-www-form-urlencoded;charset=iso-8859-1"); // set Header
     req.send(null); //send value
}

window.onLoad=dochange('states', -1);         // value in first dropdown
</script>

And this is the rest:

<?
    
	 
       //set database
define("_VALID_PHP", true);
  require_once("../include/config.php"); 
  
  
  if (isset($_POST['login']))
      : $result = $session->login($_POST['username'], $_POST['password'], isset($_POST['remember']));
  // Login successful 
  if ($result && $session->userlevel == '2')
      : redirect("useredit.php");
    elseif ($result && $session->userlevel == '1')
      : redirect("agentedit.php");
    elseif ($result && $session->userlevel == '9')
      : redirect("admin/index.php");
	  else : redirect("index.php");
  
  endif;
  endif;


     
     
     $data=$_GET['data'];
     $val=$_GET['val'];
     
     switch($data){
    case "states": // $data == "states"
        echo "<select name='state' onChange=\"dochange('cities', this.value)\">\n";
        echo "<option value='0'>==== Choose State ====</option>\n";
        $sql = "SELECT id, state FROM users ORDER BY state";
		$result = $db->query($sql);
        while ($row = $db->fetch($result)) {
			$state = $row['state'];
            echo "<option value=\"". $state . "\">" . $state . "</option> \n";
        }
    break;
    case "cities": // $data == "cities"
        echo "<select name='cities'>\n";
        echo "<option value='0'>==== Choose City ====</option>\n";
        $sql = "SELECT id, city FROM users WHERE id = '$val' ORDER BY city ";
		$result = $db->query($sql);
        while ($row = $db->fetch($result)) {
			$city = $row['city'];
            echo "<option value=\"". $city . "\">" . $city . "</option> \n";
        }
    break;
}
echo "</select>\n";
?>

the help is greatly appreciated...

[P0lT10n]

You are Welcome ! You are right on using AJAX, but I didn't recomend it to you because I dont know AJAX, but the main theme was solve your problem. I solved it :D but if you know AJAX you MUST ALWAYS use AJAX for this ! :) Not tick solved :D