php search thru multiple tables

[LONGWAY]

Hi, I need to write a php code that search from multiple tables and here is the problem:

$query = "SELECT * FROM table1, table2, table3, table4 WHERE $kategorija LIKE '%".$kriterium."%'";

The results that were given were multiplicated (hundreds copies of the same data).

I am new and can somebody help?

The question is why the results are multiplicated???

[rpk2006]

Your question is not very clear but I think you can use DISTINCT clause like this:

Select DISTINCT * From <tables.............> WHERE ................

[LONGWAY]

Your question is not very clear but I think you can use DISTINCT clause like this:

Select DISTINCT * From <tables.............> WHERE ................

thanks, I tried but the result is the same

the problem is when I'll write a name, it will find it and will display it 125, 250 or much more times than it should. If there is 1 matcing result it will display 125 multiplications, for 2 - 250 multiplications, for 5 - 625 etc.

[bruceoz]

How are the four tables related?
Are there primary keys / foreign key relationships between the tables.

[rpk2006]

For query to work, you need to provide more details. How many tables are there, what column you want to pick and what are the primary and foreign keys.

[LONGWAY]

I use a list of 5 categories and one text field for the criteria.
The tables have 10 columns and the data is not repeating only in the first.
I have 4 tables, all have primary key and the other 3 tables are related to the first
But whatever I change in the relations the result is the same, multiplicated results and for every new row of data I'll enter the number of the multiplicatios is incrreased.
As a result I want to be displaied all columns from all tables( all 10 columns but for the row that I entered as a criteria)in one big table.

[rpk2006]

You need to use a JOIN of any type along with the DISTINCT to prevent duplication. Since you have not submitted the table structure, I would assume:

Suppose your first primary table's primary column is: FID. All the other tables are linked to this column of the first table.

So you need to modify your query somewhat like this:

$query = "SELECT * FROM table1, table2, table3, table4 WHERE $kategorija LIKE '%".$kriterium."%'" AND table1.FID = table2.FID AND table1.FID = table3.FID AND table1.FID = table4.FID;

[LONGWAY]

Thanks a lot, this was the answer :)

$query = "SELECT * FROM table1, table2, table3, table4 WHERE $kategorija LIKE '%".$kriterium."%'" AND table1.FID = table2.FID AND table1.FID = table3.FID AND table1.FID = table4.FID;

[rpk2006]

Don't forget to mark as SOLVED.