Search Multiple Tables

[jason97673]

Hello, I am trying to search mutiple tables in my PHP code. I believe I only need to get the SQL query correct. Basically I have maybe 5 table where I need t oselect all the data from them but I only want to search one of the fields in each of those tables.

 

I made a post on the SQL boards but I think it will involve more PHP now because I believe I need to use a join

 

I have something like

$search=$_POST["search"];

//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search
if (!$_POST['search'] || $_POST['search'] == "" ||
strlen($_POST['search']) > 30) {
echo '<div id="search_error" class="text">There is a problem. Did you enter a keyword?</div>';
} else {

$result = mysql_query(" SELECT books.title, movies.name FROM books, movies where name or title like '%$search%'"
);   
$numrow = mysql_num_rows($result);

if ($numrow == 0) {
echo '<div id="no_results">Sorry No results found!';
echo "<a href=\"javascript:history.go(-1)\" title=\"Return to previous page\">Return to Previous Page.</a></div>";
}



//grab all the content
?>
  
<table id="search" cellpadding="0" cellspacing="0">  
      
  
<?
while($r=mysql_fetch_array($result))
{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
   $name=$r["name"];

   
   //display the row
echo "<tr>";
echo  "<td><b>$name</b></td>";
echo "</tr>";
}
} 
}

 

But now my only problem is, I am not sure what to do in the loop around the variable $name=$r["name"];

 

Anyone can help? Thanks

[clearstatcache]

how about showing samples of ur 5 tables and the display result u want...

[jason97673]

No problem

 

Books

bookID

title

ISBN

description

 

music

musicID

album

description

 

movies

movieID

name

description

 

hats

hatID

style

size

brand

 

pants

pantsID

size

style

brand

 

shirts

shirtID

style

size

brand

 

shoes

shoeID

style

size

brand

 

Basically my search feature would search the title column in the books table, the name column in the movies table, and the album column in the music table, style in each of the clothing tables. But I want to select all of the data so I can display the proper information on the page for each item.

 

Thanks,

[beboo002]

select b.title as btitle,m1.album as malbum,m2.name as moviename,s1.style as s1style ,s2.style AS s2style from book b ,music m1,movie m2, hat s1,paint s2

where condition(what ever u want)

 

if u forgert the where condition then it give

look 2 record in each table

then it return

2*2*2*2*2=32 row

[clearstatcache]

how about having a query per table

[clearstatcache]

to make it simpler ... u perform a query in each table to avoid confusion in the result...

[beboo002]

for avid confusion so we take column alias of each table

see in select statement m2.name as moviename here the output column is showing moviename

that means its movie table and column name is name it simple

[clearstatcache]

i understand u biboo002....dat was my first idea but as i try it...the query wud return wrong results.... any values in the tables will be returned even though it won't match the search value....i am thinking of another way...may be changing the database structure....but im still working on it...heheh...

[xyn]

I'd recomend exploding your search queries.

then you can match the tables better. also

 

do run two queries it looks like...

 

SELECT * FROM `table`, `table2`, `table3` WHERE (`table`.`field` LIKE '%word%' OR `table2`.`field`='%word%')

[beboo002]

here we hav two ways first

where condition will right for avoid to unnesseary result.

sceond one are change the data base

here i prafeer first method

 

 

[xyn]

could you rephrase that lol.. i really didn't understand.

[clearstatcache]

pls show it biboo002...ur where syntax to avoid wrong results...

[jason97673]

Thanks for the replies

 

I will try some of the above solutions for the queries but now I need help in displaying the search results like I have in the original code near $name=$r['name']. All I really need to do for now is have a very simple display of the search results such as if a book title was searched for, it just needs to display the title. But if a movie is searched for it will display the name of the movie etc.

[beboo002]

try this code

 

select b.title as btitle,m1.album as malbum,m2.name as moviename,s1.style as s1style ,s2.style AS s2style from book b left join music m1 on(b.title=m1.album) left join movie m2 on(m1.album=m2.name);

 

[beboo002]

plz attach rest table same manner

[clearstatcache]

have u tried ur code beboo002??? ..pls try it to see the results....

[Barand]

try

SELECT 'Movies' as category, movieID as ID, name as title 
        FROM movies
        WHERE name LIKE '%$search%'
UNION
SELECT 'Music' as category, musicID as ID, album as title 
        FROM music
        WHERE album LIKE '%$search%'
UNION
SELECT 'Books' as category, bookID as ID, title 
        FROM books
        WHERE title LIKE '%$search%'