I'm trying to populate the pulldown menus of a single input form
through queries from a single table. The three columns are 'name',
'type', and 'status'. The name table runs to approx row 23, the type,
to row 8, and status, row 9. . I'm using the following code to
populate the form pulldown menus::

<?php
$query = "SELECT DISTINCT status
FROM $table";
$result = mysql_query($query);
$number = mysql_numrows($result);

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

mysql_close();
?>


The interesting portion here is that 'name' only allows a single blank
space on the pulldown as I had expected and so does 'status'. 'Type'
on the other hand, shows 2 blank spaces on the pulldown menu so what's
with that? Is there some sort of 'end of file marker' that I should
be inserting to the database columns to get this to read correctly?
TIA

cover wrote:

> $query = "SELECT DISTINCT status
> FROM $table";

Most databases optimise GROUP BY better than DISTINCT. Generally speaking,
if you can avoid DISTINCT, you should: it forces the database engine to
retrieve rows and then discard them, whereas often, especially with
well-indexed columns, the engine will be able to do smarter things with
GROUP BY.

SELECT status FROM $table GROUP BY status;

> $result = mysql_query($query);
> $number = mysql_numrows($result);
>
> for ($i=0; $i<$number; $i++) {
> $status = mysql_result($result,$i,"status");

This is a fairly slow way to loop through rows. mysql_fetch_array is
normally faster and uses less code too.

$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
$status = $row[0];
// Do stuff here.
}

> print "<option value=\"$status\">$status</option>";

The value attribute here is redundant. When an option's label and value
are identical, you can leave out the value.

print "<option>$status</option>";

And if there's even the slightest chance that you'll need to use "special"
HTML characters like angled brackets and ampersands in status codes, then
use htmlentities() here.

printf("<option>%s</option>\n", htmlentities($status));

> The interesting portion here is that 'name' only allows a single blank
> space on the pulldown as I had expected and so does 'status'. 'Type'
> on the other hand, shows 2 blank spaces on the pulldown menu so what's
> with that? Is there some sort of 'end of file marker' that I should
> be inserting to the database columns to get this to read correctly?

It shouldn't give you any blank options in the select element unless there
are blank statuses in the database. Why would there be two? Perhaps one
blank represents a NULL in the database, and the other represents a
zero-length string -- SQL engines do not consider these the same, so will
return both. You can axe these blanks by including "WHERE NOT (status=''
OR status IS NULL)" in your query.

$query = "SELECT status "
. "FROM $table "
. "WHERE NOT (status IS NULL OR status='') "
. "GROUP BY status "
. "ORDER BY status;";
$result = mysql_query($query);
## If you really want a blank option, uncomment the next line.
## print "<option></option>\n";
while ($row = mysql_fetch_array($result))
{
printf("<option>%s</option>\n", htmlentities($row[0]));
}

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 35 days, 11:15.]

Cryptography Challenge
http://tobyinkster.co.uk/blog/2007/07/24/crypto-challenge/

cover wrote:

> $query = "SELECT DISTINCT status
> FROM $table";

Most databases optimise GROUP BY better than DISTINCT. Generally speaking,
if you can avoid DISTINCT, you should: it forces the database engine to
retrieve rows and then discard them, whereas often, especially with
well-indexed columns, the engine will be able to do smarter things with
GROUP BY.

SELECT status FROM $table GROUP BY status;

> $result = mysql_query($query);
> $number = mysql_numrows($result);
>
> for ($i=0; $i<$number; $i++) {
> $status = mysql_result($result,$i,"status");

This is a fairly slow way to loop through rows. mysql_fetch_array is
normally faster and uses less code too.

$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
$status = $row[0];
// Do stuff here.
}

> print "<option value=\"$status\">$status</option>";

The value attribute here is redundant. When an option's label and value
are identical, you can leave out the value.

print "<option>$status</option>";

And if there's even the slightest chance that you'll need to use "special"
HTML characters like angled brackets and ampersands in status codes, then
use htmlentities() here.

printf("<option>%s</option>\n", htmlentities($status));

> The interesting portion here is that 'name' only allows a single blank
> space on the pulldown as I had expected and so does 'status'. 'Type'
> on the other hand, shows 2 blank spaces on the pulldown menu so what's
> with that? Is there some sort of 'end of file marker' that I should
> be inserting to the database columns to get this to read correctly?

It shouldn't give you any blank options in the select element unless there
are blank statuses in the database. Why would there be two? Perhaps one
blank represents a NULL in the database, and the other represents a
zero-length string -- SQL engines do not consider these the same, so will
return both. You can axe these blanks by including "WHERE NOT (status=''
OR status IS NULL)" in your query.

$query = "SELECT status "
. "FROM $table "
. "WHERE NOT (status IS NULL OR status='') "
. "GROUP BY status "
. "ORDER BY status;";
$result = mysql_query($query);
## If you really want a blank option, uncomment the next line.
## print "<option></option>\n";
while ($row = mysql_fetch_array($result))
{
printf("<option>%s</option>\n", htmlentities($row[0]));
}

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 35 days, 11:15.]

Cryptography Challenge
http://tobyinkster.co.uk/blog/2007/07/24/crypto-challenge/

On 26.07.2007 09:51 Toby A Inkster wrote:
> cover wrote:
>
>> $query = "SELECT DISTINCT status
>> FROM $table";
>
> Most databases optimise GROUP BY better than DISTINCT. Generally speaking,
> if you can avoid DISTINCT, you should: it forces the database engine to
> retrieve rows and then discard them, whereas often, especially with
> well-indexed columns, the engine will be able to do smarter things with
> GROUP BY.

Don't know about "most" ones, mysql docs explicitly says they're equivalent

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization .html


--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

On Thu, 26 Jul 2007 08:51:22 +0100, Toby A Inkster
<usenet200707 [at] tobyinkster.co.uk> wrote:

<snip>

Hey, looks like some great ideas and I'll give them a try. Thank you
very much. :-)

gosha bine wrote:

> Don't know about "most" ones, mysql docs explicitly says they're equivalent
> http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization .html

It says that SELECT DISTINCT and GROUP BY *usually* end up generating the
same execution plan, so there won't be a performance difference.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 36 days, 13:07.]

Cryptography Challenge
http://tobyinkster.co.uk/blog/2007/07/24/crypto-challenge/

On 27.07.2007 11:46 Toby A Inkster wrote:
> gosha bine wrote:
>
>> Don't know about "most" ones, mysql docs explicitly says they're equivalent
>> http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization .html
>
> It says that SELECT DISTINCT and GROUP BY *usually* end up generating the
> same execution plan, so there won't be a performance difference.
>

Pretending that you cannot read is probably the worst defense you could
come up with. ;)

--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

On 27 Jul, 11:31, gosha bine <stereof... [at] gmail.com> wrote:
> On 27.07.2007 11:46 Toby A Inkster wrote:
>
> > gosha bine wrote:
>
> >> Don't know about "most" ones, mysql docs explicitly says they're equivalent
> >>http://dev.mysql.com/doc/refman/5.0/en/distinct-optimizati on.html
>
> > It says that SELECT DISTINCT and GROUP BY *usually* end up generating the
> > same execution plan, so there won't be a performance difference.
>
> Pretending that you cannot read is probably the worst defense you could
> come up with. ;)
>
> --
> gosha bine
>
> makrell ~http://www.tagarga.com/blok/makrell
> php done right ;)http://code.google.com/p/pihipi

But it is you who cannot read!

The manual does NOT (neither explicitly not implicitly) say that GROUP
BY and DISTINCT are equivalent.

What it does say is:

"In most cases, a DISTINCT clause can be considered as a special case
of GROUP BY."

And it then says:

"For example, the following two queries are equivalent: ..."

The phrase "In most cases" implies that they are not equivalent. If
they were it would say "In all cases".

On Thu, 26 Jul 2007 08:51:22 +0100, Toby A Inkster
<usenet200707 [at] tobyinkster.co.uk> wrote:

<snip>

Hey, looks like some great ideas and I'll give them a try. Thank you
very much. :-)