search 2 fields

[didgydont]

hi all i can easly search 2 fields but for a learning experince and a more accurate search im going to split my search query with preg split so if i was searching for

"b52's love shack"

it becomes

$search[0]->b52's

$search[1]->love

$search[2]->shack

 

now the problem is "b52's" is artist and "love shack" is song

so i do this

SELECT * FROM songlist WHERE artist like '%$search[0]%' and artist like '%$search[1]%' and artist like '%$search[2' OR song like '%$search[0]%' and song like '%$search[1]%' and song like '%$search[2]%' ORDER BY artist

 

 

it returns no results any ideas ?

 

thank you for your time

[fenway]

You don't have parens where you should have them.

[didgydont]

sorry 

i think the sql verion is MySQL client version: 4.1.22

i have tried this

mysql_query("SELECT * FROM songlist WHERE artist like '%$quicksearch%' OR song like '%$quicksearch%' ORDER BY artist");

and this

mysql_query("SELECT * FROM songlist WHERE * like '%$quicksearch%' ORDER BY artist");

data types are just tinytext

i havent bothered with preg split yet because it returns "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result"

 

does this help ?

[fenway]

Not really... now you've posted (1) a query that won't work with multiple keywords and (2) a query that's not valid at all.

 

My comment referred to your original query, mixing ANDs and ORs without parentheses.

[didgydont]

this would be the query that doesnt work but its the best way to explain what im trying to do

mysql_query("SELECT * FROM songlist WHERE * like '%$quicksearch%' ORDER BY artist");

but if that had worked then i would have been able to do this

mysql_query("SELECT * FROM songlist WHERE * like '%$quicksearch[0]%' AND * like '%$quicksearch[1]%' AND * like '%$quicksearch[2]%' ORDER BY artist");

i dont know how else to explain just say if there was 3 key words it would check

if word 1 is in artist or song

and if word 2 is in artist or song

and if word 3 is in artist or song

 

then display the song i can search 1 word or 1 phrase in the same order as the list by

mysql_query("SELECT * FROM songlist WHERE artist like '%$quicksearch%' OR song like '%$quicksearch%' ORDER BY artist");

but the problem is if the first word was from the artisit and the second word was from the song it would not work.

i realy cant think of any other ways to explain i hope it helps like i said it was just for learing perposes its not important but i would enjoy knowing the answer.

thanx for your help so far

 

[fenway]

Why not:

 

SELECT * FROM songlist

WHERE

artist like '%$quicksearch[0]%' OR artist like '%$quicksearch[1]%' OR artist like '%$quicksearch[2]%'

OR song like '%$quicksearch[0]%' OR song like '%$quicksearch[1]%' OR song like '%$quicksearch[2]%'

ORDER BY artist

 

Not that it's going to be particularly efficient.

[didgydont]

because that would not narrorow the results down if you searched for the phrase "love shack" it would find every song with the word "love" OR "shack" in it.

[fenway]

That's something very different.

 

First, you'll need to find any matching results, then make sure that all of the words you wanted are present.

[didgydont]

that would be easy because you could make the results 1 $value and check for the words if all there then display it but it would be a lot of code i just thought there may have been an SQL query for it. thats kool

thanx anyway