PHP/MYSQL: form <select> question

[eMonk]

How can you fetch data into select form fields and have the data stored in the database as selected?

<select name="favourite_fruit">
<option>Apples</option>
<option>Bananas</option>
<option>Oranges</option>
</select>

I was thinking something like:

<select name="favourite_fruit">
<option selected><?= $fruit ?></option>
<option>Bananas</option>
<option>Oranges</option>
</select>

But don't think the unselected fruits would show up in the select menu...

[k0nr4d]

<select name="favourite_fruit">
<option <? if($row[favorite_fruit] == 'Apples') { echo "selected"; }>Apples</option>
<option <? if($row[favorite_fruit] == 'Bananas') { echo "selected"; }>Bananas</option>
<option <? if($row[favorite_fruit] == 'Oranges') { echo "selected"; }>Oranges</option>
</select>

[FlexxAeon]

Originally Posted by k0nr4d

<select name="favourite_fruit">
<option <? if($row[favorite_fruit] == 'Apples') { echo "selected"; }>Apples</option>
<option <? if($row[favorite_fruit] == 'Bananas') { echo "selected"; }>Bananas</option>
<option <? if($row[favorite_fruit] == 'Oranges') { echo "selected"; }>Oranges</option>
</select>

except you didn't close the php with ?>

[eMonk]

That makes sense. Thanks again k0nr4d. I'll try this out shortly.

Note: I'll Paypal you some funds if I keep having to come to GFY for programming advice/code lol. Thanks again bro!

[nation-x]

I like ternaries

<select name="favourite_fruit">
<option<?php echo ($row[favorite_fruit] == 'Apples')?' selected':''; ?>>Apples</option>
<option<?php echo ($row[favorite_fruit] == 'Bananas')?' selected':''; ?>>Bananas</option>
<option<?php echo ($row[favorite_fruit] == 'Oranges')?' selected':''; ?>>Oranges</option>
</select>

[k0nr4d]

Originally Posted by FlexxAeon

except you didn't close the php with ?>

ahhh yeah sorry, stupid gfy doesn't have syntax coloring in the textbox

[SpotOnTechSupport]

Gotta make sure the processing code can pull the array as well.

[FlexxAeon]

Originally Posted by k0nr4d

ahhh yeah sorry, stupid gfy doesn't have syntax coloring in the textbox

yeah i'm pretty reliant on coloring as well....but i been breathing/sleeping/eating/shitting PHP the last couple of weeks so i'm in 'the zone'

[eMonk]

Thanks again boys... the code worked... y'all are better then google...

BTW what do you guys use to code in? Was thinking of using Notepad++

[FlexxAeon]

Originally Posted by eMonk

...BTW what do you guys use to code in? Was thinking of using Notepad++

dreamweaver...

[eMonk]

LOL, me as well but was going to look into other editors for programming starting with Notepad++ as it's free to use and to see if they make a difference in reading/writing code. As you can see though I'm still a noob and these tools probably won't benefit me for awhile.

[HomerSimpson]

I use EmEditor, but Notepad++ is also very good..

[FlexxAeon]

Originally Posted by eMonk

LOL, me as well but was going to look into other editors for programming starting with Notepad++ as it's free to use and to see if they make a difference in reading/writing code. As you can see though I'm still a noob and these tools probably won't benefit me for awhile.

they'll help even as a noob. especially with things like coloring (which will help when you forget or overlook proper syntax) and function libraries

[Aric]

Originally Posted by FlexxAeon

they'll help even as a noob. especially with things like coloring (which will help when you forget or overlook proper syntax) and function libraries

As well as bracket matching

[k0nr4d]

i use dreamweaver, except only the code view. Just force of habit

[SpotOnTechSupport]

Zend Studio 8.

[Tempest]

Not to be a dick, but the proper way to do "selected" in an option is selected="selected". Same thing for checkboxes etc. Of course that's if you're doing your HTML to adhere to some standards.

[FlexxAeon]

"selected" by itself works fine...no less 'proper' than the other way

[nation-x]

I have found that dreamweaver and notepad++ suffer from the same problem. They add hidden characters that cause scripts to not run properly on some OS's. Character encoding is a serious issue when dealing with *nix OS flavors. With that in mind I either use geany (for quick edits) or NetBeans (7 beta) for projects.

I also noted an odd issue when I was using notepad++ and the explorer plugin... for some reason, on occasion, it would somehow corrupt the security descriptors of my windows partitions. It would lock up and the next thing you know I was having partition issues. I am still not sure what exactly the issue was but I stopped using it and haven't had any more issues with partition corruption.

[glowlite]

Originally Posted by Tempest

Not to be a dick, but the proper way to do "selected" in an option is selected="selected". Same thing for checkboxes etc. Of course that's if you're doing your HTML to adhere to some standards.

True, selected="selected" is always correct and a good coding habit, just using "selected" is sometimes correct.

There's a huge % of pages that don't even declare DOCTYPE though.

[SpotOnTechSupport]

With notepad++ if you go to Settings -> Preferences ->New Document/Default Directory you can fix that stupid BOM issue. Just check UTF-8 without BOM.

[eMonk]

How would you do the same but this time fetch data from a mysql table then display it in a select form field and have the value stored in the model_in_city table as selected?

Here's what I have so far but every city is being displayed in its own dropdown menu.

<?php

include("../includes/connect.php");

$query = "SELECT * FROM city";
if ($result = $db->query($query)) {

  while ($row = $result->fetch_assoc()) {
  
  $name = $row['city_name'];
  
  echo "<select name=\"select\">";
  echo "<option>$name</option>";
  echo "</select>";
}
$result->free();
}
$db->close();

?>

[sarettah]

<?php
include("../includes/connect.php");
$query = "SELECT * FROM city";
if ($result = $db->query($query)) 
{
  echo "<select name=\"select\">";
  while ($row = $result->fetch_assoc()) 
  {
    $name = $row['city_name'];
    echo "<option>$name</option>";
  }
  echo "</select>";
  $result->free();
}
$db->close();
?>

That fixes your dropdown issue.

[eMonk]

Thanks sarettah that worked.

I'm going to play around now with the code to see if I can get the selected value to work.

[sarettah]

Originally Posted by eMonk

Thanks sarettah that worked.

I'm going to play around now with the code to see if I can get the selected value to work.

What does your model_in_city table look like? (if you don't mind me asking of course) ;p

[eMonk]

create table model_in_city
( model_id int unsigned not null,
  city_id int unsigned not null,
  city_display tinyint(2) unsigned,

  primary key (model_id, city_display)

);

city_id = the id of the city. They are displayed in this table by number.. eg 44 = Toronto, etc
city_display = their city choice.. models can choose up to 4 cities to be displayed in. In this example i'm trying to get city_display = 1 to work first.

Here's what I have now but don't know to to incorporate the selected value into the form:

<?php
include("../includes/connect.php");
$id = 17;
$query = "SELECT city.*, model_in_city.* FROM city, model_in_city WHERE model_in_city.model_id = $id AND model_in_city.city_display = 1";
if ($result = $db->query($query)) 
{
  echo "<select name=\"select\">";
  while ($row = $result->fetch_assoc()) 
  {
    $name = $row['city_name'];
	$city_id = $row['city_id'];
    echo "<option>$name</option>";
  }
  echo "</select>";
  echo "$city_id"; // just making sure the correct value is being fetched.
  $result->free();
}
$db->close();
?>

[sarettah]

Well, first you probably want your dropdown to reflect the city id when you are pulling in your vars.

Like this:

 echo "<option value=" . $city_id . ">$name</option>";

Now, I am not sure what you are trying to do but you said " incorporate the selected value into the form:". Which selected value? The city that was already selected somewhere? You are losing me there, maybe just too late in the night for me..lol.

If you have a selected city coming in then what you would do is compare the city_id in the row you are looking at with the city_id coming in and mark it as selected when you find it:

while ($row = $result->fetch_assoc()) 
  {
       $name = $row['city_name'];
       $city_id = $row['city_id'];
       echo "<option value=" . $city_id 
       if($row['city_id']==$idin)
       {
           echo " selected ";
       }
       echo ">$name</option>";
  }

Something like that. (edited in: that assumes that you already pulled the city_id coming in into $idin)

[V_RocKs]

This is the form field and it will allow for more than one selection:

          <select name="blogs[]"  size="55" multiple="multiple" style="font-size:10px;"><? displayBlogs(); ?></select>

This is the function that created the list in the form field:

function displayBlogs() {
	global $getquery,$optionrows,$blogs;
	//$count = mysql_num_rows($getquery);
	while ($optionrows = mysql_fetch_assoc($getquery))
	{
	$select = '';
	for ($x=0;$x<count($blogs);$x++)
		{ 
		if ($blogs[$x] == $optionrows["id"]) { $select = ' selected '; }
		}
	echo "<option value=\"".$optionrows["id"]."\"$select>".$optionrows["name"]."</option>";
	
	}

}

This captures the field:

$blogs = $_POST['blogs'];

This forms the statement:

for ($x = 0;$x<count($blogs);$x++) 
		{ 
		if ($x<count($blogs)-1) { $ids .= "id = $blogs[$x] or "; }
		else { $ids .= "id = $blogs[$x]"; }
		}

This selects them from the DB:

$grabblog = mysql_query("select * from blogs where ($ids) order by name;",$wp) or die (mysql_error());

Now you can do with them what you want:

while ($rowsgrab = mysql_fetch_assoc($grabblog))

[eMonk]

Thanks, I'll play with this more in about an hour. Question.. I have 2 columns in both tables that I'm quering the same. Can I use:

$city_id = $row['city.city_id'];
$model_in_city_id = $row['model_in_city.city_id'];

So mysql knows which one I'm referring to?

[eMonk]

Ok, I had to use the as clause in the query to separate the 2 different column names.

Here's what the code looks like now:

<?php
include("../includes/connect.php");
$id = 17;
$query = "SELECT city.city_id AS city_id_1, city.city_name, model_in_city.model_id, model_in_city.city_id AS city_id_2, model_in_city.city_display
          FROM city, model_in_city
	  WHERE model_in_city.model_id = $id
          AND model_in_city.city_display = 1";
if ($result = $db->query($query)) 
{
  echo "<select name=\"select\">";
  while ($row = $result->fetch_assoc()) 
  {
    $name = $row['city_name'];
	$city_id_1 = $row['city_id_1'];
	$city_id_2 = $row['city_id_2'];
    echo "<option value=\"$city_id_1\">$name</option>";
	/* if($row['city_id'] == $city_id)
       {
           echo "<option selected>$name</option>";
       } */
  }
  echo "</select>";
  echo "$city_id_2"; // just checking to make sure this value is correct.
  $result->free();
}
$db->close();
?>

Now, how can I echo the selected value if the option value = $city_id_2

I think I may have to perform another query since the cities are entered in by city id number in the model_in_city table to fetch the name of the city instead to display as selected.

Note: I commented out the if statement in the code above.

[sarettah]

What I was saying in icq:

<?php

include("../includes/connect.php");


$query="select city_id, city_name from city order by city_name";
if ($city_result = $db->query($query)) 
{
  $id = 17;
  $query = "model_id, city_id from model_in_city 
        	  WHERE model_id = $id
            AND city_display = 1";
  if ($result = $db->query($query)) 
  {
    echo "<select name=select multiple>";
    while ($cityrow = $city_result->fetch_assoc()) 
    {
      echo "<option value=" . $cityrow['city_id'] 
      mysql_data_seek($result,0);
      while($model_city=mysql_fetch_array($result))
      {
        if($model_city['city_id']==$cityrow['city_id'])
        {
          echo " selected ";
        }
      }
      echo ">" . $cityrow['city_name'] . "</option>";
  }
  echo "</select>";
  $result->free();
}
$db->close();
?>

[sarettah]

I am missing a close tag on one of the ifs in there, sorry about that.

<?php

include("../includes/connect.php");
$query="select city_id, city_name from city order by city_name";
if ($city_result = $db->query($query)) 
{
  $id = 17;
  $query = "model_id, city_id from model_in_city 
        	  WHERE model_id = $id
            AND city_display = 1";
  if ($result = $db->query($query)) 
  {
    echo "<select name=select multiple>";
    while ($cityrow = $city_result->fetch_assoc()) 
    {
      echo "<option value=" . $cityrow['city_id'] 
      mysql_data_seek($result,0);
      while($model_city=mysql_fetch_array($result))
      {
        if($model_city['city_id']==$cityrow['city_id'])
        {
          echo " selected ";
        }
      }
      echo ">" . $cityrow['city_name'] . "</option>";
    }
    echo "</select>";
    $result->free();
  }
  $cityresult->free();
}
$db->close();
?>

[sarettah]

Ok, like I said in icq.

A test page is at http://www.madspiders.com/selecttest.php

The code I have for it (using mysql, not mysqli because I do not think I have mysqli on the server) is:

<?php
//error_reporting(0);
$dbhost="XXXXXXX";
$dbuser="XXXXXXX";
$dbpass="XXXXXXX";
$db = mysql_connect($dbhost, $dbuser, $dbpass);
$dbname="select_test";
$selected=mysql_select_db($dbname);

$query="select city_id, city_name from city order by city_name";
$city_result=mysql_query($query,$db);
if ($city_result) 
{
  $id = 1;
  $query = "select model_id, city_id from model_in_city 
        	  WHERE model_id = $id
            AND city_display = 1";
  $modelresult=mysql_query($query, $db);
  if ($modelresult) 
  {
    echo "<select name=select multiple size=8>";
    while ($cityrow = mysql_fetch_array($city_result)) 
    {
      echo "<option value=" . $cityrow['city_id']; 
      mysql_data_seek($modelresult,0);
      while($model_city=mysql_fetch_array($modelresult))
      {
       
        if($model_city['city_id']==$cityrow['city_id'])
        {
          echo " selected ";
        }
      }
      echo ">" . $cityrow['city_name'] . "</option>";
    }
    echo "</select>";
  }
  else
  {
    echo "no model result mysql error=" . mysql_error() . "<br>\n";
  }

}
else
{
  echo "no city result mysql error=" . mysql_error() . "<br>\n";
}
?>

[sarettah]

Home now,

as I said in icq, that version was so you could see what was happening all the way through.

To do it from one query like you were trying to do would look like this: http://www.madspiders.com/selecttest1.php

<?php
//error_reporting(0);
$dbhost = "XXXXX";
$dbuser = "XXXXX";
$dbpass = "XXXXX";
$db = mysql_connect($dbhost, $dbuser, $dbpass);
$dbname="select_test";
$selected=mysql_select_db($dbname);

// for testing
$id = 1;

$query  ="select a.city_id, a.city_name, b.city_display from city a ";
$query .="left outer join model_in_city b on a.city_id=b.city_id and b.city_display>0 and b.model_id=" . $id . " ";
$query .="order by a.city_name";

$city_result=mysql_query($query,$db);
if ($city_result) 
{
    echo "<select name=select multiple size=8>";
    while ($cityrow = mysql_fetch_array($city_result)) 
    {
      echo "<option value=" . $cityrow['city_id']; 
      if($cityrow['city_display']==1)
      {
          echo " selected ";
      }
      echo ">" . $cityrow['city_name'] . "</option>";
    }
    echo "</select>";
}
else
{
  echo "no city result mysql error=" . mysql_error() . "<br>\n";
}
?>

You said on yours that it was not marking the options as selected. When you get back, post up the actual code you have so we can take a look.

.

[sarettah]

Lol, I just can't keep this out of my head dammit.

Okay, when we talked about it, you had said something about using 4 different dropdowns, one for each selected city and that you had the cities marked as 1, 2, 3, 4 in the city_display field.

I went in and created a new model_in_city table and named it model_in_city_mod and emulated the way you had that set up.

So you could do your 4 separate dropdowns kinda like this: http://www.madspiders.com/selecttest2.php

<?php
//error_reporting(0);
$dbhost = "XXXXX";
$dbuser = "XXXXX";
$dbpass = "XXXXX";
$db = mysql_connect($dbhost, $dbuser, $dbpass);
$dbname="select_test";
$selected=mysql_select_db($dbname);

$sel=array();

$id = 1;
$query="select a.city_id, a.city_name, b.city_display from city a ";
$query  .="left outer join model_in_city_mod b on a.city_id=b.city_id and b.city_display>0 and b.model_id=" . $id . " ";
$query .="order by a.city_name";

$city_result=mysql_query($query,$db);
if ($city_result) 
{
    for($i=1;$i<=4;$i++)
    {
      $sel[$i]="<select name=select[" . $i . "] size=8>\n";
    }
    while ($cityrow = mysql_fetch_array($city_result)) 
    {
      for($i=1;$i<=4;$i++)
      {
        $sel[$i].="<option value=" . $cityrow['city_id']; 
        if($cityrow['city_display']==$i)
        {
          $sel[$i] .=" selected ";
        }
        $sel[$i].=">" . $cityrow['city_name'] . "</option>\n";
      }
    }
    for($i=1;$i<=4;$i++)
    {
      $sel[$i].="</select>";
    }
}
else
{
  echo "no city result mysql error=" . mysql_error() . "<br>\n";
}
for($i=1;$i<=4;$i++)
{
  echo $sel[$i] . "<br>\n<br>\n";
}
?>

[eMonk]

Thanks man that worked!

Now have to add this code to a form with other queries and get it working there. Will try it in about an hour and report back if I have problems. Thanks again sarettah.