(drop downs, Php and Mysql) with stored value in Mysql
I want to store the name of the city instead of the ID in my MySQL database. The PHP function for building the city list is something like this:
public function getCity() {
if(isset($this->attiva)) {
$base = mysql_connect ('localhost', 'bolmdb', 'bolmdb');
mysql_select_db ('bolmdb', $base);
$result = mysql_query("SELECT * FROM city WHERE idstate开发者_JS百科=$_POST[idcity]");
$city = '<option value="0">Select City...</option>';
while($row = mysql_fetch_array($result)) {
$city .= '<option value="' . $row['idcity'] . '">' . utf8_encode($row['nomecity']) . '</option>';
}
return $city;
}
}
and the form HTML:
</label>City:</label>
<select id="city" name="city" value="" ><br />
<option>Select City...</option>
</select>
So when the form is submitted, I get the value with $city = $_POST['city']
; and it's stored in MySQL as an ID. How can I store the name of the city in place of the ID?
[EDIT:] Hi! Thank you very much for your reply. Maybe I didn't explain me very well. My problem is that I need to keep $row['idcity'] in the option value to populate dynamically the drop down list with ajax. Obviously, It's necessary that I connect the State Mysql table with the City Mysql table by ID field to get data. STATE TABLE:
CREATE TABLE `state` (
`idstate` int(4) NOT NULL auto_increment,
`nomestate` varchar(50) NOT NULL default '',
PRIMARY KEY (`idregione`)
) TYPE=MyISAM AUTO_INCREMENT=161 ;
--
-- Dump dei dati per la tabella `state`
--
INSERT INTO `state` VALUES (1, 'Marche');
...
CITY TABLE:
CREATE TABLE `city` (
`idcity` int(4) NOT NULL auto_increment,
`nomecity` varchar(20) NOT NULL default '',
`idstate` int(4) NOT NULL default '0',
`siglacity` char(2) NOT NULL default '',
PRIMARY KEY (`idprovincia`)
) TYPE=MyISAM AUTO_INCREMENT=109 ;
--
-- Dump dei dati per la tabella `city`
--
INSERT INTO `city` VALUES (1, 'Ancona', 1, 'AN');
...
and two php funcions:
public function getState()
{
if(isset($this->attiva))
{
$base = mysql_connect ('localhost', 'bolmdb', 'bolmdb');
mysql_select_db ('bolmdb', $base);
$result = mysql_query("SELECT * FROM state");
$state = '<option value="0">Select State...</option>';
while($row = mysql_fetch_array($result))
{
$state .= '<option value="' . $row['idstate'] . '">' . utf8_encode($row['nomestate']) . '</option>';
}
return $state;
}
}
public function getCity()
{
if(isset($this->attiva))
{
$base = mysql_connect ('localhost', 'bolmdb', 'bolmdb');
mysql_select_db ('bolmdb', $base);
$result = mysql_query("SELECT * FROM city WHERE idstate=$_POST[idstate]");
$city = '<option value="0">Select City...</option>';
while($row = mysql_fetch_array($result))
{
$city .= '<option value="' . $row['idcity'] . '">' . utf8_encode($row['nomecity']) . '</option>';
}
return $city;
}
}
But in this way when I get the value with $city = $_POST['city'];
I store in the database the ID value. I thought to change IDSTATE with NOMESTATE in CITY table but I found that It was no better way to solve it and also It could create non-ASCII characters problems about which @Gerard remarks. Lastly I wonder as a matter of curiosity if there is a way to get output function (in the specific case . utf8_encode($row['nomecity']) .
) instead of the option value.
First, you need to modify the field id, change it to varchar. Replace id of city by his name in the database
I'm not sure and I would prefer to see your database table (not a database table field, as you have shown to Chumillas), but perhaps you should change this fragment
'<option value="' . $row['idcity'] . '">'
by this one
'<option value="' . utf8_encode($row['nomecity']) . '">'
inside your getCity() function.
This will probably make that, when the form is submitted and you get the value with
$city = $_POST['city'];
you get the name of the city instead of the ID.
EDIT: By the way, as the name of the city can have non-ASCII characters, I strongly recommend you to create another field to your table to store a representative name of the city but without them. For example, if you create a VARCHAR field named 'nickname', you should replace the mentioned line with this one:
'<option value="' . $row['nickname'] . '">'
精彩评论