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?