Gazz1982 Posted October 7, 2015 Share Posted October 7, 2015 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> Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/ Share on other sites More sharing options...
Barand Posted October 7, 2015 Share Posted October 7, 2015 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> Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522550 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 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> Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522554 Share on other sites More sharing options...
Barand Posted October 7, 2015 Share Posted October 7, 2015 Your form needs a submit button. Your select has name='site' therefore the value will be in $_POST['site'] Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522556 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 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... Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522559 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 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> Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522564 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 ok the problem lies in this line, it is returning the k as set in this value: $opts .= "<option value='k' $sel>$v</option>\n"; Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522567 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 (edited) And solved, remove the value='k' and it works Although still it only gives the Sites.site_name_1 and not the Sites.site_id Edited October 7, 2015 by Gazz1982 Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522568 Share on other sites More sharing options...
Ch0cu3r Posted October 7, 2015 Share Posted October 7, 2015 value='k' should of been value='$k' Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522570 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 (edited) 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 October 7, 2015 by Gazz1982 Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522573 Share on other sites More sharing options...
Gazz1982 Posted October 7, 2015 Author Share Posted October 7, 2015 ok sometimes I'm as thick as a post, just needed to put the $k after the $sel> $opts .= "<option value='$k' $sel>$k $v</option>\n"; Quote Link to comment https://forums.phpfreaks.com/topic/298464-dynamic-update-option-list-from-mysql-query/#findComment-1522574 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.