Write IF condition in Query , and mysql Search function

--00504502ad208786bb0471a11033
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi All ,

Am working on the autosuggestion program ....

SELECT $field2,$field1 FROM $tblname where locate('$q', $field2) > 0 OR
locate('$q', $field1) > 0 order by locate('$q', $field2),

$field2 limit " . ($pagesize * $page) . "," . $pagesize


This is my query ,
Example .... in my Database table .. am having values somthing like

Bombay(BOB),Delhi(DI),Gujarath(GA),Rajasthan(RA),Baroda(BD)

My present act like ...

If user enter the " d "

then It show the baroda(BD) as first row and Delhi as second result ....

So can u please guide me here

Also for clear vision please go this link

http://ukatn.com/index_autosuggest.php

Select the Postal code in the Taxi From combo...

Then enter the l as keyword . then u will find the first are start with A
not an L ,

So my expectation is ,

Assume if user enter the L then i want the first result as start with L row
as out ... and if they enter LI then result must lilliput somthing like
that....

Am fething columns are postcodename and postcodeCODE ......

IIn the search, the first preference must be field *postcodename *if no
keyword match in the first column then go second column *postcodeCODE *
......

Can u please tell me How to write query for this siutaion....

Thanks

--00504502ad208786bb0471a11033--

Re: Write IF condition in Query , and mysql Search function

bharani kumar wrote:
> Hi All ,
>
> Am working on the autosuggestion program ....
>
> SELECT $field2,$field1 FROM $tblname where locate('$q', $field2) > 0 OR
> locate('$q', $field1) > 0 order by locate('$q', $field2),
>
> $field2 limit " . ($pagesize * $page) . "," . $pagesize
>
>
> This is my query ,
> Example .... in my Database table .. am having values somthing like
>
> Bombay(BOB),Delhi(DI),Gujarath(GA),Rajasthan(RA),Baroda(BD)
>
> My present act like ...
>
> If user enter the " d "
>
> then It show the baroda(BD) as first row and Delhi as second result ....
>
> So can u please guide me here
>
> Also for clear vision please go this link
>
> http://ukatn.com/index_autosuggest.php
>
> Select the Postal code in the Taxi From combo...
>
> Then enter the l as keyword . then u will find the first are start with A
> not an L ,
>
> So my expectation is ,
>
> Assume if user enter the L then i want the first result as start with L row
> as out ... and if they enter LI then result must lilliput somthing like
> that....
>
> Am fething columns are postcodename and postcodeCODE ......
>
> IIn the search, the first preference must be field *postcodename *if no
> keyword match in the first column then go second column *postcodeCODE *
> .....
>
> Can u please tell me How to write query for this siutaion....
>
> Thanks
>

The query is hard to write because you are not leveraging the design
qualities of a Relational Database. MySQL does not index every term of a
list of values that are stored within a single field. It indexes the
entire field.

MySQL (or any other RDBMS) can index a list of values stored
one-at-a-time in separate rows of data. This is called "normalization"
and it can not only improve your search performance but it can also
reduce your storage footprint.

I suggest you modify how your data is stored by implementing the
techniques of normalization. I think it will help get you started.

--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org