Member Avatar for 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

  • 2 Contributors
  • 8 Replies
  • 10K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by manc1976
Member Avatar for 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 } ?>
commented: Was the soulution I was looking for +0
Member Avatar for 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 :)

Member Avatar for simplypixie

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

Member Avatar for 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?

Member Avatar for simplypixie

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

Member Avatar for 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

Member Avatar for simplypixie

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

Member Avatar for 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 :)

Edited by manc1976 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.