Autopopulating Select field using PHP and mySQL

[galahad2]

Hi, this is an issue that a lot of people have come across and apparently there are fixes using Jquery, Json and all sorts of things- but my specific need is a bit different and I can't find any article that addresses it exactly.

So I basically have two dropdowns (selects) in a form, let's call them Category and Name. Category has a list of values you can pick, say Supplier, Client, Associate etc. Now, each value corresponds to a table in the mySQL db, so there's a table called Suppliers, one called Clients and so on.

What I need is: when the user picks a value from Category, the Select / dropdown called "Name" autopopulates with a list of names from that table. So if the user selected Associates as the category, when he or she opens the second dropdown, there are the list of Associates' names already there for them to pick from, taken from the appropriate database table.

The reason I'm doing this is so that the user can't simply add in arbitrary values for Name (e.g if it was just a normal text input), the Name has to be picked from the values in the appropriate table.

Anyone know how this can be done, bearing in mind I'm connecting to multiple mySQL tables here?

Thanks

[Demaestro]

gala you can achieve this with an Ajax solution.

Basically it goes like this:

Register a onblur/onchange/onclick event on the 1st drop down box.

Create a javascript function that gets invoked by the above mentioned events.

The javascript function makes an Ajax request to a PHP page on the server... something like:

http://example.com/dropdown.php?cat=SELECT_FIELD.value

The php method takes the category and queries the corresponding table and responds to the Ajax request with an xml file containing all the values from the table.

The Javascript method then takes that XML file and creates the values for the 2nd drop down.

[eelixduppy]

It will be very similar to the solution provided here: [webmasterworld.com...]

The only difference is how you handle the PHP code and the query. Instead of a variable WHERE clause in the query like is shown, you'd have to check to see if they selected an acceptable table name and use that for the query.

[galahad2]

Thanks, I will take a look...

[galahad2]

Ok, had a look at that link, the only problem with it is that the PHP only caters for a single table in the db, my problem is that I have several different tables that the data needs to be pulled from, depending on what value is selected in the first SELECT...

[eelixduppy]

Yes, so you have to run a query that has a variable table name.....

Example:

$table = $_GET['table'];
$acceptable = array('table1', 'table2', 'table3');

if(!in_array($table, $acceptable)) exit;

$query = sprintf("SELECT `col` FROM `%s`", $table);
#etc....

[galahad2]

Also tried editing the PHP so that it picks up a variable to insert as the table name, but that doesn't do anything:

[PHP]
<?php
session_start();
require_once('settings.php');
checkLogin ( '1' );

include ('inc/dbconnect.php');

if(empty($_GET['_Category'])) { # if url query is left empty, do nothing but terminate the script
exit();
}
$category = $_GET['_Category'];

#
#select records based on 'topic'
$query = "SELECT Name FROM " . $category . "ORDER BY Name";
$result = mysql_query($query);
#
if($result) { #make sure the query was successful
$items = array();
#
while($row = mysql_fetch_array($result)) {
$items[] = $row['Name']; #push all of the results into an array
}
#
$string = implode(',',$items); #implode the results separated by commas
echo $string; #here we echo the string to the browser; this is what the javascript will be receiving
}
?>
[/PHP]

[eelixduppy]

Try removing the session stuff and the credential check. Also, your query is going to cause an error because you are missing a space before ORDER. Should be:

$query = "SELECT Name FROM " . $category . " ORDER BY Name"; 

But also try to do a check like I have shown in my previous post so that your query cannot be injected with just any SQL string.

[galahad2]

Also when I select one of the values in the first SELECT, although the second SELECT becomes enabled, it shows no values.

I added echo $category; into the PHP and this verified that the value was being picked up (it was showing in the second SELECT)

Something's clearly wrong but I don't see what it could be...

[galahad2]

Tried removing the session / crednetial check at the top, and used the suggested $query... but no joy.

<?php 

include ('inc/dbconnect.php');

if(empty($_GET['_Category'])) { # if url query is left empty, do nothing but terminate the script 
  exit(); 
} 
$category = $_GET['_Category']; 
//echo $category;
# 
#select records based on 'topic' 
$query = "SELECT Name FROM " . $category . " ORDER BY Name"; 
$result = mysql_query($query); 
# 
if($result) { #make sure the query was successful 
  $items = array(); 
# 
  while($row = mysql_fetch_array($result)) { 
   $items[] = $row['Name']; #push all of the results into an array 
  } 
# 
$string = implode(',',$items); #implode the results separated by commas 
echo $string; #here we echo the string to the browser; this is what the javascript will be receiving 
} 
?> 

[eelixduppy]

>>

$_GET['_Category']

This seems wrong, unless your URL query looks like this:

?_Category=table_name

. Change this to the following:

$_GET['Category']

and make sure your URL query looks like this:

?Category=table_name

Change to this for debugging purposes:

$result = mysql_query($query) or die(mysql_error());

Also, instead of using the AJAX request, just try visiting this page manually by typing in the URL in the address bar with the appropriate table_name in the URL.

[rocknbil]

<aside>I wouldn't go for an Ajax solution at the outset, I'd build it in PHP and add Ajax later, so that way it will work without Javascript and allow Ajax to enhance it.</aside>

Structure your logic something like this. You'd have to write these functions. Don't reveal your table name publicly, that's bereft with security issues. Map it, like this, where the ID's correspond to the selected category . . .

$subcattables = Array(
'1' => 'real-client-table-name',
'2' => 'real-sales-table-name'
// etc
);


$content = output_form();
echo $content;

function output_form() {

// Set these null to avoid concatenation errors
$categoryList=$subcategoryList=null;

// Assuming you are using numeric values for option values
// and textual for the option label. Which you should. :-)
if isset($POST['category'] and is_numeric($_POST['category']) and ($_POST['category'] > 0)) {
$subcat_table=$subcattables[$_POST['category']];
$subcategoryList = get_subcat('subcategory',$subcat_table);
}

$categoryList = get_category('category');

Then output the form with the variables in place.
$form = "<form action="blah bla" method="post">";
if ($categoryList) { $form .= "<p>$categoryList</p>"; }
if ($subcategoryList) { $form .= "<p>$subcategoryList</p>"; }
$form .= "
<!-- other form elements -->
</form>
";

return $form;
}

Then each function looks up the appropriate table, composes, and returns, the select lists. In the case of get_subcat, it requires the table name as a parameter . . .

function get_subcat($name,$subcattable) { 
 $subcat = "<label for=\"$name\">Subcategory:</label> 
 <select name=\"$name\" id=\"$name\"> 
 <option value=\"\">Select Subcategory</option> 
 "; 
 $query = "select id,title from $subcattable order by title"; 
 $result = mysql_query or die("cannot query for subcat " . mysql_error()); 
 while ($row=mysql_fetch_array($result)) { 
 $subcat .= '<option value="' . $row['id'] . '"'; 
 if (isset($_POST[$name]) and ($_POST[$name]==$row['id'])) { $subcat .= ' selected'; } 
 // selected="selected" for XHTML. :-/ 
 $subcat .= '>' . $row['title'] . "</option>\n"; 
 } 
 $subcat .= "</select>\n\n"; 
 return $subcat; 
} 

This allows you to display any subcategory list for any category anywhere, which is especially useful if you have multiple forms (quick search and advanced search?) The category function would be similar, just requiring name for a parameter.

Code above is **not** copy and paste, typed on the fly.

[galahad2]

@eelix: the reason I used _Category was because that's the name of the first SELECT- so that should be the subject of the GET statement? Or not? ? Thanks

@rocknbil: Seems pretty complicated- I'll give it a go though...

[eelixduppy]

No, it's all about how you construct the url in your javascript request here:

sendRequest('fetch.php?topic='+el.options[selected].value);

Here it's

$_GET['topic']

. You would have to change this line to reflect the PHP code, assuming you are using the javascript provided in the example.

[galahad2]

Well, I changed the combo.js as follows:

//globals 
var first = "_Category"; //id of first SELECT 
var second = "_Name"; //id of second SELECT 
// 
function sendRequest(url,params,HttpMethod) { 
if(!HttpMethod) { //check if http method is defined, if not, set it to GET 
  HttpMethod="GET"; 
} 
// 
// initialize request object 
req=null; 
if(window.XMLHttpRequest){ 
  req=new XMLHttpRequest; //mozilla/safari 
} else if(window.ActiveXObject){ 
  req=new ActiveXObject("Microsoft.XMLHTTP"); //internet explorer 
} 
// 
//define callback handler 
if(req) { 
// 
  req.onreadystatechange=onReadyState; 
  req.open(HttpMethod,url,true); 
  req.setRequestHeader("Content-Type","application/x-www-form-urlencoded"); 
  req.send(params); 
} 
} 
// 
function onReadyState() { 
// 
var ready=req.readyState; 
var data=null; 
if(ready==4){ //check ready state 
  data=req.responseText; //read response data 
  var items = data.split(','); 
  var length = items.length; 
  for(var i = 0; i < length; i++) { 
   var childEl = document.createElement('option'); //create option 
   var El = document.getElementById(second); 
   El.appendChild(childEl); //then append it to the second dropdown list 
   childEl.value = items[i]; 
   childEl.innerHTML = items[i]; 
  } 
} 
} 
// 
function clicked() { 
// 
var el = document.getElementById(first); 
var ob2=document.getElementById(second); 
var selected = el.selectedIndex; 
// 
while(ob2.hasChildNodes()) { //removes items from dropdown if some already exist 
  ob2.removeChild(ob2.firstChild); 
} 
if(selected!= 0) { //if they choose something other than the first select-->"Select topic first" 
  sendRequest('fetch.php?category='+el.options[selected].value); 
  ob2.disabled=0; 
} else { //otherwise add the Select Topic First option and disable it 
  var childEl = document.createElement('option'); 
  ob2.appendChild(childEl); 
  childEl.innerHTML = 'Select Category First'; 
  ob2.disabled=1; 
} 
} 

And changed fetch.php as follows:

<?php 

include ('inc/dbconnect.php');

if(empty($_GET['category'])) { # if url query is left empty, do nothing but terminate the script 
  exit(); 
} 
$category = $_GET['category']; 
//echo $category;
# 
#select records based on 'topic' 
$query = "SELECT Name FROM " . $category . " ORDER BY Name"; 
$result = mysql_query($query); 
# 
if($result) { #make sure the query was successful 
  $items = array(); 
# 
  while($row = mysql_fetch_array($result)) { 
   $items[] = $row['Name']; #push all of the results into an array 
  } 
# 
$string = implode(',',$items); #implode the results separated by commas 
echo $string; #here we echo the string to the browser; this is what the javascript will be receiving 
} 
?> 

But the second Select is still coming up blank even after I pick a value in the first Select...?

[eelixduppy]

And you are sure that fetch.php is returning results? Did you try going to that page manually?

[galahad2]

Thanks for the tip, reckon I've sorted it now after running an echo of the sql statement.

[eelixduppy]

Glad it is working.

Now, you should take into consideration rocknbil what has posted. If the user has javascript disabled your solution will not work.

Don't reveal your table name publicly, that's bereft with security issues. Map it, like this, where the ID's correspond to the selected category . . .

This is also good advice to follow, especially since the way you are doing it now I can easily inject SQL into your query.

[Bookofradeluxe]

Thanks, I will take a look...