SEARCH 3 TABLES

[kagster]

Hi all,

 

I try to make a search engine that will search on three tables and with multiple fields. Something like this:

 

<form action="search_results.php" method="post" name="findBook">

<p><label>Book title:</label><input type="text" name="BtName" class="field" size="20" /></p>

<p><label>Author:</label><input type="text" name="AuthorName" class="field" size="20"/></p>

<p><label>Publisher:</label><input type="text" name="PubName" class="field" size="20"/></p>

<p class="btn"><input type="submit" name="submit" value="Search.." /></p>

<input type="hidden" name="submitted" value="TRUE" />

</form>

 

And this is the three tables that form will search:

 

-----------------------------------

BOOKTITLE

BtId (Primary Key)

BtName

PubId (Foreign Key)

Value

-----------------------------------

 

-----------------------------------

AUTHOR

AuthorId (Primary Key)

AuthorName

-----------------------------------

 

-----------------------------------

PUBLISHER

PubId (Primary Key)

PubName

PubAddress

----------------------------------

I dont know how can make this select.  The user needs to be able to search Book Title, Author & Publisher.

 

Can anyone help me?

__________________

 

[ptolomea]

you need something like on your results page

 

$bookTitle = $_POST['BtName']

 

then

 

mysql_query("SELECT [fields you want] FROM [table ie BOOKTITLE] WHERE BtName='" . $bookTitle . "'");

[kagster]

Thank you for your reply.

 

I have tried this but it doesnt work

 

$query = "SELECT * t.BtName, a.AuthorName, p.PubName

FROM author

AS a

INNER JOIN authorship

AS i USING (AuthorId)

INNER JOIN BookTitle

AS t USING (BtId)

INNER JOIN publisher

AS p USING (PubId)

WHERE a.AuthorName LIKE '%".$_POST["AuthorName"]."%' AND p.PubName LIKE '%".$_POST["PubName"]."%' AND t.BtName LIKE '%".$_POST["BtName"]."%'";

 

Any idea where I am going wrong?

 

Thank you

[Ken2k7]

Why doesn't the book have a foreign key to author? ???

[kagster]

Sorry, what do you mean?  :-\