MySql Querying Multiple Columns.

Newbie needs help with databases

[Dayo_UK]

Still learning MYSQL so hope this question make sense :)

Trying to achieve a like function where the match is achieved if the data is contained in column1 and/or column2 but if the keyword is 2 or more words it looks at both columns not just the one (make sense - if not look at my examples)

If the $q variable is Blue Widgets and I use the following query I will get rows returned if Column1 contains Blue Widgets

Select * from $TableName where Column1 like '%$q%'

Same query on the below will return rows if Column1 and Column2 contain Blue Widgets

Select * from $TableName where Column1 like '%$q%' and Column2 like '%$q%'

Changing the and to an or will return rows if either contain the term Blue Widgets.

However, if Column1 contains Blue and Column2 contains Widgets how can I achieve this query? I assume (not sure) that I may need to Concat Column1 and Column2 - but cant get my head around it.

I hope this makes enough sense for someone to help me.

[brucec]

You have it right for the most part. My suggestion is to use PHP more because PHP and MySQL work very well together.

It may be better to do an IF loop to compare. You can still perform your MySQL statement and read the values from the database query, but then do an IF comparison with your PHP.

[Dayo_UK]

brucec

Thanks for your feedback.

Yep, the query is being run through PHP. I cant see how an If comparison would work here :(

[brucec]

I use the Concat function all the time and I don't think it would work in your case, because Concat will put two strings together from two columns, but you want to use it in a WHERE clause. I don't believe it will work, because the "blue" and "Widget" are in two different columns and your comparison would be on the wrong side of the equals sign.

Stick with a PHP IF conditional loop.

[brucec]

I think it will work. :) Let me see if I can do the PHP for you:

After your MySQL database connection, try this:

$WidgetQuery = mysql_query("SELECT * from TableName WHERE column1='blue' AND Column2='widget', $dbh) or die("Cannot query widgets!");
while($RecordSet=mysql_fetch_array($WidgetQuery)){
$col1 = $RecordSet["column1"];
$col2 = $RecordSet["column2"];
if ($col1=="blue" && $col2="widget") {
$MatchFound=true;
//PROCESS PHP HERE IF MATCH FOUND
}

}

This will work :)

[Dayo_UK]

brucec

Sorry - I probably did not explain myself clearly enough earlier.

The Blue Widgets was an example of the variable/string used of $q - which could be any combination of words - so specifying them as you have done above would not work unless I split the $q somehow (mmmmmmm - I wonder).

I have just found the following posted elsewhere on the Web and will give a go later (when I have access to my PC - work is slow this afternoon :))

I am not sure of the risks/benefits of using HAVING instead of WHERE though,

select *,CONCAT(Column1,Column2) AS alais1 FROM $TableName HAVING alais1 LIKE "%q%";

[coopster]

It's because this example uses an alias [mysql.com]. Standard SQL doesn't allow you to refer to an alias in a

WHERE

clause. This is because when the

WHERE

code is executed the column value may not yet be determined.

[brucec]

Oh ok, I understand. Coopster is right, You cannot use an alias in the Where clause, but you can try using a MySQL variable which is like an alias.

Use MySQL variables like this:

"SELECT @bluewidgets := CONCAT(column1,column2) FROM tablename @bluewidgets LIKE '%$q%' ";

[brucec]

So, the MySQL variable can be used in the where clause, but you cannot fetch it into a PHP array like you can with an alias.

[brucec]

Experiment, dude, just experiment :)

[coopster]

Something like this would work...

$q = 'blue widgets'; 
$sql = "SELECT * FROM $TableName WHERE '$q' IN(Column1, Column2, CONCAT_WS(' ', Column1, Column2))";

It says to return those rows where the exact string, 'blue widgets' is found in either Column 1, Column 2, or Column1 concatenated to Column2 with a blank space in between the two. However, I don't think it is going to give exactly what you want. It would only find exact matches. If your table contained something like this...

+----------------------+----------------------+ 
¦ Column1              ¦ Column2              ¦ 
+----------------------+----------------------+ 
¦ blue widgets         ¦ red                  ¦ 
¦ red                  ¦ blue widgets         ¦ 
¦ red                  ¦ red                  ¦ 
¦ blues                ¦ widget               ¦ 
¦ blue                 ¦ widget               ¦ 
¦ blue                 ¦ widgets              ¦ 
¦ widgets              ¦ blue                 ¦ 
¦ red and blue widgets ¦ blue and red widgets ¦ 
¦ red blue             ¦ widgets red          ¦ 
+----------------------+----------------------+ 

...it would only return the following rows...

+--------------+--------------+ 
¦ Column1      ¦ Column2      ¦ 
+--------------+--------------+ 
¦ blue widgets ¦ red          ¦ 
¦ red          ¦ blue widgets ¦ 
¦ blue         ¦ widgets      ¦ 
+--------------+--------------+ 

...which is probably not the desired result set, correct?

[Dayo_UK]

Thanks folks.

Not really there yet, but getting some ideas :)

[Dayo_UK]

Ok,

I have gone with Match Against to acheive what I want here - but I am still having problems with getting exactly what I want.

Using the Red, Blue Widgets example and the following rows.

Column 1, Column 2
Red Stuff, Blue Widgets
Red Stuff, Blue Bits
Blue Stuff, Blue Bits

and if I have code of:-

Select * from $Tablename Match (Column1, Column2) Against ('Red Widgets');

What I want to achieve here is the first row only to be returned (An exact match only) however with Match Against it will return both row 1 & 2 with row 1 having a higher relevance than row 2 - I am wanting an exact match as I will be ordering the row by another column eg (Price) and am only interested in returning exact matches otherwise row 2 could be higher than row 1 if the price is right (!)

Anyone have ideas on how to restrict Match Against to only 100% keyword match?

I have got a system in place but if I could acheive the above it would be ideal :)

[Dayo_UK]

Hey Guys, I think I have it, but need to test it (lol - why do I come to work when I have so much work to do at home)

Select * from $Tablename Match (Column1, Column2) Against ('+Red, +Widgets' IN BOOLEAN MODE);