Mysql Select combined tables/fields

I have 2 tables in the database

BRAND
- id
- name

PRODUCT
- id
- brand_id
- name

The site user will search for a product using only one text field.

Example 1: "sony dvd player" where "sony" is in the BRAND table and "dvd player" is in the PRODUCT table.

Example 2: "car audio pioneer deh-1234", in this case "car audio deh-1234" belongs to PRODUCT and pioneer to BRAND.

It's there any way to write a unique SQL query searching for results?

Something like this:

SELECT
   B.name,P.name
FROM
   brand B,product P 
WHERE
   B.id=P.brand_id 
   AND (P.nombre LIKE '%{$textfield}%' OR B开发者_JS百科.name LIKE '%{textfield}%')

Thanks for any help. :)

Edit: The SQL above doesn't work for combined brand/product, but for a product OR a brand. I edited the variable name used to $textfield since it is from one single text input.

The reason why it doesn't work is that you have both brand and product information in the string. So, if you search for, say, "sony dvd player" the query searches for "%sony dvd player%" within the two tables. But probably there is no such entry (neither in brand nor in product).

You should split the search string instead and search for parts "separately". For example:

P.name LIKE '%sony%' OR p.name LIKE '%dvd%' OR p.name LIKE '%player%' OR b.name LIKE ...

The expression has to be generated dynamically. You could then sort the result by some "probability" value to have those rows in the first places that have a higher match.

If you reconsider what parts are done in PHP, and which in SQL, this is pretty easy

--PHP--

$t = $_POST('textField1');
$tArr = explode(' ', $t);
$lastClause = '';
foreach ($tArr as $k=>$v)
{
    if ($k != 0) $lastClause .= ' OR ';
    $lastClause .= " B.name LIKE '%$v%' OR P.name LIKE '%$v%' ";
}


$sql = "SELECT B.name,P.name
       FROM
       brand B,product P
       WHERE
       B.id=P.brand_id 
       AND ($lastClause)";

mysql_query($sql, $conn);

I'd also recommend you change your query to use an INNER JOIN, but thats another matter.

As far as the sorting, if that is important to you. I'd recommend putting ORDER BY on a the SUM of a CASE on each separate piece of lastClause, where match -> 1, no-match -> 0

@gustyaquino: Your code should work, just needs a small update --

SELECT
   B.name,P.name
FROM
   brand B,product P 
WHERE
   B.id = P.brand_id 
   AND (P.name LIKE '%{$textfield}%' OR B.name LIKE '%{textfield}%')

Replacing nombre with name, señor ;-)

Update

@hol: I tested the SQL code in a shell and it worked. I only noticed gustyaquino's edit after I had already answered and came back to see you jumping on me and down-voting me.

mysql> create database so4432028;
Query OK, 1 row affected (0.16 sec)

mysql> use so4432028;
Database changed

mysql> create table brand (id int not null auto_increment, 
name varchar(20) not null default '', 
primary key(`id`));
Query OK, 0 rows affected (0.34 sec)

mysql> create table product (id int not null 
auto_increment, name varchar(40) not null default '', 
brand_id int not null, primary key(`id`), 
key brand (`brand_id`));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into brand (name) values ('Sony');
Query OK, 1 row affected (0.08 sec)

mysql> insert into brand (name) values ('LG');
Query OK, 1 row affected (0.02 sec)

mysql> insert into brand (name) values ('Panasonic');
Query OK, 1 row affected (0.01 sec)

mysql> insert into product (name, brand_id) values ('DVD Player', 1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into product (name, brand_id) values ('Plasma Television', 2);
Query OK, 1 row affected (0.06 sec)

mysql> insert into product (name, brand_id) values ('CD Player', 3);
Query OK, 1 row affected (0.03 sec)

mysql> select p.name, b.name from brand b, product p where b.id = p.brand_id and
 (p.name like '%Sony%' OR b.name LIKE '%Sony%');
+------------+------+
| name       | name |
+------------+------+
| DVD Player | Sony |
+------------+------+
1 row in set (0.09 sec)