Search from multiple tables ??

[Suhacini]

hi,
How can I search from multiple tables in a db?DB I am using is MySql db.

Thank You,
Suhasini

[Fungus1487]

SELECT * FROM tablename WHERE somefield LIKE '%searchquery%'

other than taking the time to write an iterative script you will need to write individual SQL statements for each table.

[Suhacini]

Does this work for something like this..
I have a db in which i have 26 tables suppose say A to Z.Now if i want to search some 's1' in table 'S' will this work?

[Fungus1487]

then you would search using the following in that circumstance.

SELECT * FROM S WHERE s1 LIKE '%what you want to search for%'

The % sign either side of the search text indicates two wildcards and will search for the string inside other strings.

E.G.

table 'user' has rows...

id | name
1  | 'john'
2  | 'alan'
3  | 'joe'

when firing this SQL statement

SELECT id FROM user WHERE name LIKE '%jo%'

it will return rows with ID's 1& 3

because john and joe contains the phrase 'jo'

if you wanted to do this through all your tables and they have a similiar format.

then you could use a php array to hold the table name, ID and search field and loop through generating the sql statement dynamically.

[Suhacini]

Thanks for ur reply.but i want to search a keyword from all the tables at once from a db.
As u said i have tried like tht but am getting the result onli from the last query.
For example:

$var = @$_POST['keyword'] ;
  $trimmed = trim($var);

$query = "select * from a where MedName like \"%$trimmed%\"  
  order by dispname"; 

$query = "select * from b where MedName like \"%$trimmed%\"  
  order by dispname";
  
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

Now if i want to search '1' and i wanted the result shuld be 'a1,b1' but am getting onli 'b1'

so waht shuold i do to get both a1,b1??

[nav33n]

You can do it this way.

<?php
$con = mysql_connect("localhost","root");
mysql_select_db("test");
//get all the tables from the database test
$q = "show tables";
$r = mysql_query($q);
$searchresult = array();
while($row = mysql_fetch_array($r)) {
	$table = $row[0]; // get the tablename
	$searchtables = "select * from ".$table." where col1 like '%".$searchstring."%'"; //search the table column for the search string
	$result = mysql_query($searchtables);
	while($rows = mysql_fetch_array($result)) {
		$searchresult[] = $rows['name'];
	}
}
// $searchresult will have all the records from all the tables for the searchstring
?>

Cheers,
Nav

[Suhacini]

This is the error am getting after trying this code

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\exphp\medwits\search.php on line 14

[nav33n]

Print the query and show us what it says..

[Suhacini]

same error is coming...by the way which line to print??

[nav33n]

Have you changed the column and table names to suit your requirements ? Print out $searchtables and tell us what it prints.

[Suhacini]

$q = "show tables";

shall i write the table name thr?I have 26 tables...All of the at a time shall i write?is this the way i must write?

$q= " show a,b,c....";

[nav33n]

No. The code that I have given will search for all the tables in the database. (make sure you have only those tables in the database which you want to search). And what I mean by print your query is, echo $searchtables;
Tell us what it prints on the screen.

[Suhacini]

Error Again...but changed a little than before

select * from a where col1 like '%%'
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\exphp\medwits\search.php on line 14

select * from b where col1 like '%%'
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\exphp\medwits\search.php on line 14
              .
              .
              .

select * from z where col1 like '%%'
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\exphp\medwits\search.php on line 14

[Suhacini]

I have changed the col name...
the i got the result like this

select * from a where MedName like '%searchresult%'select * from b where MedName like '%searchresult%'select * from c where MedName like '%searchresult%'select * from d where MedName like '%searchresult%'select * from e where MedName like '%searchresult%'select * from f where MedName like '%searchresult%'select * from g where MedName like '%searchresult%'select * from h where MedName like '%searchresult%'select * from i where MedName like '%searchresult%'select * from j where MedName like '%searchresult%'select * from k where MedName like '%searchresult%'select * from l where MedName like '%searchresult%'select * from m where MedName like '%searchresult%'select * from n where MedName like '%searchresult%'select * from o where MedName like '%searchresult%'select * from p where MedName like '%searchresult%'select * from q where MedName like '%searchresult%'select * from r where MedName like '%searchresult%'select * from s where MedName like '%searchresult%'select * from t where MedName like '%searchresult%'select * from u where MedName like '%searchresult%'select * from v where MedName like '%searchresult%'select * from w where MedName like '%searchresult%'select * from x where MedName like '%searchresult%'select * from y where MedName like '%searchresult%'select * from z where MedName like '%searchresult%'

[nav33n]

$q = "show tables";

I have 26 tables...shall i write all the names at once

$q="show a,b,c...z";

Show tables will get all the 26 tables (If you have 26 tables in the database) . Thats a mysql query.

[Suhacini]

Hey got this...proceeding furthur...will get back to u again with new doubt...Thank You.

[nav33n]

Y/w. I ll be here.. ( I hope!)

[Suhacini]

wht do u mean by Y/W ??

By da way can we store long text directly in db..not using any insert queries...

[nav33n]

You are welcome.

No. You can't store a value in the table without using an insert query.

[Suhacini]

cant we enter thm frm back end?

Using insert query can we insert long text?
For example i have a value in db..and i want to describe tht...can i do tht?

[nav33n]

Yes. Using insert query you can insert anything, long text, short text, image, etc :)

What do you mean by describe it ?

[Suhacini]

From the above search prog i got few results..I stored thm in the form of table.
I jus wante to give some desc abt each search result in tht table

For example:I am searching for 'a'.I got abt 10 items related to a
A1:
blaba...blaba...asdasdaasdd......asd
asdadadsasasdd adttvbcvn...
A2:
tasd asdda....
A3:
sadfgfd dgdfg...

[nav33n]

Yeah.. You can store the description in whatever way you want..

[Suhacini]

ok fine...

[Suhacini]

I want to give page no's for navigation like we have here "page 1 of 3 1|2|3" how can i do tht?

[tulipputih]

Hi Nav,
I have modified this coding to suit my problem.
It works when I specify the column..however I would like to search from all columns in all tables.
So, do i need to list down all the columns or is there any other method to so ?

many thanks.

[djjjozsi]

if i were you, i were pull the table and field names into an array with the field type. Lets make an exeptions list to block connector tables, and make a list hidden/blocked field names.

[tulipputih]

if i were you, i were pull the table and field names into an array with the field type. Lets make an exeptions list to block connector tables, and make a list hidden/blocked field names.

hi, can you explain to me in details..thank you.
here is my code:

$searchtables = "select * from ".$table." where ".$column." like '%".$searchstring."%'"; //search the table column for the search string
            $result = mysql_query($searchtables);
              while($rows = mysql_fetch_array($result)) {

the error was shown on the last line above, while .....

thank you

[djjjozsi]

yes, i will write the details.

[djjjozsi]

lets see this example.

http://phpcode.hu/dl/?f=1028