Jump to content

Dynamic update option list from mysql query


Gazz1982

Recommended Posts

Hi all, I was hoping you could point me in the right direction, I have 2 questions.

 

1. I want to have an option drop down which uses the array of a returned sql query. I have the following code which uses onchange to submit the selection, how do I use the mysql_fetch_array($result1) to give a list of the two selected columns in the $Site=array() bit.

 

2. The selected result will be used in more SQL select statements, so once a site from the list is selected the relevant data from other tables will be displayed using e.g. select person from people [some inner join statement] where site="site 1"; Therefore the $_POST value must be available to be passed to other queries once selected.

 

Thanks for any help, I still feel like a noob, but I'm getting there.

 

Gary

<?php include 'header.php'; ?> 
<div class='container'> 
<?php include 'menu.php'; ?> 
<?php include 'connect.php'; ?>
<?php $sql1="SELECT Sites.Site_ID, Sites.Site_name_1 FROM `Sites`"; $result1=mysql_query($sql1); ?>

<?php
  function get_options()
  {
    $site=array('Site 1'=>'Site 1', 'Site 2'=>'Site 2', 'Site 3'=>'Site 3');
    $options='';
    while(list($k,$v)=each($site))
    {
      $options.='<option value="'.$v.'">'.$k.'</option>';
    }
     return $options;
  }
   if(isset($_POST['site']))
    {
    echo $_POST['site'];
  }
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<select name="site" onchange="this.form.submit();">
<?php echo get_options(); ?>
</select>
Link to comment
Share on other sites

use a function which takes an array as its argument. You can then use the same function for sites, people etc by just passing it a different array.

<?php
$sql1="SELECT Site_ID, Site_name_1 FROM `Sites`"; 
$result1=mysql_query($sql1); 
$site_array = array();
while (list($id, $name) = mysql_fetch_row($result1)) {
    $site_array[$id] = $name;
}

function get_options($arr, $current=null) 
{
    $opts = '';
    foreach ($arr as $k => $v) {
        $sel = $k==$current ? 'selected="selected"' : '';
        $opts .= "<option value='k' $sel>$v</option>\n";
    }
    return $opts;
}
?>
<html>
<body>
    <select name='site'>
        <?php echo get_options($site_array);?>
    </select>
</body>
</html>
Link to comment
Share on other sites

Thank you, that gets me half way there.

 

Now I have added in the if(isset($_POST['site_array'])) to post the result onchange but it is not echoing the site_array. I only want the selected value from the array so I can use it in another sql query (see question part 2)

I would also like it to display the Site_ID as well as the Site_name_1 in the drop down (this is less important)

 

The changed code:

<?php
$sql1="SELECT Site_ID, Site_name_1 FROM `Sites`"; 
$result1=mysql_query($sql1); 
$site_array = array();

while (list($id, $name) = mysql_fetch_row($result1)) {$site_array[$id] = $name;}

function get_options($arr, $current=null) 
{
    $opts = '';
    foreach ($arr as $k => $v) {
        $sel = $k==$current ? 'selected="selected"' : '';
        $opts .= "<option value='k' $sel>$v</option>\n";
    }
    return $opts;
}

   if(isset($_POST['site_array']))
  {
    echo $_POST['site_array'];
  }

?>

<html>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
    <select name="site" onchange="this.form.submit">
        <?php echo get_options($site_array);?>
    </select>
</form>

</body>
</html>
Link to comment
Share on other sites

Thanks, I spotted that error ($_POST['site'] after posting the reply.

 

However it does not require a submit button, the onchange function is doing the submit once an option is selected from the list: onchange='this.form.submit'

This was working before I altered the code, now I'm scratching my head...

Link to comment
Share on other sites

Ok, so I had an error in my onchange statement, now it kind of works but the returned value is 'k' and not the values which should be returned, heres the updated code:

<?php
$sql1="SELECT Site_ID, Site_name_1 FROM `Sites` ORDER BY Sites.Site_name_1 ASC"; 
$result1=mysql_query($sql1); 
$site_array = array();

while (list($id, $name) = mysql_fetch_row($result1)) {$site_array[$id] = $name;}

function get_options($arr, $current=null) 
{
    $opts = '';
    foreach ($arr as $k => $v) {
        $sel = $k==$current ? 'selected="selected"' : '';
        $opts .= "<option value='k' $sel>$v</option>\n";
    }
    return $opts;
}

   if(isset($_POST['site']))
  {
    echo $_POST['site'];
  }

?>

<html>
<body>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
  <select name="site" onchange="this.form.submit();">
    <?php echo get_options($site_array);?>
  </select>
</form>

</body>
</html>
Link to comment
Share on other sites

Thanks, now it works better, although I'm still not getting the Site_ID from the while (list($id, $name) = mysql_fetch_row($result1)) {$site_array[$id] = $name;}

 

I would like this:

 

1 sitename

2 sitename

3 sitename

4 sitename
 

where the number is the site_ID, so two columns in the option dropdown

Edited by Gazz1982
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.