Hi
I have inherited a db with a bunch of tables which all need to be keyword-searched from a webform.
The main problem is that there is no unique field which can be used to relate the tables, so my SQL statement keeps failing.
There is one field which unites each of the tables, but many records share the same value. Is it the lack of a unique index field that's stopping MySQL from performing this select?
It is not a case where 1 record on table1 identifies 10 records on table 2, and 6 records on table 3 etc. All the records in all of the tables hold individual content, but all are listed as status=A.
I'm quite lost - I've posted my SQL statement below if anyone can spot why things aren't working. BTW would a separate index table help things out?
$sql = "SELECT * FROM table1, table2 WHERE "
$sql.= "(table1.headline LIKE '%$keyword%' OR table1.body LIKE '%$keyword%' OR";
$sql.= "table2.headline LIKE '%$keyword%' OR table2.body LIKE '%$keyword%') ";
$sql.= "AND status='A' ORDER BY table1.time, table2.time DESC";
This is for two tables - ultimately I'll have to search 8.
Thanks in advance
I have inherited a db with a bunch of tables which all need to be keyword-searched from a webform.
The main problem is that there is no unique field which can be used to relate the tables, so my SQL statement keeps failing.
There is one field which unites each of the tables, but many records share the same value. Is it the lack of a unique index field that's stopping MySQL from performing this select?
It is not a case where 1 record on table1 identifies 10 records on table 2, and 6 records on table 3 etc. All the records in all of the tables hold individual content, but all are listed as status=A.
I'm quite lost - I've posted my SQL statement below if anyone can spot why things aren't working. BTW would a separate index table help things out?
$sql = "SELECT * FROM table1, table2 WHERE "
$sql.= "(table1.headline LIKE '%$keyword%' OR table1.body LIKE '%$keyword%' OR";
$sql.= "table2.headline LIKE '%$keyword%' OR table2.body LIKE '%$keyword%') ";
$sql.= "AND status='A' ORDER BY table1.time, table2.time DESC";
This is for two tables - ultimately I'll have to search 8.
Thanks in advance