search display using drop down

[siobhan]

Hi,

I'm quite new to php and mysql so apologies if I'm asking stuff that is too basic but I've had a look around and having difficulty finding the answer to my question.

I have a search page ( www.gamevolunteer.com/search.htm ), when the user chooses a searchtype from the dropdown menu and inputs a search term I'd like the results to show details from two different tables in the same database: Researcher and details.

The code I have right now is below but it just displays the result from the Researcher table. I'd like it to also display the results from the details tablewhich has seven columns, two of which are the same Researcher table (forename and surname) . Could someone explain in detail how I would do that?

<?php
// create short variable names
$searchtype=$HTTP_POST_VARS['searchtype'];
$searchterm=$HTTP_POST_VARS['searchterm'];
$searchterm= trim($searchterm);

if (!$searchtype || !$searchterm)
{
echo 'You have not entered search details. Please go back and try again.';
exit;
}

$searchtype= addslashes($searchtype);
$searchterm= addslashes($searchterm);

@ $db = mysql_pconnect('localhost', 'username', 'password');

if (!$db)
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}

mysql_select_db('volunteer');

$query = "SELECT * FROM Researcher WHERE ".$searchtype." LIKE '%".$searchterm."%'";
echo $query;
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

echo '<p>Number of researchers found: '.$num_results.'</p>';

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo '<p><strong>'.($i+1).'. Surname: ';
echo stripslashes($row['surname']);
echo '</strong><br />Country: ';
echo stripslashes($row['country']);
echo '<br />Institution: ';
echo stripslashes($row['institution']);
echo '</p>';
}
?>

[vmusic]

Hi,
Bascially you're joining two tables. You can look on line for SQL join examples and I'll show one here also.

I actually design databases for a living. It looks as if you have your welll.... lets say apples mixed up in the oragne barrel. -
So I'll make some things up here to make my point clear.

A researcher is a person ( in a role, but that's another story) so lets have a person table. In this person table we'll have the columns:
person_id,
surname,
lastname

We'll use the person_id to identify them. This means when some selects a researcher from the drop down or html <select> control the value they select is a person id

Then we'll have a research project table. (Ohhhh that doesn't even sound right). But for this example well make this anyway.
In this research table we'll have the columns
project_id,
project_nm,
project_keywords,
person_id

(The project keywords column might be a large varchar 225 and the key words about the project are in there in a comma separated fashion. For example "amino acid, protien, petptide, nitrogen")

Now we'll say the relationship between these two tables are:
A research project has only one researcher.
A researcher can have zero, one or many research projects.

I hope you can see 2 important things One is that I've described the relationship between these two entities. The other is that person_id appears in both tables..... here is where we'll do the join.

So now pretend I have 3 reasearchers or 3 rows in my person table. And pretend I have 18 rows or 18 research projects in the project table. My query might be something like:

SELECT per.person_id, per.surname, per.lastname, proj.project_id, proj.project_nm, proj.project_keywords, proj.person_id FROM person AS per, project AS proj WHERE per.person_id = proj.person_id AND proj.project_keywords, LIKE %searchword%

This query isn't exact - But it gives you an idea how to join tables. If you have access to your websites php MySQLAdmin pages where you can run queries in a test window that would help.

First build a query taht does a join between the tables you need. THEN add the WHERE or filter clause that looks for the keywords. It will be much easier that way.

Hope that gets you moving in the right direction
Vmusic

[siobhan]

Thanks for this--very helpful indeed! I'll have a go with this (and a table restructuring) and let you know how I get on.

Thanks again

siobhan

[siobhan]

Okay, I restructured the tables into two tables

Researcher table:

researcherid (primary key)
forename
surname
position
department
faculty
institution
address1
address2
address3
city
province
country
postcode
telephone
email

details table:
detailsid (primary key)
researcherid
bio
volunteerinterest
chapter
chapterdescription
researchinterest
originalemail

I've got the join working in phpmyadmin and have entered it into the php code on the page but as soon as I add in the %searchtype% and upload the page I get a
Warning: mysql_num_rows(): supplied argument error

I'd like to keep the drop down menu for now so that users can query based on institution, forename or country. Will this work?

Thanks again for all of your help,

Siobhan