[Resolved] Query first three digits of a string....

sgtpepper

Ok, here we go 🙂

I have a database that contains a field with 255 characters. The first three of these characters represents a 'type' of real estate property....for example

A06B03C01D14E11G11H09F03I10K07N06Q07L11

This represents a storefront....because A06 means 'Storefront'

Dont ask me why someone setup the database like this...but they did.

I am creating a form that has a pulldown for 'property type'. The values for each type I have set as the corresponding number.

For example:
<select multiple name="btype" size="6">
<option value="A06">Storefront</option>
</select>

Now, I am not sure how to setup my query to look for just the first three digits.

I tried

select features from database where features = '$btype';

But that produces no results...how should I setup my query?

pyro

Try this:

$sql = "SELECT `features` FROM `database` WHERE `features` LIKE = '$btype%';"

sgtpepper

My full query is this:

$numresults = mysql_query("SELECT town, list_price, features, IF (lister_office_name like '$client_name', 1, 0)
AS featured
FROM commercial
WHERE ( $selecttownstring )
WHERE features LIKE $btype%
AND list_price BETWEEN $lowprice AND $highprice
ORDER BY featured DESC, list_price DESC
"); // the query.

But, I get the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Any ideas on what is wrong?

stolzyboy

have you put an

or die(mysql_error());

after your mysql_query() part to see where mysql isn't liking the query and/or results

mrhappiness

you use WHERE twice which is one to often

btw: LEFT(columnname, 3) should be faster than LIKE

sgtpepper

This is what fixed it...

$numresults = mysql_query("SELECT town, list_price, features, IF (lister_office_name like '$client_name', 1, 0)
AS featured
FROM commercial
WHERE ( $selecttownstring )
AND list_price BETWEEN $lowprice AND $highprice
AND features LIKE '$btype%'
ORDER BY featured DESC, list_price DESC
"); // the query.