[SOLVED] mysql query trouble

[tomasd]

 

I have a question on how to do a simple mysql query...

$r_departments = mysql_query("SELECT * FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby") 
or error("Cannot load departments");


while($db_departments = mysql_fetch_object($r_departments))
{
$cid = $db_departments->department_id;
$lst_departments .= "<option value=\"$db_departments->department_id\"$cur_department[$cid]>$db_departments->department_name</option>\n";
}

 

After echo'ing:

$departments .="<select name=\"user_department\" size=\"5\" id=\"user_department\">/option> $lst_departments";

 

I'm getting nice select field generated, however I need somehow to tell to select a name in the selection, I am able to do something simillar using:

$departments .="<select name=\"user_department\" size=\"5\" id=\"user_department\">/option> <option selected>Accounting</option> $lst_departments";

 

This way I'm getting a new accounting entry put in selection field instead of selecting accounting from generated list...

 

Any ideas on how I should form mysql query to achieve this?

 

any help is much appreciated!

 

[Wildbug]

$departments .="<select name=\"user_department\" size=\"5\" id=\"user_department\">/option> $lst_departments";

 

It looks like you're missing an angle bracket (<) before the opening option tag in your 2nd code fragment above.

 

If you know what option you wish to be selected, have that value in a variable and just compare it to the query output while generating the list.  Echo a SELECTED when it matches.

 

Something like so:

<?php
$current_value = "whatever";

while ($row = mysql_fetch_row($result)) {
     echo "<option value=\"$row[0]\"",$row[0] == $current_value ? ' SELECTED' : '',">$row[0]</option>";
}
?>

[bubblegum.anarchy]

Also consider wrapping the strings with html content in single quotes so that the following:

 

$departments .= "<select name=\"user_department\" size=\"5\" id=\"user_department\">$lst_departments</option>";

 

Would be more readable in this format:

 

$departments .= '<select name="user_department" size="5" id="user_department">$lst_departments</option>';

[Wildbug]

Bubblegum: Then variable interpolation wouldn't work.  You'd need to use sprintf() (or concatenation) to switch to single quotes while still retaining the variable-inserting functionality.

[bubblegum.anarchy]

Yeah sure... I usually code with a function to insert prepared variables:

 

$departments .= '<select name="user_department" size="5" id="user_department">'.to_html($lst_departments).'</option>';

[tomasd]

Thanks for your replies, I'm trying to use a diff approach and instead of querying an object I chose to use mysql_fetch_array so

<?php
$r_departments = mysql_query("SELECT * FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby") 
				or error("Cannot load departments");

while ($row = mysql_fetch_array($r_departments))
{
$stack[$row[0]] = $row[1];
}


print_r($stack);
?>

From print_r($stack); I'm getting the following:

Array
(
    [1] => Accounting
    [2] => Banqueting
    [3] => Executive Office
    [4] => Front Office
    [5] => Housekeeping
    [6] => Human Resources
    [7] => Kitchen
    [8] => Loss Prevention
    [9] => Mini Bar
    [10] => Repairs & Maintenance
    [11] => Revenue
    [12] => Room Service
    [13] => Sales & Marketing
)

How do I go about writing a function where I could specify department ID and this would add <option value=$var selected>$stack[$var]</option> where $var can be preset from 1 to 13 or whatever and for the rest have only <option value=$var>$stack[$var]</option>, the catch is to have this arranged by the ID 1,2,3,4…n I just can’t figure out how to get this done.

Any ideas and help is much appreciated, also is this the right way of doing this?

 

[bubblegum.anarchy]

This is a mysql forum, but anyway... I would do something similar to the following:

 

<?php
$result = mysql_query($query = "SELECT * FROM departments") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);

if (mysql_num_rows($result))
{
?>
     <SELECT id="department_id" name="department_id">
          <?php while ($record = mysql_fetch_assoc($result)):?>
               <OPTION value="<?php=$record['department_id'];?>"<?php if ($record['department_id'] == $_GET['department_id']):?> selected<?php endif;?>><?php=$record['department_name'];?></OPTION><?php endwhile;?>
     </SELECT>
<?php
}
else ; // handle null records
?>

 

<?php only used for colour coding.

[tomasd]

thanks for the tip, I thought I should move on to php help, I'll do that now