search

[yebaws]

need some advice on the best way to make a search page in php for a mysql database.

The database contains 3 columns with CSV's. The CSV's are all taken from a set list of about 20 different values.

I want users to be able to search for each row for different values using dropdowns or checkboxes.

For example, for row 1:
column a = "apples, pears, oranges"
column b = "red, black, green"
column c = "wood, stone, water"

so a user might want to find a row with columns a, b or c containing ALL of apples, oranges, red and stone **OR** ANY of apples, oranges, red and stone (preferably in the latter case sorted by the number of matched occurrences).

I'm familiar with the syntax for a basic search, but can't put together a query for this that does what I want it to.

Thank you in advance....

 

[preachie]

Although I'm sure this is not the answer you want to read, but the best solution would be, if you would normalize the tables in your MySQL DB (

http://en.wikipedia.org/wiki/Database_normalization)

It is not impossible to do what you want to do with the current scenario, but for me, I never would suggest to keep this model.

Why do you need multiple values, comma separated within one DB column?

 

[jpadie]

ouch. this is typically the reason that people move from flat file (csv) to a structured data format.

but it can be done if you don't much care about performance.

but we need a bit more information too, before we can help:

you have given a data snip. does this mean that your actual csv looks like this for the first line

---begin csv---
"apples,pears,oranges","red,black,green","wood,stone,water"
---end csv---

if so can you provide us with an actual excerpt of data so that we can determine the relationships? or if not can you explain the relationships between the columns and the reasons for grouping information items in a single value? (e.g. "apples,pears,oranges" is a single value)?

in your post you want help with the syntax for the search. and then use the word "query". i am not aware of a SQL or other query language for csv files. there may be one that i don't know of, of course. IMO we will have to approach this programmatically.

i am assuming that you are able to build the cascade of dropdowns etc. and are comfortable with adding AND/OR radio buttons.

if you want to make things more complex and allow sub-queries you will need to add, also, a sub-query checkbox between each item. once you have done this please post your form code so that we can use the actual design to show you how to structure your query code.

 

[jpadie]

one other thought. could you also run the following and post back the output in addition to the questions above:

<?php
print_r(PDO::getAvailableDrivers());
?>

 

[yebaws]

yes, I realise that the CSV,s are making things more complicated and that changing the structure of the database will be the best solution in the long term, but that will involve a *lot* of work.

I think I'm getting there with check boxes, but won't post anything yet as it's still in progress.

But I've come up against another problem using searches of fulltext indexes....

One column has a CSV list of 2, 3 & 4 character initials - for example: "BL, BT, GL, IWM, NMM, NRA, TNA,". I want to be able to search for rows that contain ANY particular initials.

For example, I can build a string "BL, GL, TNA" with check boxes to search for all rows containing any of those initials. But mysql only indexes words of 4 or more characters. I've emailed my ISP to see if they can change the configuration to allow indexing of 2 character words, but I'm guessing that the answer will be "no" as it will probably require a server wide change. So is there a way around this?

Using LIKE won't work because a LIKE search for "BL, BT, GL, IWM, NMM, NRA, TNA," will only return a row that has exactly that string, and not one that just contains BL for example. I'm a bit stumped with this one.....

 

[vacunita]

You can use wildcards with LIKE statements. Denoted by the percetn character %

So if you are looking for "BT" or initials that start with N you can use the wild cards to help you.

... LIKE '%BT%' ...

BT can occur anywhere in the field.

... LIKE 'N%' ...

The field starts with an N.


etc..

You are still limited to searching for a single string, but at least it doesn't matter where in your field it occurs.

With that you can use PHP to loop through the options you eed to search for and create a query like:

... WHERE myfield LIKE '%[green]$string_one[/green]%' OR LIKE '%[green]$string_two[/green]%' OR LIKE '%[green]$string_three[/green]%' ...

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

@OP,

could you take another look at my posts and provide some answers? we can certainly solve the problems without databases but need the information I asked for.

 

[vacunita]

Actually Jpadie I believe he is using a database. but the fields are set as comma separated values so a field in the DB contains more than one value separated by a comma.

This is very poor DB design to say the least, but he's said he can't really alter it now.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[yebaws]

I know - the database should be normalised, but I didn't write it, so please give me a break....

It would take forever to do it and I need a quick fix for now. And I've found it: FIND_IN_SET is the function I'm after.

 

[jpadie]

@vacunita

oh, i'd understood the term database to be used loosely given the reference to csv.

there are a number of bug-reports for find_in_set so if you are getting unanticipated results do check the bug-reports out before spending hours trying to debug.