mysql relational database problem, NEED HELP

[MDanz]

the following should return 1 row

$search= "Timothy";

$query4 = mysql_query("SELECT * FROM test1, test2, combination WHERE test1.IDONE = `combination`.IDONE AND test2.IDTWO = combination.IDTWO AND test1.NAME LIKE '%$search%'",$this->connect) or die(mysql_error());

instead it returns zero.

the query should take values from all three tables according to the where clause.

in short to describe the table structure.

3 tables(test1, test2, combination)

test1 has primary key IDONE, test2 has primary key IDTWO

combination looks like this

CREATE TABLE `combination` (
IDONE int(8) NOT NULL,
IDTWO varchar(11) NOT NULL,
INFO char(200) NOT NULL,

INDEX (IDONE, IDTWO),
PRIMARY KEY (IDONE,IDTWO),
FOREIGN KEY (IDTWO) REFERENCES `test2` (IDTWO)  ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (IDONE) REFERENCES `test1` (IDONE) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;

i havent done the relational database wrong.. there is no mysql_errors either. any idea?

[smantscheff]

I don't see any errors in the structure, so presumably it's in the data. Please show them.

[drjohn]

Are you sure about these quotes around combination? I've never done that when using sql.

WHERE test1.IDONE = `combination`.IDONE

[griswolf]

@drjohn. Those back quotes around table or column names are legal (and often a good idea). I tend not to do it that way because I'm a lazy typist. I'd have written

"SELECT * FROM test1 as t1, test2 as t2, combination as c WHERE t1.IDONE = c.IDONE AND t2.IDTWO = c.IDTWO AND t1.NAME LIKE '%$search%'"

add:
In fact, I'd use an explicit join:

"SELECT * FROM test1 as t1 join test2 as t2 join combination as c on t1.IDONE = c.IDONE AND t2.IDTWO = c.IDTWO where t1.NAME LIKE '%$search%'"