CODEWITHSUNDEEP
phpmysql
user2444990
user2444990

Reputation: 1

select * from two tables

I'm building php search box and this is my query

SELECT * FROM table1, table2 WHERE table1.title AND table2.posts LIKE '$query%' LIMIT 10

after the query the code is

if($query) {
while ($result = $query ->fetch_object()) {
echo '<li onClick="window.open(\''.$result->guid.'\');">'.$result->post_title.'</li><br>';
            }
        } else {
            echo 'ERROR: There was a problem with the query.';
        }

And it does not fetch anything. I think it's something from the query. Please help..

Upvotes: 0

Views: 86

Answers (5)

rivimey
rivimey

Reputation: 931

Suhail Patel is correct but not complete, probably.

Depending on your table definitions SELECT * FROM table1, table2 WHERE table1.name LIKE x AND table2.name LIKE y;

will return all possible combinations of the fields selected from each table. So if table1 contains the following rows that satisfy LIKE:

1
2
3

and table2 contains these rows that satisfy LIKE:

6
7
8

then your result will contain 3x3 = 9 answers:

1, 6
1, 7
1, 8
2, 6
2, 7
2, 8
3, 6
3, 7
3, 8

To avoid this you must define a relation between the two tables. For example:

SELECT * FROM table1, table2 WHERE table1.name LIKE x AND table2.name LIKE y AND table1.type = table2.id;

This can also be rewritten as a JOIN statement. For more on Joins, this link should help: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Upvotes: 0

transilvlad
transilvlad

Reputation: 14532

$result = $query->query("SELECT * FROM `table1` AS `t1` JOIN `table2` AS `t2` ON `t1`.`id` = `t2`.`t1id` WHERE `t1`.`title` LIKE '%" . $query . "%' AND `t2`.`posts` LIKE '%" . $query . "%' LIMIT 10;");

if($result ) {
  while ($result = $query->fetch_object()) {
    echo '<li onClick="window.open(\'' . $result->guid . '\');">' . $result->post_title . '</li><br>';
  }
}
else
  echo 'ERROR: There was a problem with the query.';

I think it should look something like this.

You should use join and have a field to connect the two tables like this: t1.id = t2.t1id

You can use the table1, table2 if you want but still better to use JOIN.

One thing to note when you use LIKE it is better to use percent before and after like this: LIKE '%" . $query . "%

Upvotes: 1

geryjuhasz
geryjuhasz

Reputation: 184

change: 

SELECT * FROM table1, table2 WHERE table1.title AND table2.posts LIKE '$query%' LIMIT 10

to:

SELECT * FROM table1, table2 WHERE table1.title LIKE '$query%' AND table2.posts LIKE '$query%' LIMIT 10

or whatever variable you want title to be like

Upvotes: 0

Suhail Patel
Suhail Patel

Reputation: 13694

You can not join conditions like you are trying to, each connective needs to have it's own statement

SELECT * FROM table1, table2 WHERE table1.title LIKE '$query%' AND table2.posts LIKE '$query%' LIMIT 10

Upvotes: 2

AllTooSir
AllTooSir

Reputation: 49372

Change your query to : 

SELECT * FROM table1, table2 WHERE table1.title LIKE '$query%' 
AND table2.posts LIKE '$query%' LIMIT 10

Upvotes: 3

Related Questions