Hello
I am a real newbie and it took me a while to get the script working but it does! Thank you so much, now I have a question and would like to know if it is possible to modify it.
When I have my 3 drop down selected it brings descr info.
I would like it to be displayed in a table but most important:
Is it possible that if you have your selection with your 3 MSL, it brings more than descr column but other columns?
like for me if they select:
Campus | Course | Category
it will return:
title
descr
start date
end date
start time
end time
location
I can see the potential but such a newbie that I will not be able to modify tyhe code.
THANK YOU
More columns for Description in MSL with Ajax
-
petitesouris
- Posts:4
More columns for Description in MSL with Ajax
Admin
Posts:805
Hello
If you want to select data from more columns for description in Multiple Select Dropdown Lists, and display it into a html table, use the script from this page:
https://coursesweb.net/ajax/multiple-sel ... ist-ajax_t
- and replace the content in the "select_list.php" with this code:
- In the $extra_desc array add the other columns that you want with data for description.
- If you want to modify the html table with description, edit the code from the lines; 68-70.
If you want to select data from more columns for description in Multiple Select Dropdown Lists, and display it into a html table, use the script from this page:
https://coursesweb.net/ajax/multiple-sel ... ist-ajax_t
- and replace the content in the "select_list.php" with this code:
Code: Select all
<?php
// Multiple select lists - https://coursesweb.net/ajax/
// Here add your own data for connecting to MySQL database
$server = 'localhost';
$user = 'root';
$pass = 'password';
$dbase = 'dbname';
// Here add the name of the table and columns that will be used for select lists, in their order
// Add null for 'links' if you don`t want to display their data too
$table = 'sites';
$ar_cols = array('site', 'menu', 'categ', 'links');
//Here you can add extra columns with data for description
$extra_desc = array('ext_col1', 'ext_col2');
if(!isset($_SESSION)) session_start();
header('Content-type: text/html; charset=utf-8');
$preid = 'slo_'; // a prefix used for element's ID, in which Ajax will add <select>
$col = $ar_cols[0]; // the variable used for the column that wil be selected
$re_html = ''; // will store the returned html code
// if there is data sent via POST, with index 'col' and 'wval'
if(isset($_POST['col']) && isset($_POST['wval'])) {
// set the $col that will be selected and the value for WHERE (delete tags and external spaces in $_POST)
$col = trim(strip_tags($_POST['col']));
$wval = trim(strip_tags($_POST['wval']));
}
$key = array_search($col, $ar_cols); // get the key associated with the value of $col in $ar_cols
$wcol = $key===0 ? $col : $ar_cols[$key-1]; // gets the column for the WHERE clause
// gets the next element in $ar_cols (needed in the onchange() function in <select> tag)
$last_key = count($ar_cols)-1;
$next_col = $key<$last_key ? $ar_cols[$key+1] : '';
$conn = new mysqli($server, $user, $pass, $dbase); // connect to the MySQL database
if (mysqli_connect_errno()) { exit('Connect failed: '. mysqli_connect_error()); } // check connection
$_SESSION['ar_cols'][$wcol] = isset($wval) ? $conn->real_escape_string($wval) : $wcol; // store in SESSION the column and its value for WHERE
// sets an array with data of the WHERE condition (column=value) for SELECT query
for($i=1; $i<=$key; $i++) {
$ar_where[] = '`'. $ar_cols[$i-1]. "`='". $_SESSION['ar_cols'][$ar_cols[$i-1]] ."'";
}
// define a string with the WHERE condition, and then the SELECT query
$where = isset($ar_where) ? ' WHERE '. implode($ar_where, ' AND ') : '';
$get_cols = $key<$last_key ? $col : $col .','. implode(',', $extra_desc);
$sql = "SELECT DISTINCT $get_cols FROM `$table`".$where;
$result = $conn->query($sql); // perform the query and store the result
// if the $result contains at least one row
if ($result->num_rows > 0) {
// sets the "onchange" event, which is added in <select> tag
$onchg = $next_col!==null ? " onchange=\"ajaxReq('$next_col', this.value);\"" : '';
// sets the select tag list (and the first <option>), if it's not the last column
if($col!=$ar_cols[$last_key]) $re_html = $col. ': <select name="'. $col. '"'. $onchg. '><option>- - -</option>';
while($row = $result->fetch_assoc()) {
// if its the last column, set a tablle with description, else, adds data in OPTION tags
if($col==$ar_cols[$last_key]){
$nrc = count($extra_desc);
$re_html .= '<table><tr><td>'. $row[$col] .'</td></tr>'; //start table with data from first column for description
for($i=0; $i<$nrc; $i++) $re_html .='<tr><td>'. $row[$extra_desc[$i]] .'</td></tr>'; //add data from $extra_desc columns
$re_html .='</table>'; //end table
}
else $re_html .= '<option value="'. $row[$col]. '">'. $row[$col]. '</option>';
}
if($col!=$ar_cols[$last_key]) $re_html .= '</select> '; // ends the Select list
}
else { $re_html = '0 results'; }
$conn->close();
// if the selected column, $col, is the first column in $ar_cols
if($col==$ar_cols[0]) {
// adds html code with SPAN (or DIV for last item) where Ajax will add the select dropdown lists
// with ID in each SPAN, according to the columns added in $ar_cols
for($i=1; $i<count($ar_cols); $i++) {
if($ar_cols[$i]===null) continue;
if($i==$last_key) $re_html .= '<div id="'. $preid.$ar_cols[$i]. '"> </div>';
else $re_html .= '<span id="'. $preid.$ar_cols[$i]. '"> </span>';
}
// adds the columns in JS (used in removeLists() to remove the next displayed lists when makes other selects)
$re_html .= '<script>var ar_cols = '.json_encode($ar_cols).'; var preid = "'. $preid. '";</script>';
}
else echo $re_html; - If you want to modify the html table with description, edit the code from the lines; 68-70.
petitesouris
Posts:4
Hello thank you so much I am such a newbie that it does not work.
Here is how I did the modification
select file
You told me to modify row 68 - 70 but with what ?
the test.php file is as it was
do I need to have my sql view in order of the data I want to display?
- Here is what is is giving me
frozzie.com/scu/multiple/test.php
As you can see the select drop down does work I can select but it brings zero results
I have downloaded the script ajax the test.php and selectlist.php and replace it with the codee provided and it is posted above.
- Here is my simple one and it works
frozzie.com/scu/multiple/scu/test.php
What I would like to achieve is:
Ihope it is clearer for you.
Thank you very much for your help and support.
Here is how I did the modification
select file
Code: Select all
<?php
// Multiple select lists - https://coursesweb.net/ajax/
// Here add your own data for connecting to MySQL database
$server = 'localhost';
$user = 'user';
$pass = '1234';
$dbase = 'frozzie_orientation';
// Here add the name of the table and columns that will be used for select lists, in their order
// Add null for 'links' if you don`t want to display their data too
$table = 'orientation_schedule';
$ar_cols = array('campus', 'course', 'category', 'title'); Title is to be my first description
//Here you can add extra columns with data for description
$extra_desc = array('descr', 'start date', 'end date', 'start time', 'end time', 'location', 'student type');
if(!isset($_SESSION)) session_start();
header('Content-type: text/html; charset=utf-8');
// The rest of code posted before the test.php file is as it was
do I need to have my sql view in order of the data I want to display?
- Here is what is is giving me
frozzie.com/scu/multiple/test.php
As you can see the select drop down does work I can select but it brings zero results
I have downloaded the script ajax the test.php and selectlist.php and replace it with the codee provided and it is posted above.
- Here is my simple one and it works
frozzie.com/scu/multiple/scu/test.php
What I would like to achieve is:
Code: Select all
Select: campus: LISMORE | course: BA | category: ESSENTIAL SELECTED
return
Table
Title Descr Start time
Meet your School Hosts welcome to uni 9am
Senior Deputy Vice Chancellor Welcome VC speach 9am
University Essentials learn system 10 am
Gnibi College of Indigenous Australian Peoples- Course Information Session lern about ur course 11 amThank you very much for your help and support.
Admin
Posts:805
Hello
A problem is that you have space character in the column name, it is better for your scripts to use mysql tables and columns names without spaces, replacing space-character with "_", so: "start_date", "end_date", etc..
But, if for some reasons you have to use mysql column names with space, in the code add the name between this character: "`...`", like this: "`start date`", "`end date`".
I fixed also something in the script.
You not need to modify the rows 68-70, i mentioned it in case you want to modify the code of the html table with description, for example to add a css id or class.
I modified the script to can work with table /column names with space.
Here is the code for "select_list.php":
In this archive there is the working Multiple Select Dropdown Lists script with a sql file for test.
A problem is that you have space character in the column name, it is better for your scripts to use mysql tables and columns names without spaces, replacing space-character with "_", so: "start_date", "end_date", etc..
But, if for some reasons you have to use mysql column names with space, in the code add the name between this character: "`...`", like this: "`start date`", "`end date`".
I fixed also something in the script.
You not need to modify the rows 68-70, i mentioned it in case you want to modify the code of the html table with description, for example to add a css id or class.
I modified the script to can work with table /column names with space.
Here is the code for "select_list.php":
Code: Select all
<?php
// Multiple select lists - https://coursesweb.net/ajax/
// Here add your own data for connecting to MySQL database
$server = 'localhost';
$user = 'root';
$pass = 'password';
$dbase = 'dbname';
// Here add the name of the table and columns that will be used for select lists, in their order
// Add null for 'links' if you don`t want to display their data too
$table = 'sites';
$ar_cols = array('site', 'menu', 'categ', 'links');
//Here you can add extra columns with data for description
//Let it empty ( array(() ) if you not use additional column for description
$extra_desc = array('descr', 'start_date', 'end date');
if(!isset($_SESSION)) session_start();
header('Content-type: text/html; charset=utf-8');
$preid = 'slo_'; // a prefix used for element's ID, in which Ajax will add <select>
$col = $ar_cols[0]; // the variable used for the column that wil be selected
$nrc = count($extra_desc);
$re_html = ''; // will store the returned html code
// if there is data sent via POST, with index 'col' and 'wval'
if(isset($_POST['col']) && isset($_POST['wval'])) {
// set the $col that will be selected and the value for WHERE (delete tags and external spaces in $_POST)
$col = trim(strip_tags($_POST['col']));
$wval = trim(strip_tags($_POST['wval']));
}
$key = array_search($col, $ar_cols); // get the key associated with the value of $col in $ar_cols
$wcol = $key===0 ? $col : $ar_cols[$key-1]; // gets the column for the WHERE clause
// gets the next element in $ar_cols (needed in the onchange() function in <select> tag)
$last_key = count($ar_cols)-1;
$next_col = $key<$last_key ? $ar_cols[$key+1] : '';
$conn = new mysqli($server, $user, $pass, $dbase); // connect to the MySQL database
if (mysqli_connect_errno()) { exit('Connect failed: '. mysqli_connect_error()); } // check connection
$_SESSION['ar_cols'][$wcol] = isset($wval) ? $conn->real_escape_string($wval) : $wcol; // store in SESSION the column and its value for WHERE
// sets an array with data of the WHERE condition (column=value) for SELECT query
for($i=1; $i<=$key; $i++) {
$ar_where[] = '`'. $ar_cols[$i-1]. "`='". $_SESSION['ar_cols'][$ar_cols[$i-1]] ."'";
}
// define a string with the WHERE condition, and then the SELECT query
$where = isset($ar_where) ? ' WHERE '. implode($ar_where, ' AND ') : '';
$get_cols = $key<$last_key ? $col : $col .($nrc >0 ?','. implode(',', $extra_desc) :'');
$get_cols = '`'. str_replace(array('`', ','), array('', '`,`'), $get_cols) .'`'; //add the column names between `..`
$sql = "SELECT DISTINCT $get_cols FROM `$table`".$where;
$result = $conn->query($sql); // perform the query and store the result
// if the $result contains at least one row
if ($result->num_rows > 0) {
// sets the "onchange" event, which is added in <select> tag
$onchg = $next_col!==null ? " onchange=\"ajaxReq('$next_col', this.value);\"" : '';
// sets the select tag list (and the first <option>), if it's not the last column
if($col!=$ar_cols[$last_key]) $re_html = $col. ': <select name="'. $col. '"'. $onchg. '><option>- - -</option>';
while($row = $result->fetch_assoc()) {
// if its the last column, set a tablle with description, else, adds data in OPTION tags
if($col==$ar_cols[$last_key]){
$re_html .= '<table><tr><td>'. $row[$col] .'</td></tr>'; //start table with data from first column for description
for($i=0; $i<$nrc; $i++) $re_html .='<tr><td>'. $row[str_replace('`', '', $extra_desc[$i])] .'</td></tr>'; //add data from $extra_desc columns
$re_html .='</table>'; //end table
}
else $re_html .= '<option value="'. $row[$col]. '">'. $row[$col]. '</option>';
}
if($col!=$ar_cols[$last_key]) $re_html .= '</select> '; // ends the Select list
}
else { $re_html = '0 results'; }
$conn->close();
// if the selected column, $col, is the first column in $ar_cols
if($col==$ar_cols[0]) {
// adds html code with SPAN (or DIV for last item) where Ajax will add the select dropdown lists
// with ID in each SPAN, according to the columns added in $ar_cols
for($i=1; $i<count($ar_cols); $i++) {
if($ar_cols[$i]===null) continue;
if($i==$last_key) $re_html .= '<div id="'. $preid.$ar_cols[$i]. '"> </div>';
else $re_html .= '<span id="'. $preid.$ar_cols[$i]. '"> </span>';
}
// adds the columns in JS (used in removeLists() to remove the next displayed lists when makes other selects)
$re_html .= '<script>var ar_cols = '.json_encode($ar_cols).'; var preid = "'. $preid. '";</script>';
}
else echo $re_html; - Attachments
-
- multiple_select_dropdown_list.zip
- (6.18KiB)Downloaded 477 times
petitesouris
Posts:4
Thank you so much it does work so well and does what I wished for.
For the name with space my database in sql is built like that "start _date", it was only for the view but happy to change it
I like to do the right thing. Is there a way I can make a donation?
Also the next thing I would like to do is have the info display in a table so it is nicely presented but I will try first to think how it is done before I bother you again.
As I said... I have never touched sql before and it took me 3 weeks to understand how to built my database
then never touched php so it took me a a week even to make the thing work without modification, I am an autodidact and without knowledge you spend a lot of time.
You made my day thank you so much !
For the name with space my database in sql is built like that "start _date", it was only for the view but happy to change it
I like to do the right thing. Is there a way I can make a donation?
Also the next thing I would like to do is have the info display in a table so it is nicely presented but I will try first to think how it is done before I bother you again.
As I said... I have never touched sql before and it took me 3 weeks to understand how to built my database
then never touched php so it took me a a week even to make the thing work without modification, I am an autodidact and without knowledge you spend a lot of time.
You made my day thank you so much !