PHP/MySQL editing a form using drop-down boxes

[manc1976]

I have created a form that uses a drop-down box to save information to a MySQL database using a php script I wrote.

That works perfectly, but the problem I have is when I am trying to edit the information in the database. I have created an edit form that gets the values from the database and displays the fields.

The issue I am having is how to populate the drop-down box on the edit form with the current value from the database, but also show all the other options available, so that any of them can be selected and updated once saved.

I have 2 tables where the information needs to come from.

CREATE TABLE IF NOT EXISTS 'user' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'username' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  'forename' tinytext COLLATE utf8_unicode_ci,
  'surname' tinytext COLLATE utf8_unicode_ci,
  'address1' tinytext COLLATE utf8_unicode_ci,
  'address2' tinytext COLLATE utf8_unicode_ci,
  'post_code' tinytext COLLATE utf8_unicode_ci,
  'access_id' int(11) NOT NULL, 
  PRIMARY KEY ('id'),
  KEY 'access_id' ('access_id')
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;


CREATE TABLE IF NOT EXISTS 'access' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'access' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

The drop down box is only for the 'access' field in the 'access' table. The 'access_id' is stored in the main table 'user'.

The options in the 'access' table could increase so I do not want to hard code the options into the form, but instead use the options available in the table.

How do I go about doing this?

Thanks in advance

[simplypixie]

A quick and dirty solution to show you how it can be done:

<?php
$sql = mysql_query("SELECT access_id FROM user WHERE id='".$userid."' LIMIT 1");
$result = mysql_fetch_array($sql);
$id = $result['access_id'];

$query = mysql_query("SELECT * FROM access");
?>
<select name='access'>
  <?php
    while ($accessrow = mysql_fetch_array($query)) {
  ?>
<option value="<?php echo $accessrow['id']; ?>" <?php if ($id == $accessrow['id']) { echo 'selected'; } ?> ><?php echo $accessrow['access']; ?></option>
<?php } ?>

[manc1976]

Hi

It worked great but noticed that it created another text box next to it, and then realised it is because select had not been closed.

Many Thanks for your help, it had been bugging me for the whole of yesterday how to do it :)

[simplypixie]

No problem and sorry I forgot to close the select tag (knew I had missed something but couldn't think what it was).

[manc1976]

Ok, another question

I used the above code and it worked fine, but when I use it again for another field, or even the same field again (as a test), It doesn't show the selected, only the list of fields. Why would this be?

[simplypixie]

Can you post your form code so I can understand what you are trying to do please

[manc1976]

CREATE TABLE IF NOT EXISTS 'details' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'user_id' int(11) NOT NULL,
  'office_id' int(11) NOT NULL,
  'dept_id' int(11) NOT NULL,
  'job_id' int(11) NOT NULL,
  PRIMARY KEY ('id'),
  KEY 'user_id' ('user_id')
  KEY 'office_id' ('office_id'),
  KEY 'dept_id' ('dept_id'),
  KEY 'job_id' ('job_id'),
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;


CREATE TABLE IF NOT EXISTS 'office' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'office' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY ('id'),
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=42 ;
  
CREATE TABLE IF NOT EXISTS 'department' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'department' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY ('id'),
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;  

CREATE TABLE IF NOT EXISTS 'jobs' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'job_title' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY ('id'),
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=31 ;

<h3>Access Level:</h3>
<p>
<?php
$sql = mysql_query("SELECT access_id FROM user WHERE id='".$id."' LIMIT 1");
$result = mysql_fetch_array($sql);
$id = $result['access_id'];

$query = mysql_query("SELECT * FROM access");
?>
<select name='access'>
  <?php
    while ($accessrow = mysql_fetch_array($query)) {
  ?>
<option value="<?php echo $accessrow['id']; ?>" <?php if ($id == $accessrow['id']) { echo 'selected'; } ?> ><?php echo $accessrow['access']; ?></option>
<?php } ?> </select></p>


<p>&nbsp;</p>

<h3>Office:</h3>
<p>
<?php
$sql = mysql_query("SELECT office_id FROM details WHERE user_id='".$id."' LIMIT 1");
$result = mysql_fetch_array($sql);
$id = $result['office_id'];

$query = mysql_query("SELECT * FROM office");
?>
<select name='office'>
  <?php
    while ($officerow = mysql_fetch_array($query)) {
  ?>
<option value="<?php echo $officerow['id']; ?>" <?php if ($id == $officerow['id']) { echo 'selected'; } ?> ><?php echo $officerow['office']; ?></option>
<?php } ?> </select></p>


<p>&nbsp;</p>

<h3>Department:</h3>
<p>
<?php
$sql = mysql_query("SELECT dept_id FROM details WHERE user_id='".$id."' LIMIT 1");
$result = mysql_fetch_array($sql);
$id = $result['dept_id'];

$query = mysql_query("SELECT * FROM department");
?>
<select name='dept'>
  <?php
    while ($deptrow = mysql_fetch_array($query)) {
  ?>
<option value="<?php echo $deptrow['id']; ?>" <?php if ($id == $deptrow['id']) { echo 'selected'; } ?> ><?php echo $deptrow['department']; ?></option>
<?php } ?> </select></p>


<p>&nbsp;</p>

<h3>Job Title:</h3>
<p>
<?php
$sql = mysql_query("SELECT job_id FROM details WHERE user_id='".$id."' LIMIT 1");
$result = mysql_fetch_array($sql);
$id = $result['job_id'];

$query = mysql_query("SELECT * FROM jobs");
?>
<select name='jobs'>
  <?php
    while ($jobsrow = mysql_fetch_array($query)) {
  ?>
<option value="<?php echo $jobsrow['id']; ?>" <?php if ($id == $jobsrow['id']) { echo 'selected'; } ?> ><?php echo $jobsrow['job_title']; ?></option>
<?php } ?> </select></p>

Thanks in advance

[simplypixie]

I would try naming your variables differently, for example in the second query use $osql, $oresult, $oquery, $oid, and see if that helps.

[manc1976]

Hi

I have re-written the edit page and it works, there must have been something in my code that was stopping it working properly.

Thanks again for your help :)