How to Union tables ?

hacku69

i have a this problem :😕

my code:

$sql="select from hotnews where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from slavenews where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from bd_trongnuoc where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from bdqt_others where bdqt_others.topic like '%$adsearch%' or bdqt_others.message like '%$adsearch%'
Union
select from bd_anh where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from bd_tbn where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from bd_y where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from cacmonthethao where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from tintucthoisu where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungchuyencoso where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungnhanvat where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungdoithuong where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungnguoiay where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhunggocnhin where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungvande where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungykienbandoc where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungdoisong where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select from nhungchuyenvat where topic like '%$adsearch%' or message like '%$adsearch%'

";
$result_search=mysql_query($sql);
$row=mysql_num_rows($result_search);

my purpose's search keyword in topic and message in databae ( have much tables)... But i received a message #1222 - The used SELECT statements have a different number of columns :queasy:

Weedpacket

The message explains the error. You're selecting every column from all of those tables (why?!?!); if they have different numbers of columns then they're not going to fit together.

Ask for the columns you actually want ("Select topic, message, etc.") from each table.

hacku69

i chose clearly column that i take. but still errors :

my code

$sql="SELECT topic as t1,message as m1 FROM (select topic as t2,message as m2 from hotnews where topic like '%$adsearch%' or message like '%$adsearch%' Union
select topic as t3,message as m3 from slavenews where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t4,message as m4 from bd_trongnuoc where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t5,message as m5 from bdqt_others where bdqt_others.topic like '%$adsearch%' or bdqt_others.message like '%$adsearch%'
Union
select topic as t6,message as m6 from bd_anh where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t7,message as m7 from bd_tbn where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t8,message as m8 from bd_y where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t9,message as m9 from cacmonthethao where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t10,message as m10 from tintucthoisu where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t11,message as m11 from nhungchuyencoso where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t12,message as m12 from nhungnhanvat where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t13,message as m13 from nhungdoithuong where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t14,message as m14 from nhungnguoiay where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t15,message as m15 from nhunggocnhin where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t16,message as m16 from nhungvande where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t17,message as m17 from nhungykienbandoc where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t18,message as m18 from nhungdoisong where topic like '%$adsearch%' or message like '%$adsearch%'
Union
select topic as t19,message as m19 from nhungchuyenvat where topic like '%$adsearch%' or message like '%$adsearch%')

		";
		$result_search=mysql_query($sql);
		$row=mysql_num_rows($result_search);

this error : #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select topic as t2 , message as m2 from hotnews where topic lik

i dont know how to search a keyword in a database with tables have columns: topic, message, author...to show in my page which word that User search :queasy:

please, show me, thks

Piranha

You want all the results in the same columns, then you need to use the same column names:

select topic as t from table1 where something
union
select topic as t from table2 where something

Roger_Ramjet

The column names or aliases from the first select are used for all subsequent selects in a union query.

Your syntax error is because you are using different column names in the outer query to those that are returned by the subquery.

If you want to know where the match came from then do this

$sql="SELECT t, topic as t1,message as m1 FROM 
(select 'hotnews' as t, topic,message from hotnews where topic like '%$adsearch%' or message like '%$adsearch%' 
Union
select 'slavenews', topic,message from slavenews where topic like '%$adsearch%' or message like '%$adsearch%'
Union 
etc

Sxooter

More importantly, this points up what looks like a de-normalized database design that is just going to get uglier and uglier to maintain.

Make a main table, put all those data points into it, and have an extra column that identifies the user (currently the table name in each of your selects).

I shudder to think of maintaining this thing over the years. It'll grow to thousands of tables and explode everytime someone needs to change anything.