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

Thank You,
Suhasini

Recommended Answers

All 34 Replies

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.

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?

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.

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??

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

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

Print the query and show us what it says..

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

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

$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....";

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.

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

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%'
$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.

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

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

wht do u mean by Y/W ??

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

You are welcome.

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

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?

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

What do you mean by describe it ?

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...

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

ok fine...

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?

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.

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.

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

yes, i will write the details.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.