Posted by Toby A Inkster on 07/26/07 07:51

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/

[Back to original message]