Need direction on listboxes 2

[ZOR]

I have been searching for sometime how I construct an HTML listbox which will display the row content of a MySql database, based on a query. Even if without query, how does one fill a listbox. I can then move on to how one posts a selection from listbox. Have someone leaning over me thinking why am I taking so long! Many thanks

 

[Vragabond]

Please be more specific in your request. As you know by now, php can easily output any html code you want via print() or echo commands. Outputting a select box should therefore be as straightforward as any other output, unless there are some additional binding conditions. In that case, you need to let us know what those are, before we can help you. Also, please detail in full whether it is the php output or the html code for a "listbox" that is giving you trouble.

 

[ZOR]

Many thanks. I am trying to fill a listbox from the contents of a table. Having an option select, being posted to another page. I tried this but did not work.

Connection to db ommitted.

$query = "SELECT Caption FROM Template ";
$result = mysql_query($query);
$numrows=mysql_numrows($result);
if ($numrows >0) {
// at least one row
while($row = mysql_fetch_row($result))
{
print("<option value=\"$row[0]\">$row[0]</option>");
}
} else {
//print("<option value=\"\">No data available</option>");
}

Basically I am wanting a listbox to appear on an HTML page, having a list visible showing records from an order table where the user can click on an order number in the list which then gets posted to another page having a query that then produces another list of all row items of that order. I know it could be done in java but I want to try and keep it all in php. Hope thats made it a bit clearer. Thanks again

 

[Vragabond]

Here's what you need do:

1. You need to put the [tt]option[/tt] tags inside a select tag. I cannot see that in your code. I would print out the select tag at the beginning of the if sentence that checks the number of rows. That way, if there are no rows, you don't need to print out the select tag at all.
2. You will need to submit the form after the order is selected. You can either create a button and have the user press it when they make their selection or use javascript to automatically submit the form. For the latter, you would add [tt]onchange="this.form.submit()"[/tt] to your select tag.
3. In the script, check for the existance of order. If it exists (the form has already been submitted), then run the query with this specific order. If not, output the original form with the list of orders.

Hope that makes sense.

 

[ZOR]

Thanks, I straightened it out a bit and got some result.

echo "<select name=\"Orders\">";
echo "<option size =30 selected>Select</option>";
if(mysql_num_rows($result))
{
while($row = mysql_fetch_assoc($result))
{
echo "<option>$row[Caption]</option>";
}
}
else {
echo "<option>No Data Present</option>";
}

echo '</select name>';

Is there a way to show other items in the list row, possibly with alignment in columns?

 

[jpadie]

use long strings and a mono-space font with space-padding

 

[ZOR]

Thanks Jpadie. I added another field in my row and used:
echo "<option>$row[Caption],$row[Description]</option>";

How can I get a space in the above? Thanks

 

[jpadie]

let's assume that each column is to be fixed width as 25 chars

printf("\t<option value=\"%s\" style=\"font-family: monospace;\">%25s %25s</option>\r\n", $value, $row['Caption'], $row['Description']);

note that i have added in a value argument (as well as some html formatting. you should include this so as not to return the formatted string (with all the spaces) as part of the form submission

i have also added a simple style declaration to make the options monospaced. if you don't have a monospace font then the columns will not line up.

ideally you would know what the longest item in any column was and set the string mask accordingly. this would involve walking through the recordset and reading each item into an array for later reuse. at the same time set a one-way floating variable to capture the strlens

while ($row = mysql_fetch_assoc($result)):
  if (strlen($row['Caption']) . $max['Caption']) 
{$max['Caption'] = $row['Caption'];}
  if (strlen($row['Description']) . $max['Description']) {$max['Description'] = $row['Description'];}
  $rows[] = $row;
endwhile;
echo "<select name=\"selectbox\">\r\n";
foreach ($rows as $row):
 printf("\t<option value=\"%s\" style=\"font-family: monospace;\">%".($max['Caption'] + 1)."s %".$max['Description'] ."s</option>\r\n", $value, $row['Caption'], $row['Description']);
endforeach;
echo "</select>";

note that strictly speaking you don't need to worry about the padding of the last column as the browser will automatically pad the select box to fit the longest option contents.

 

[ZOR]

Thanks, that was welcome, however I'm getting a strange result. The list shows the Caption field, then the word ini (which I cannot trace) followed by the last records description. This is on every row of the list. I put in some bits to see what was there all the time the list was being assembled, and everything was correct there?

while ($row = mysql_fetch_assoc($result)):
if (strlen($row['Caption']) . $max['Caption'])

print ('a '. $row['Caption'] . "<br>\n");

{$max['Caption'] = $row['Caption'];}
print ('a2 '. $max['Caption'] . "<br>\n");

if (strlen($row['Description']) . $max['Description']) {$max['Description'] = $row['Description'];}
print ('b '. $row['Description'] . "<br>\n");

$rows[] = $row;
print ('b2 '. $max['Description'] . "<br>\n");

a,a2,b,b2 show all the correct items. Have I to put any parameters in anywhere? Thanks

 

[jpadie]

there is an error in my code. i did not press shift...

change this line

if (strlen($row['Caption']) . $max['Caption'])

to

if (strlen($row['Caption']) > $max['Caption'])

and

  if (strlen($row['Description']) . $max['Description'])

to

  if (strlen($row['Description']) > $max['Description'])

 

[ZOR]

Thanks. I replaced the . with >, but still same result in the list, the word ini where a space should be followed by the last records description. My page is a .php page, and contains no HTML section. Strange one. I wish I could identify where ini comes from.

 

[jpadie]

zor

i've misled you. first the code still has errors. this is what the if statements should look like

 if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
  if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}

second, it seems that the browsers ignore spaces within the option element. so multicolumnar select boxes won't work. you could "cheat" by inserting an underscore instead

printf("\t<option value=\"%s\" style=\"font-family: monospace;\">%-'_".($max['Caption'] + 1)."s %-'_".$max['Description'] ."s</option>\r\n", $value, $row['Caption'], $row['Description']);

the best alternative i could come up with is to use optgroup and option tags together to create an indented column. not perfect by any means.

apologies for earlier red herring.

 

[jpadie]

oh rubbish, rubbish rubbish. i'm not thinking straight.

there is a really easy css fix...

see this code for example:

<? 
//dummied for debugging
for ($i=1; $i<=26; $i++):
	$_rows[] = array("Description"=>"Description_$i", "Caption"=>"Caption_$i", "Value"=>$i);
endfor;

foreach ($_rows as $row):
  if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
  if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}
  $rows[] = $row;
endforeach;

echo "<pre>";
echo "<select name=\"selectbox\" multiple rows=\"10\">\r\n";
foreach ($rows as $row):
	echo "<option style=\"white-space:pre; font-family:monospace;\" value=\"{$row['Value']}\">";
	printf("%-' 25s", $row['Caption']);
	echo "\t";
	printf("%-' 25s", $row['Description']);
	echo "</option>\r\n";
endforeach;
echo "</select>";

?>

really sorry for mucking around. eyes burning too much...

 

[ZOR]

Thanks, don't worry, appreciate you trying to help. I tried the code, but could not get a space between the two row items unless I put points after item.

$_rows[] = array("Description"=>"Description_$i......", "Caption"=>"Caption_$i......", "Value"=>$i);

I then ran into trouble not being able to replace the items with my table data. I come from working with VB6, and find it such a culture change in this area, I could have wrapped it all up in 5 minutes with VB.

Will sleep on it for tonight.

 

[jpadie]

the dots are a cheat and kind of avoid the problem. the solution I posted works fine for me in firefox. I admit not to testing it in ie but will do so in the morning. it relies on the browser accepting the white-space css declaration.

the soln above contains redundant code from various interim explorations into debugging. I will post some clean code and a url with a working two column select box in the morning too.

 

[Vragabond]

Of course, much easier than mucking around with white space css declarations is to replace a space with a simple [tt][ignore]nbsp;[/ignore][/tt], which works fine in all browsers.

 

[jpadie]

Of course, much easier than mucking around with white space css declarations is to replace a space with a simple nbsp;, which works fine in all browsers.

i tried this with printf and could not get it to work. i accept that you can add the non-breaking spaces using strlen but that's a bit of an ugly solution (imo)

 

[jpadie]

i take it back. no more ugly than mine (and they come to the same thing in the end, of course).

here is the code i eventually used:

for ($i=1; $i<=26; $i++):
	$_rows[] = array("Description"=>"Description_$i", "Caption"=>"Caption_$i", "Value"=>$i);
endfor;

foreach ($_rows as $row):
  if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
  if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}
  $rows[] = $row;
endforeach;

$contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"25\">\r\n";
foreach ($rows as $row):
	$contents .= "<option value=\"{$row['Value']}\">";
	$contents .= sprintf("%-'#".($max['Caption'] + 2)."s", $row['Caption']);
	$contents .= sprintf("%-'#". ($max['Description'] + 2)."s", $row['Description']);
	$contents .= "</option>\r\n";
endforeach;
$contents .= "</select>";
$contents = str_replace("#", "&nbsp;", $contents);
echo "<div>Original code</br>$contents</div>";


echo "<div>Vragabond's suggestion<br/>";
$items = array("Caption", "Description");
$options = "";
foreach ($_rows as $row):
	foreach ($items as $item):
		if (strlen($row[$item]) > $max[$item]) {$max[$item] = strlen($row[$item]);}
	endforeach;
endforeach;
foreach ($_rows as $row):
	foreach ($items as $item):
		for($i=strlen($row[$item]); $i<($max[$item] + 2); $i++):
			$pad[$item] .= "&nbsp;";
		endfor;
	endforeach;
	$options .= '<option value="'.$row['Value'].'">'.$row['Caption'].$pad['Caption'].$row['Description'].$pad['Description'].'</option>';
	$pad = array();
endforeach;
echo "<select size=\"25\" style=\"font-family:monospace\">$options</select></div>";
?>

you can see the code in action here: [ignore]

http://www.tmogolf.co.uk/tektips/thread434-1272230/index.php

[/ignore]

 

[ZOR]

Many thanks Jpadie, thats fantastic, looks excellent, my data sits in there a treat. I just need to clarify where my posted value is, is selectbox the name or is that to identify the control?.

This site is very bad with its star awards, there should be an option of 1,2,3 star values for gratitude. Appreciate all you have done. Thanks Vragabond for your input.

 

[jpadie]

the select's value will either be in $_POST['nameofselectbox'] or the $_GET equivalent depending on what form submission method you choose. note that i have not include a name attribute for the select boxes in my example.