Using dropdown and mySQL & PHP

[RPGguy]

I have created a function (works fine) that will display a dropdown list of a columns values in a table. I want to create a dynamic sql WHERE clause based on the fields on a form and the values in them. When I place this statement in an empty script the text box works fine:

getdata("e107_act_emps", "position", "title", "title");

When I try and put it on a form nothing happens. The form will have the many fields found in an employee table that my users want to query on.

Can someone show me the code for a form that will have 2 drop down boxes using the function below.

<script>
<?
function getdata($file, $key, $desc, $order){
$sql = mysql_query("SELECT distinct $desc, $key FROM $file order by $order");
echo "<select name=$desc>";
while(list($desc, $key)=mysql_fetch_array($sql)){
$desc = stripslashes($desc);
echo "<option value=$key>$desc</option>";
}
echo "</select>";
mysql_free_result($sql);
}
?>
</script>

 

[DRJ478]

Think about this:

1. Hopefully - and it is a default setting - register globals is off.
2. Look at the form's method (either get or post).
3. Inspect the superglobal array $_GET (for get requests) or $_POST (for post requests).

I assume your form submits the data to itself (look at the action attribute of the form tag).

I also don't understand why your PHP is in a HTML scrip tag.

 

[RPGguy]

I didn't explain this very well so let me try again. I have a function where I pass file name, key, description and order by. This gives me a drop down list of the descriptions in the table and it works great.

What I don't understand is what the code looks like on my form to show the text 'Position:' and then present the drop down list. Also how do I reference the $key value on the form? The form may have the following:

Position: <drop down showing title but returning position #>
Job Code: <drop down showing job description but returning job code#>

When the user clicks the submit button I need to string an SQL WHERE statement together that concatenates the field name, an '=' sign and the key value they selected.

Thanks in advance for your help.

 

[DRJ478]

Thanks for providing more info.

Do you use context switching (PHP blocks inline with HTML)?
All you need then is to call the function in the context where you want to insert the created <select> element:

...
Position: <?getdata("e107_act_emps", "position", "title", "title");?>
...
Jobcode: <?getdata("e107_act_emps", "jobcode??", "title", "title");?>

Some fact to remember:
- The inline PHP is evaluated when the page loads.
- The form submits to somwhere (action attribute), PHP is not involved. ANy manipulation at that point would be client side and require code e.g. JavaScript.
- the receving script must process the submitted info and construct the SQL

I understood your question better, but I am not yet entirely sure if this is what you asked for.

 

[RPGguy]

Thanks. That's what I'm after. From reading other threads it looks like I should pass all the fields as an array and use something like this in the receiving script:

$clause_array=array();

if(($v_dd1)<>'') {$clause_array[] = "field1=$v_dd1";}
if(($v_dd2)<>'') {$clause_array[] = "field2=$v_dd2";}
if(($v_dd3)<>'') {$clause_array[] = "field3=$v_dd3";}
$clause = implode (" AND ",$clause_array);

 

[DRJ478]

Here's a suggestion:

Name the select elements consistently, e.g.

<select name="sel_jobcode">

If you do that you can iterate the $_POST variables and decide by the key to add it to the SQL WHERE clause:

foreach ($_POST as $key=>$value){
   #check if it's a select, and if not, continue (skip)
   if (!strstr($key,'sel_')) continue;

   # extract the fieldname
   $fieldname = strreplace('sel_',$key);
   if (!empty($value)){
      $wArray[] = $fieldname.'='.mysql_real_escape($value);
   }
}
# check if something was added to the where array
if (count($wArray>0)){
   # build SQL WHERE
   $clause = implode(' AND ',$wArray);
} else {
   # show all
   $clause = "1=1";
}

That's a simple way to do it.

 

[DRJ478]

One correction:
I truncated the function name for escaping the string:

http://www.php.net/mysql_real_escape_string