query by first letter of word???

Anon

I\'m trying to query the first letter of a column.

$query = mysql_query(\"SELECT * FROM products WHERE product_description >= \'$letter[0]%\' AND product_description <= \'$letter[1]%\'\");

For example if $letter[0]%=a and $letter[1]%=c then I want to return everything beginning with a, b, and c.

Then problem i\'m having is it\'s only returning things beginning with A and B.

Anyone have any ideas how to get C included in the output?

reagent

try using regexp instead, it makes more sense.

SELECT *
FROM products
WHERE
product_description REGEXP '^{[".$letter[0]."-".$letter[1]."]'}

I haven't tested this, but play w/ it and see if you can't get it to work..

reagent

I neglected to remember that REGEXP IS case sensitive.. so try this code snippet:

$strSearch = strtoupper($letter[0].'-'.$letter[1]).
$letter[0].'-'.$letter[1];

$qsvGetProduct = "
SELECT *
FROM products
WHERE product_description
REGEXP '^{[$strSearch]'}
ORDER BY product_description ASC";

i tested this regexp, and it works..

good luck.
p.

Anon

Thank you so much. It works great.

Could you take a quick look at the following code and give me your thoughts on the efficientness of it:

Thanks again.

//<?php

$db = mysql_connect()
or die ("Could not connect");

mysql_select_db("udllabs")
or die ("Could not select database");

$letter = split("-", $alpha);

$letter[0]; // first letter
$letter[1]; // second letter

$strSearch = strtoupper($letter[0].'-'.$letter[1]).$letter[0].'-'.$letter[1];

$query = mysql_query("SELECT * FROM UDLUD WHERE product_description REGEXP '^{[$strSearch]'} ORDER BY product_description ASC");

echo "<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\">\n";
echo "<tr>\n";
echo "<td style=\"background-color: #000;\"><table cellpadding=\"2\" cellspacing=\"1\" border=\"0\">\n";
echo "<tr>\n";
echo "<th>NDC#</th>\n";
echo "<th>Product Description</th>\n";
echo "<th>Package Size</th>\n";
echo "<th>Compare To</th>\n";
echo "</tr>\n";

{
while($row = mysql_fetch_array($query))

printf("
<tr valign=\"top\">
<td class=\"data\" nowrap>%s</td>
<td class=\"data\">%s</td>
<td class=\"data\">%s</td>
<td class=\"data\">%s</td>
</tr>\n", $row["ndc"], $row["product_description"], $row["size"], $row["compare"]);

}
echo "</table>\n";

//?>

reagent

I'm assuming that $alpha is coming from a get or post var..

first check to see if it's valid

if (!preg_match('/^\w-\w$/', $alpha))
{
//invalid input
}

then you can just do:
$strSearch = strtoupper($alpha).$alpha;

since you don't really need to split and then join..

the only other thing, and this is personal preference, is that i jump out of php mode to make things a little more readable:

<? while($row = mysql_fetch_array($query)) { ?>

<tr valign="top">
<td class="data" nowrap><?= $row['ndc'] ?></td>
...

Anon

Does it slow down processing by opening and closing php so many times?

Anon

Actually i'm not using get or post. I'm link to a page like this:

product.php?alpha=d-g
So that's where the alpha comes from. So i've changed to this:

<?php

$db = mysql_connect()
or die ("Could not connect");

mysql_select_db("udllabs")
or die ("Could not select database");

$letter = split("-", $alpha);

$letter[0]; // first letter
$letter[1]; // second letter

$strSearch = strtoupper($alpha).$alpha;

$query = mysql_query("SELECT * FROM products WHERE product_description REGEXP '^{[$strSearch]'} ORDER BY product_description ASC");

while($row = mysql_fetch_array($query))
{

<?php
}
?>

reagent

why would you do it that way?
in effect you're just splitting and joining it, which is confusing, and unnecessary. you don't even use the $letter array do you?

this url:
product.php?alpha=d-g

actually passes in a GET var (you specify these as part of the url) and can be accessed as part of the $HTTP_GET_VARS array as well.

Anon

So you saying that i can change this:

$db = mysql_connect()
or die ("Could not connect");

mysql_select_db("udllabs")
or die ("Could not select database");

$letter = split("-", $alpha);

$letter[0]; // first letter
$letter[1]; // second letter

$strSearch = strtoupper($alpha).$alpha;

$query = mysql_query("SELECT * FROM products WHERE UPPER(code) = 'O' AND product_description REGEXP '^{[$strSearch]'} ORDER BY product_description ASC");

while($row = mysql_fetch_array($query)) {

To this:

<?php

$db = mysql_connect()
or die ("Could not connect");

mysql_select_db("udllabs")
or die ("Could not select database");

$strSearch = strtoupper($alpha).$alpha;

$query = mysql_query("SELECT * FROM products WHERE UPPER(code) = 'O' AND product_description REGEXP '^{[$strSearch]'} ORDER BY product_description ASC");

while($row = mysql_fetch_array($query)) {

????

Thanks

reagent

right. I added this:

if (!preg_match('/^\w-\w$/', $alpha))
{
//invalid input
}

to make sure the ?alpha= value you supply is actually a valid range