[DAL]

Hi forum

I got problems. Ive already setup the database. I didnt go through any normalisation as I thought that since the data was so unique that slitting it into more than one table would just give me problems. I am now faced with query problems as running a query is quite complex. I have 20 fields. I have offered the visitor a search page which searches 7 of the 20. My first problem was the title search which explodes the words and sends them to the query string. I then need to add the other 6 search variables. Some are static like type which you can only select 1 from the list (or none for anything). My query string ends up being;

SELECT * FROM `events` WHERE title LIKE '%$value%' OR title LIKE '%$value%' OR title LIKE '%$value%' OR title LIKE '%$value%' OR title LIKE '%$value%' OR venue LIKE '%$value%' OR venue LIKE '%$value%' OR venue LIKE '%$value%' OR area LIKE '%$value%' OR area LIKE '%$value%' OR area LIKE '%$value%' OR area LIKE '%$value%' OR date = '$value' OR organiser LIKE '%$value%' OR organiser LIKE '%$value%' OR type = '$value' OR admission = '$value'

This is quite stupid and hard to work with. In an ideal world I would have just a name search but with dates and venues and such like envloved I need to narrow down the results.

After re-designing my tables (on paper) through a normalisation process I find that the query is going to be the same except I now would need to join the tables together in a query.

I guess my question is, how do I perform individual queries and join them effectivley so that I can use MYSQL's sort by functions. Ive already tried to write the results to a php 2dim array which has resulted in me losing more hair. I then couldnt get the commands to sort arrays out to work on a 2dim array. they seem to only want to work on a 1dim array.

Ive searched the web looked at my books and find Im really stuck. Can anyone please help.

Thanks

[DAL]

Ok - I think Im just confusing everyone including myself.

I guess its normal to have OR this OR that all over the place when you query MYSQL. So Ill just go with the flow. Just seemed that its BAD programming but then again this is the first time Ive delt with a relational db, Ive always just used arrays in my past experiences with other off line programming.

anyway I bunched all my queries together into one and ran it. Im a bit confused as to what to make of it.

example table

name --------- sex
---------------------
jack a ---------- man
jill z ------------ woman
jenny e --------- woman
---------------------

WHERE 'name' LIKE '%jack%' OR 'name' LIKE '%jill%' OR 'name' LIKE '%jenny%' AND 'sex' = 'man'

you should end up with
--------------------
jack a ---------- man
--------------------
because of the AND =

except I get
---------------------
jack a ---------- man
jill z ------------ woman
jenny e --------- woman
---------------------

then I noticed that I havent been putting --> ' <-single quotes around my field titles. Ive played around with this and can't find why the results change like they do. Can someone explain the difference.

WHERE name LIKE '%jack%' OR name LIKE '%jill%' OR name LIKE '%jenny%' AND sex = 'man'

WHERE 'name' LIKE '%jack%' OR 'name' LIKE '%jill%' OR 'name' LIKE '%jenny%' AND 'sex' = 'man'

[mab]

The AND operator has higher precedence than the OR operator. Therefore, it will be evaluated before the OR's will. To force your logic to do what you expect, enclose the terms in ( ) -

Code:

WHERE ('name' LIKE '%jack%' OR 'name' LIKE '%jill%' OR 'name' LIKE '%jenny%') AND 'sex' = 'man'

As to the quotes, table names and field/column names should not have quotes. Strings should have quotes. In your examples, 'name' LIKE ... should probably not be returning any thing, but it is acting like the WHERE clause is always true. In this example, 'name' is literally the string name and not the column name. The above should be -

Code:

WHERE (name LIKE '%jack%' OR name LIKE '%jill%' OR name LIKE '%jenny%') AND sex = 'man'

[mab]

For your title search/complex query, you might take a look at the full text search functions - http://dev.mysql.com/doc/refman/5.0/...xt-search.html