I have a table with a column named 'vendname'. This column isn't
completely populated so as I query the column within the table to
populate a pull down on a query page, there are many white spaces. My
intent is to eventually have all of those empty table cells populated
but until then, would really like to eliminate the white spaces and
hoped I might get some ideas here. As it currently stands, I have
approximately 50 rows with 15 'vendname' rows populated and a whole
bunch of white space that shows up on the pull down query.

My code is:

<?php
$hostName = "localhost";
$userName = "####";
$password = "########";
$dbName = "approvals";
$table = "approvals_tbl";

mysql_connect($hostName, $userName, $password) or die("Unable to
connect to host $hostName");

mysql_select_db($dbName) or die("Unable to select database $dbName");

$query = "SELECT DISTINCT vendname
FROM $table";
$result = mysql_query($query);

$number = mysql_numrows($result);

for ($i=0; $i<$number; $i++) {
$vendname = mysql_result($result,$i,"vendname");
print "<option value=\"$vendname\">$vendname</option>";
}

mysql_close();
?>

any is greatly appreciated.

John

You can add to your query:

where trim(vendname) <> ''

On Mar 26, 10:08 pm, jc...@lycos.com wrote:
> I have a table with a column named 'vendname'. This column isn't
> completely populated so as I query the column within the table to
> populate a pull down on a query page, there are many white spaces. My
> intent is to eventually have all of those empty table cells populated
> but until then, would really like to eliminate the white spaces and
> hoped I might get some ideas here. As it currently stands, I have
> approximately 50 rows with 15 'vendname' rows populated and a whole
> bunch of white space that shows up on the pull down query.
>
> My code is:
>
> <?php
> $hostName = "localhost";
> $userName = "####";
> $password = "########";
> $dbName = "approvals";
> $table = "approvals_tbl";
>
> mysql_connect($hostName, $userName, $password) or die("Unable to
> connect to host $hostName");
>
> mysql_select_db($dbName) or die("Unable to select database $dbName");
>
> $query = "SELECT DISTINCT vendname
> FROM $table";
> $result = mysql_query($query);
>
> $number = mysql_numrows($result);
>
> for ($i=0; $i<$number; $i++) {
> $vendname = mysql_result($result,$i,"vendname");
> print "<option value=\"$vendname\">$vendname</option>";
>
> }
>
> mysql_close();
> ?>
>
> any is greatly appreciated.
>
> John

trim in mysql
is as in, php dql VB Java Python lisp Ruby a 'common string manipulation
function'
http://en.wikipedia.org/wiki/Trim_(programming) and is coded -about the same
way

http://dev.mysql.com/doc/refman/5.0/...#function_trim
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM]
str)

Returns the string str with all remstr prefixes or suffixes removed. If none
of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed.
remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
This function is multi-byte safe.

see also ltrim and rtrim
--
If at first you dont succeed
try try try again
If at first you do succeed
try not to look surprised

_
"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:KbudnWyuZMuoiHbanZ2dnUVZ_jWdnZ2d@comcast.com. ..
> jcage@lycos.com wrote:
> > I have a table with a column named 'vendname'. This column isn't
> > completely populated so as I query the column within the table to
> > populate a pull down on a query page, there are many white spaces. My
> > intent is to eventually have all of those empty table cells populated
> > but until then, would really like to eliminate the white spaces and
> > hoped I might get some ideas here. As it currently stands, I have
> > approximately 50 rows with 15 'vendname' rows populated and a whole
> > bunch of white space that shows up on the pull down query.
> >
> > My code is:
> >
> > <?php
> > $hostName = "localhost";
> > $userName = "####";
> > $password = "########";
> > $dbName = "approvals";
> > $table = "approvals_tbl";
> >
> > mysql_connect($hostName, $userName, $password) or die("Unable to
> > connect to host $hostName");
> >
> > mysql_select_db($dbName) or die("Unable to select database $dbName");
> >
> > $query = "SELECT DISTINCT vendname
> > FROM $table";
> > $result = mysql_query($query);
> >
> > $number = mysql_numrows($result);
> >
> > for ($i=0; $i<$number; $i++) {
> > $vendname = mysql_result($result,$i,"vendname");
> > print "<option value=\"$vendname\">$vendname</option>";
> > }
> >
> > mysql_close();
> > ?>
> >
> > any is greatly appreciated.
> >
> > John
> >
>
> Try a MySQL newsgroup, such as comp.databases.mysql.
>
> trim() won't work becuase this is a PHP function, and therefore is
> processed BEFORE the MySQL call. You need a SQL function.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
>

On 2008-03-27, jcage@lycos.com <jcage@lycos.com> wrote:
> I have a table with a column named 'vendname'. This column isn't
> completely populated so as I query the column within the table to
> populate a pull down on a query page, there are many white spaces. My
> intent is to eventually have all of those empty table cells populated
> but until then, would really like to eliminate the white spaces and
> hoped I might get some ideas here. As it currently stands, I have
> approximately 50 rows with 15 'vendname' rows populated and a whole
> bunch of white space that shows up on the pull down query.
>
> My code is:
>
><?php
> $hostName = "localhost";
> $userName = "####";
> $password = "########";
> $dbName = "approvals";
> $table = "approvals_tbl";
>
> mysql_connect($hostName, $userName, $password) or die("Unable to
> connect to host $hostName");
>
> mysql_select_db($dbName) or die("Unable to select database $dbName");
>
> $query = "SELECT DISTINCT vendname
> FROM $table";
> $result = mysql_query($query);
>
> $number = mysql_numrows($result);
>
> for ($i=0; $i<$number; $i++) {
> $vendname = mysql_result($result,$i,"vendname");
> print "<option value=\"$vendname\">$vendname</option>";
> }
>
> mysql_close();
> ?>
>
> any is greatly appreciated.
>
> John

try you query as SELECT DISTINCT vendname FROM $table where
length(vendname) > 0

ken

On 2008-03-27, No_One <no_one@no_where.com> wrote:
>
> try you query as SELECT DISTINCT vendname FROM $table where
> length(vendname) > 0
>
> ken

Forget it, I misread your post....will not work unless you trim the field

my mistake

ken

jcage@lycos.com wrote:
> I have a table with a column named 'vendname'. This column isn't
> completely populated so as I query the column within the table to
> populate a pull down on a query page, there are many white spaces. My
> intent is to eventually have all of those empty table cells populated
> but until then, would really like to eliminate the white spaces and
> hoped I might get some ideas here. As it currently stands, I have
> approximately 50 rows with 15 'vendname' rows populated and a whole
> bunch of white space that shows up on the pull down query.
>
> My code is:
>
> <?php
> $hostName = "localhost";
> $userName = "####";
> $password = "########";
> $dbName = "approvals";
> $table = "approvals_tbl";
>
> mysql_connect($hostName, $userName, $password) or die("Unable to
> connect to host $hostName");
>
> mysql_select_db($dbName) or die("Unable to select database $dbName");
>
> $query = "SELECT DISTINCT vendname
> FROM $table";
> $result = mysql_query($query);
>
> $number = mysql_numrows($result);
>
> for ($i=0; $i<$number; $i++) {
> $vendname = mysql_result($result,$i,"vendname");
> print "<option value=\"$vendname\">$vendname</option>";
> }
>
> mysql_close();
> ?>
>
> any is greatly appreciated.
>
> John
>

Try a MySQL newsgroup, such as comp.databases.mysql.

trim() won't work becuase this is a PHP function, and therefore is
processed BEFORE the MySQL call. You need a SQL function.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================