PHP and My SQL

[KloD]

Anyone good with MySQL? I'm writing a web application and in it I have a form that allows the users to submit text and files to a MySQL database. The issue I'm having is w/ the search engine. I'm able to search for one word from one column. ("select * FROM table WHERE column like \"%$trimmed%\" order by created_at DESC" ;

However, I need to both search all columns of one row for multiple words, if any of the words appear in the row, it will display a result.

ex.

| column 1 | column 2 | column 3
--------------------------------
| test | play | set
| only | test | other
| pass | set | new

user puts 'test, set' it would echo rows 1 and 3.

Then I need to drill down and get reults for (test AND set).
It would echo back only row 1.

These are two seperate searches (basic and advanced) and have their own PHP pages.

Can anyone help?

Thanks!

 

[jw7]

Anyone good with MySQL? I'm writing a web application and in it I have a form that allows the users to submit text and files to a MySQL database. The issue I'm having is w/ the search engine. I'm able to search for one word from one column. ("select * FROM table WHERE column like \"%$trimmed%\" order by created_at DESC" ;

However, I need to both search all columns of one row for multiple words, if any of the words appear in the row, it will display a result.

ex.

| column 1 | column 2 | column 3
--------------------------------
| test | play | set
| only | test | other
| pass | set | new

user puts 'test, set' it would echo rows 1 and 3.

Then I need to drill down and get reults for (test AND set).
It would echo back only row 1.

These are two seperate searches (basic and advanced) and have their own PHP pages.

Can anyone help?

Thanks!

Not a MySQL expert, but I do work with SQL enough to be dangerous.

I would start with the MySQL Reference and look at the String Functions.

Multiple column logic is difficult so maybe try this.

One way you could do it is to add a column 4 to your table that concatenates columns 1-3, maybe with a separator (for example "-").

| column 1 | column 2 | column 3 | column 4
--------------------------------
| test | play | set | test-play-set-

Let's call "test" arg1, and "set" arg2. Then search for the like substring in column 4 with an OR for arg1 or arg2 with a hyphen tacked on.

I don't know the mysql syntax but in English something like select column1, column2, column3 from table where column4 like %(arg1 + "-")% or column4 like %(arg2 + "-")%.

Just throwing spaghetti against the wall.

 

[Sandan]

I know way more SQL than enough to be dangerous Been an Oracle DBA for 20 years

There are any number of ways to do that but depending on the size of the data and the performance required there are some possibilities.

I would not recommend adding a column, this is called denormalization and for the most part is a bad thing. Why is a longer discussion and developers rail against this all time, for now trust me its a bad idea.

Can I assume you have a valid primary key on these rows ? Please say you do .

The correct way to do this is to have a where clause for each column them all together with OR

like [leaving the string processing off and not syntaticly correct]

where
col1 = %parameter OR
col2 = %parameter OR
etc
etc

For each column

If you don't have a predefined list of columns you will need to hit the metadata and construct the sql statement dynamicly

 

[Sandan]

If you need to use the result of this query to seed another one that [providing MySql supports it] you probably need a correlated sub query.

Something like

select pkey, col1, ....
from table1 t1
where exists
(select pkey from table1 t2
where t2.pkey = p1.pkek and
(col1 like '%parameter%' OR
col2 like '%parameter%' OR
...
....))

Typically I have to mess around with queries for a while to get them to return what I need

 

[KloD]

Thanks to both of you for your responses! I've been super busy and when I tried to access the site over the weekend it was down.

I did though get jw7's reply in an email and thought it was a great suggestion. I thought I'd look into a little more and found that folks recommended against it. So I held off and did some more reading.

Then I found a suggestion on some website to do the WHERE part of the query as an OR statement with each column on its own as Niden suggests. It worked perfect. So I have my basic search using OR and my advanced search using AND. Now all I have left is to rework my PHP code to allow for more than one word separated by a ','.

I've been writing software for a long time, but writing web apps is a little new to me. Same concept in general so I've picked it up quickly.

Anyway, that tool of one of my future websites is pretty much done. Thanks for your input!

 

[CaptTurbo]

I know way more SQL than enough to be dangerous Been an Oracle DBA for 20 years

Ha! I found out why we disagree so much. I am a SQL Server DBA at the moment. We make fun of Oracle DBA's and I am sure its visa versa as well. lol

Also hate, HATE SUN

 

[Sandan]

Not really.

Having been a DBA for a long time, I've also worked on SQL Server. Nothing wrong with it except it brings windows baggage, ie stuff you don't need to run a DB server. So pound for pound is a little slower but frankly for 90% of applications that doesn't matter.

The only place where Sql Server falls on its face is in fault tolerance. Federated databases are a very poor subsitute for Oracle RAC [DB is in the middle, better than SS not as good as Oracle]. If you don't need that fault tolerance the differences are minor.

 

[Sandan]

Thanks to both of you for your responses! I've been super busy and when I tried to access the site over the weekend it was down.

I did though get jw7's reply in an email and thought it was a great suggestion. I thought I'd look into a little more and found that folks recommended against it. So I held off and did some more reading.

Then I found a suggestion on some website to do the WHERE part of the query as an OR statement with each column on its own as Niden suggests. It worked perfect. So I have my basic search using OR and my advanced search using AND. Now all I have left is to rework my PHP code to allow for more than one word separated by a ','.

I've been writing software for a long time, but writing web apps is a little new to me. Same concept in general so I've picked it up quickly.

Anyway, that tool of one of my future websites is pretty much done. Thanks for your input!

takes a bow

 

[Gaddabout]

What kind of security do you put into this code? Minus a log-in function, at least check the mime-type and file extension. Simple enough, though lots of loopholes. Set permissions on that folder to disallow executable files. You can do this through chmod. Still might get something bad through, but at least you've mostly contained it.