1.

Solve : Building an efficent sql text search method.?

Answer»

I have a table with about 15000 records.

And it looks like this:
Code: [Select]# Column Type
1 Model int(11)
2 Name varchar(64)
3 Comment varchar(64)
4 CATEGORY varchar(64)
5 MTA varchar(64)
6 IDE varchar(64)
The first column is indexed as unique.

I need an efficient method of searching using the user input from e.g. a php textbox in "columnX" where "ocweeg" would actually match "procweegrs".

Also the number of records that match needs some limits, e.g:
Quote

* Results Page 1: "LIMIT 0, 30"
* Results Page 2: "LIMIT 20, 30"
* Results Page 3: "LIMIT 40, 30"
And so on...


So what would be a proper and efficient way to do this?Quote
I need an efficient method of searching using the user input from e.g. a php textbox in "columnX" where "ocweeg" would actually match "procweegrs"

You can use the LIKE CLAUSE and wildcards. This might help.

Quote
Also the number of records that match needs some limits

The LIMIT clause is vendor specific and not a standard SQL clause. What database are you using?

Quote from: Sidewinder on August 24, 2012, 02:50:48 PM
You can use the LIKE clause and wildcards. This might help.

The LIMIT clause is vendor specific and not a standard SQL clause. What database are you using?



I'm using InnoDB. And i will take a look at that tomorrowAfter some trial and error i came up with something that should have worked;

Code: [Select]SELECT *
FROM `objects`
WHERE 'Model' LIKE '%plant%'
OR 'Name' LIKE '%plant%'
OR 'Comment' LIKE '%plant%'
OR 'Category' LIKE '%plant%'
OR 'MTA' LIKE '%plant%'
OR 'IDE' LIKE '%plant%'
LIMIT 0 , 21
Unfortunately i get this response:
Quote
MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec )
APPARENTLY the message indicates the SQL is fine, you simply did not select any records. Have you tried limiting the scope of the SQL? Something like this:

Code: [Select]SELECT *
FROM `objects`
WHERE 'Model' LIKE '%plant%'
LIMIT 0 , 21

Showing us some of the OBJECTS table might also help. Question: why the back ticks wrapping the table name?



Sorry Sidewinder, but it all works now

This is the new syntax:

Code: [Select]SELECT *
FROM objects
WHERE Model LIKE '%plant%'
OR Name LIKE '%plant%'
OR Comment LIKE '%plant%'
OR Category LIKE '%plant%'
OR MTA LIKE '%plant%'
OR IDE LIKE '%plant%'
LIMIT 0 , 21
I don't know why but apparently MySQL did not like that i encased field names or the table name.


Discussion

No Comment Found

Related InterviewSolutions