Search in two mysql tables with non identical tables (keyword search)

Question

I want to make my keyword search in two mysql tables. my tables don't have any identical column names. But I tried few queries, they didn't work for me.

Keyword IS 07731A0328

I tried this:

$sql =  "select a.*, b.* from table1 a inner join table2 b on a.col1=b.htno WHERE a.col1 like '$name'";

$sql =  "select a.*, b.* from table1 a join table2 b on a.col1=b.htno WHERE a.col1 like $name";

Can someone help me with this? Thank you!

TABLE 1

TABLE 1

TABLE2

TABLE 2

"i tried few quiries" - show what exactly, might be easier to help. — Michał Powaga, Jun 14 '13 at 07:01
You don't mention what you have tried, what you want to do or the results you expect. How do you expect us to answer? Poor show. Please read the FAQ for questions on this site. — webnoob, Jun 14 '13 at 07:01
@MichałPowaga i have updated the question with queries i have used before. please check now. — user2457175, Jun 14 '13 at 07:17
@webnoob i have updated the question with queries i have used before. please check now — user2457175, Jun 14 '13 at 07:26

score 1 · Answer 1 · answered Jun 14 '13 at 07:04

1

Join is your friend: http://www.w3schools.com/sql/sql_join.asp

Combine rows from two or more tables, based on a common field between them.

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.col1=TABLE2.htnon 
WHERE TABLE1.col1 = "07731A0328"

answered Jun 14 '13 at 07:04

Enigma

1,699
10
14

got an error. `Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' ` – user2457175 Jun 14 '13 at 07:10
I would guess that the text in col1 and htnon have different formats. See http://stackoverflow.com/questions/9819159/illegal-mix-of-collations-utf8-general-ci-implicit-and-utf8-unicode-ci-implic for the solution to this problem – Enigma Jun 14 '13 at 07:13

score 0 · Accepted Answer · answered Jun 14 '13 at 07:06

0

The query will be

SELECT * FROM Table1,Table2 
WHERE Table1.col1=Table2.htnon AND Table1.col1 = "07731A0328"

answered Jun 14 '13 at 07:06

Yogesh Suthar

30,424
18
72
100

How does this query give two rows depicted by OP? – peterm Jun 14 '13 at 07:07
@peterm OP is asking for all rows from table1, not 2 rows. – Yogesh Suthar Jun 14 '13 at 07:08
got same error :( `Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' ` – user2457175 Jun 14 '13 at 07:13
@user2457175 You never said you got this error. Change the collation of anyone table and its respective column also. – Yogesh Suthar Jun 14 '13 at 07:15
bro now my collection is `utf8_unicode_ci` to what should i change it.. didn't have idea about this collection and things... kindly please let me know – user2457175 Jun 14 '13 at 07:21
See this link for changing the collation http://www.hosting.com/support/mysql/change-the-collation-on-a-mysql-database-via-phpmyadmin make collation to `utf8_general_ci`. – Yogesh Suthar Jun 14 '13 at 07:24
@YogeshSuthar Sorry. I've missed it. – peterm Jun 14 '13 at 07:46

Search in two mysql tables with non identical tables (keyword search)

2 Answers2