Hi all,

I'm having difficulty with a function I've written. What it does, is creates a standardised list of Options Value's in a Selector for HTML. The value of the MySQL query is then used as the value, but for some reason, I'm getting an error in the code. If you could take a look and try to make sense of it; that would be extremely awesome. The error seems to be that $optionVal appears to already have a value - as though it's the last result pulled from the database. The ORIGINAL function (see below) works perfectly on the production server and so it's strange how what I see as adding in a locale check and moving the array over by one position - it messes up. :(

Code as follows;

dbfunctions.inc.php

// Function to write <SELECT> option lines for given query
function frmConfigurationDroplist($dbName,$tabName,$selectionValue,$prompt=TRUE) {
    $UC = $GLOBALS['userclient'];
// Open the correct database
    dbConnect();
//Open prod_has and get the locale ID from conf_settings.
    dbOpenDatabase($GLOBALS['db_has']);
    $loc_sql = "SELECT locale_id FROM conf_settings WHERE client = '".$UC."'";
    $loc_que = mysql_query($loc_sql);
    $loc_res = mysql_fetch_row($loc_que);
    $locale_id = $loc_res[0];

//Move on to the drop list - select appropriate database as passed in by $dbName in function.
    $dbLink = dbOpenDatabase($dbName);

// Build query statement
    if ($tabName == "conf_injclass" && $UC == "022") {
    $query = "SELECT * FROM $tabName ORDER BY alternative_order ASC";
    } else {
    $query = "SELECT * from $tabName";
    }
//if the table being accessed is listed below, concatenate the locale ID claus on the end.
    if ($tabName == 'conf_inctype' ||
    $tabName == 'conf_inc_injury_sustained' ||
    $tabName == 'conf_inc_main_factor' ||
    $tabName == 'conf_inc_sub_activity' ||
    $tabName == 'conf_inc_work_process' ||
    $tabName == 'conf_injclass' ||
    $tabName == 'conf_injeffect') {
        $query .= " WHERE locale_id = '$locale_id'";
    }
//Now order the results, whether locale ID is there or not.
    $query .= " ORDER BY 1";

// Retrieve all configuration values from table
    $conflist = mysql_query($query);
    if (!($conflist)) {
// No values retrieved from table
        print("<option value=\"999\">No Values: $query</option>\n");
    } else {
// Write initial selection value - prompt to choose value
        if ($prompt) {
            print("<option value=\"\">Make a selection...</option>\n");
        }
        $selectionArray = array();
        if (is_array($selectionValue)) {
            foreach($selectionValue as $currval) {
                echo $currval."\n";
                $selectionArray[] = explode(":",$currval);
            }
        } elseif (isset($selectionValue)) {
            $selectionArray[] = explode(":",$selectionValue);
        }
// Write values retrieved as <OPTION> statements within a <SELECT>
        while ($listarray = mysql_fetch_row($conflist)) {
// Print option value
            $optionVal = implode($listarray, ":");
            $selected = false;
            if (count($selectionArray) > 0) {
                foreach($selectionArray as $currarray) {
                    if ($listarray[0] == $currarray[0]) {
                        $selected = true;
                    }
                }
            }

            if ($selected) {
                print("<option value=\"$optionVal\" selected>$listarray[2]</option>\n");
            } else {
// if check for retired items, and doesnt display the option UNLESS it is already selected, so that previous documents will still work correctly!!
                if($listarray[4] !='1') {   
                print("<option value=\"$optionVal\">$listarray[2]</option>\n");
                }
            }
        }
// printing stuff in options so I can determine errors. 
    print("<option value=\"999\"> </option>\n");
    print("<option value=\"999\"> </option>\n");
    print("<option value=\"999\">QUERY IS: $query</option>\n");
    print("<option value=\"999\">LOC ID IS: $locale_id</option>\n");
    print("<option value=\"999\">OPTION VAL IS: $optionVal</option>\n");
    print("<option value=\"999\">SELECTION ARRAY IS: $selectionArray</option>\n");
    print("<option value=\"999\">SELECTION VALUE IS: $selectionValue</option>\n");
    }
} //End function

Stage2a.html

<td class="formName" nowrap align="right">Main Factor Involved</td>
<td>
    <select name="frm_mainfactor" class='formField'> 
        <?php 
            if (!($frm_mainfactor == '')) {
                frmConfigurationDroplist($GLOBALS['db_has'],"conf_inc_main_factor",$frm_mainfactor,TRUE);
            } elseif (!($GLOBALS['per_mainfactor'][$frm_personnelcount] == '')) {
                frmConfigurationDroplist($GLOBALS['db_has'],"conf_inc_main_factor",$GLOBALS['per_mainfactor'][$frm_personnelcount],TRUE);
            } else {
                frmConfigurationDroplist($GLOBALS['db_has'],"conf_inc_main_factor","",TRUE);
            }
        ?>
    </select>
</td>

incident.sessions.php

// Stage 2a
session_register('per_dateofbirth');
session_register('per_riddor');
session_register('per_county');
session_register('per_country');
session_register('per_age');
session_register('per_sex');
session_register('per_jobtitle');
session_register('per_inctype');
session_register('per_inctype_text');
session_register('per_inj_hse');
session_register('per_statement');
session_register('per_company_medical_scheme');
session_register('per_com_employment');
session_register('per_time_in_position');
session_register('frm_per_inj_hse');
session_register('frm_per_inctype_text');
session_register('frm_per_inctype');
session_register('frm_per_inj_hse');
session_register('per_injsus');
session_register('per_mainfactor'); //Added "per_" @ JUNE 15 - 9:50am to run inline with rest of system.

The frmConfigurationDroplist() function is used throughout our software to compile a list of Selection Options. I have added in Locale ID stuff, and expanded on it somewhat. The original can be found below; (I will post results and some screenshots too.)

Please note, that each of the tables that USED to have 2 columns (an ID and Description) in the tables in the first code snippet above (the ones in an IF for the locale id) are now 5 columns, and each one has moved over once. Therefore what used to be listarray position 0 is now listarray position 1.

Original function in dbfunction.inc.php

// Function to write <SELECT> option lines for given query
// Assumes 2-field table : key, description
function frmConfigurationDroplist($dbName,$tabName,$selectionValue,$prompt=TRUE)
{
// Open the correct database
    dbConnect();
    $dbLink = dbOpenDatabase($dbName);
    $uc=$GLOBALS['userclient'];
// Build query statement
    if ($tabName == "conf_injclass" && $uc == "022") {
    $query = "SELECT * FROM $tabName ORDER BY geopost_order ASC";
    } else {
    $query = "SELECT * from $tabName ORDER BY 1";
    }
// Retrieve all configuration values from table
    $conflist = mysql_query($query);
    if (!($conflist))
    {
// No values retrieved from table
//      print("<option value=\"999\">No values - table '$tabName'.</option>\n");
        print("<option value=\"999\">$query</option>\n");
    } else {
// Write initial selection value - prompt to choose value
        if ($prompt)
        {
            print("<option value=\"\">Make a selection...</option>\n");
        }
        $selectionArray = array();
        if (is_array($selectionValue))
        {
            foreach($selectionValue as $currval)
            {
                echo $currval."\n";
                $selectionArray[] = explode(":",$currval);
            }
        } elseif (isset($selectionValue))
        {
            $selectionArray[] = explode(":",$selectionValue);
        }
// Write values retrieved as <OPTION> statements within a <SELECT>
        while ($listarray = mysql_fetch_row($conflist))
        {
// Print option value
            $optionVal = implode($listarray, ":");
            $selected = false;
            if (count($selectionArray) > 0)
            {
                foreach($selectionArray as $currarray)
                {
                    if ($listarray[0] == $currarray[0])
                    {
                        $selected = true;
                    }
                }
            }

            if ($selected)
            {
                print("<option value=\"$optionVal\" selected>$listarray[1]</option>\n");
            } else {
// if check for retired items, and doesnt display the option UNLESS it is already selected, so that previous documents will still work correctly!!
        if($listarray[3] !='1') {   
                print("<option value=\"$optionVal\">$listarray[1]</option>\n");
            }
        }
        }
    }
}

I apologise for the messy state above in the last snippet, that's how I found it. :P

Here's what they look like;

Screen_Shot_2012-06-15_at_14.26_.10_ http://i48.tinypic.com/2myrwv8.png

I meant to add the database table so you can see what it looks like on the production server and on the development server.

Development:
(conf_inc_main_factor)

Screen_Shot_2012-06-15_at_14.34_.02_

Production:
(conf_inc_main_factor)

Screen_Shot_2012-06-15_at_14.35_.55_

If you need any further information on this, please feel free to let me know, I will try to be as descriptive as possible. Thanks for any help!

I think the problem is that if this condition succeed:

if ($tabName == "conf_injclass" && $UC == "022") {

you obtain a query like this:

SELECT * FROM $tabName ORDER BY alternative_order ASC WHERE locale_id = '$locale_id' ORDER BY 1

which is not correct.

Aye, that's only if the $tabName variable is equal to the table name "conf_injclass" though.

So on the field in the screenshot (Main Factor Involved) - you see in the drop down that the table being access is correct and the data collected is correct. But for some reason; the $optionVal variable is setting itself before it should be set - to "Other" - see last few options on the Drop Down Selectbox. After the gap, they're printed variables.

$listarray should be imploded from $conflist - the actual query. $optionVal is a comparison operator, 2 arrays within in an array - if they are equal to each other, then the array is used as a selection.

But that's not working properly :/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.