Keywords search over multiple tables

Hi folks, I have a normalized database which is based on a learning environment.
I would like to be able to search for a selection of keywords which are in a table called ‘C_Search’ and use them to pull up the course details which stored in ‘C_Info’. I have a basic search function but it is driving me crazy with how to get the keywords involved as I am new to all this and trying to learn as I go along…sometimes we need help :slight_smile:

The code I have so far is:

<?php
 
mysql_connect ("localhost", "jimbooth","test1")  or die (mysql_error());
mysql_select_db ("jimbooth_database");

$term = $_POST['term'];
 
$term = $_POST['term'];
 
$sql = mysql_query("select * from C_Info where C_Description like '%$term%'");

if (mysql_num_rows($sql) <= 0) {
// no results
echo 'Sorry, No results found.';
} else
while ($row = mysql_fetch_array($sql)){
    echo '<br/> Course Info: '.$row['C_Description'];
    echo '<br/> Duration: '.$row['C_Duration'];
    echo '<br/> Entry Requirements: '.$row['C_Entry_Req'];
    echo '<br/> Course Cost: '.$row['C_Cost'];
        echo '<br/> Course Progression: '.$row['C_Progression'];    
    echo '<br/><br/>';
    }
?>

Could anyone shed any light on this for me and point me in the right direction please

I would like to be able to search for a selection of keywords which are in a table called ‘C_Search’ and use them to pull up the course details which stored in ‘C_Info’.

So why are you not searching the table called C_Search?

Can you elaborate a little more, perhaps show us a real example, “I searched for …”

Posting the table schemas might help too.

+1 :tup:

Plus I’d like to point out that you need to sanitize your input and switch to mysqli or PDO.

Also why are you assigning $term twice?

$term = $_POST['term'];
 
$term = $_POST['term'];

Evening,
sorry for the delay but sadly I had to work. I have had a revision on the code which I will post underneath.
The reason that the keywords aren’t in the same table as the rest of the course info is because it apparently has to be normalized to death as part of the criteria.
This is what I have got so far along with the error message it produces:

<?php
 
mysql_connect ("localhost", "jimbooth_test","test1")  or die (mysql_error());
mysql_select_db ("jimbooth_groupproject");

// first part of the main query (with dummy WHERE operator so you can then use AND operators)
$query = "select * from C_Info WHERE 1";
// query the keywords
$res1 = mysql_query("select C_Key_Words from C_Search");
// loop through rows and add conditions to the main query
while ($keyword_row = mysql_fetch_assoc($res1)) {
    $query .= " AND C_Description like '%{$keyword_row['keyword']}%'";
}
$res2 = mysql_query($query);

die($query);

if (mysql_num_rows($res2) <= 0) {
// no results
echo 'Sorry, No results found.';
} else
while ($row = mysql_fetch_array($res2)){
    echo '<br/> <B>Course Title:</B> '.$row['Course_Name'];
    echo '<br/> <B>Course Info:</B> '.$row['C_Description'];
    echo '<br/> <B>Duration:</B> '.$row['C_Duration'];
    echo '<br/> <B>Entry Requirements:</B> '.$row['C_Entry_Req'];
    echo '<br/> <B>Course Cost: '.$row['C_Cost'];
        echo '<br/> <B>Course Progression: '.$row['C_Progression'];    
    echo '<br/><br/>';
    }
?>

This is the error I receive with the code as it is:

select * from C_Info WHERE 1 AND C_Description like ‘%%’ AND C_Description like ‘%%’ AND C_Description like ‘%%’ AND C_Description like ‘%%’ AND C_Description like ‘%%’ AND C_Description like ‘%%’

Like I said before, I am enjoying coding understand I have a long way to go. I could put all of the information in one table but that kind of defeats the object of learning :slight_smile:

Well…

This query:

select C_Key_Words from C_Search

Indicates that each row of the result set will contain a single column with the name of C_Key_Words. Therefore, this line:

$query .= " AND C_Description like ‘%{$keyword_row[‘keyword’]}%’";

Incorrectly attempts to access an undefined key. The proper key would be the name of the column cited above: C_Key_Words.

However, this really only fixes the symptom not the disease.

There should only be a single query that joins against the other table. Though I’m not really sure without seeing the table schemas. Mainly what is the relationship between C_Info and C_Search – the foreign key declaration?

Oh… and if you are creating a new application you should really be using PDO w/ variable binding. There is no reason not to if you don’t have to update a bunch of other code that might be dependent on the standard adapter.

I thought I would add the relevant table structures to help out :slight_smile:

C_Info

Course_ID
Course_Name
C_Description
C_Duration
C_Cost
C_Entry_Req
C_Assessment_Type
C_Progression
C_Type

C_Search

Course_ID
C_Key_Words
C_NLC_Ref_No
Awarding_Body
C_UCAS_Code

Any suggestions would be great. I am considering just moving all of the information into one table and saying ‘Sod it!’ lol

If for each row in C_Info there is only one corresponding row in C_Search, then yes, you might as well have just one table.

If C_Key_Words contains a string of keywords separated by commas or some other character such as a space, then it is that which should be in a separate table.

Are either of those the case?

EDIT…I have a normalized database which is based on a learning environment.
I would like to be able to search for a selection of keywords which are in a table called ‘C_Search’ and use them to pull up the course details which stored in ‘C_Info’. I have a basic search function but it is driving me crazy with how to get the keywords involved as I am new to all this and trying to learn as I go along…sometimes we need help

These are the relevant tables and the fields in them.
C_Info

Course_ID
Course_Name
C_Description
C_Duration
C_Cost
C_Entry_Req
C_Assessment_Type
C_Progression
C_Type

C_Search

Course_ID
C_Key_Words
C_NLC_Ref_No
Awarding_Body
C_UCAS_Code

There are a list of keywords separated by a comma. I would like to use them to allow users to search the database for available courses.

I know I have posted this before but some of the answers were confusing and I’m struggling to learn as it is.

&lt;?php
mysql_connect ("localhost", "jimbooth_test","test1")  or die (mysql_error());
mysql_select_db ("jimbooth_groupproject");
// first part of the main query (with dummy WHERE operator so you can then use AND operators)
$query .= " AND C_Description like '%{$keyword_row['keyword']}%'";
// query the keywords
$res1 = mysql_query("select keyword from C_Search") or trigger_error(mysql_error()
// loop through rows and add conditions to the main query
while ($keyword_row = mysql_fetch_assoc($res1)) {
    $query .= " AND C_Description like '%{$keyword_row['keyword']}%'";
}
$res2 = mysql_query($query);
die($query);
if (mysql_num_rows($res2) &lt;= 0) {
// no results
echo 'Sorry, No results found.';
} else
while ($row = mysql_fetch_array($res2)){
    echo '&lt;br/&gt; &lt;B&gt;Course Title:&lt;/B&gt; '.$row['Course_Name'];
    echo '&lt;br/&gt; &lt;B&gt;Course Info:&lt;/B&gt; '.$row['C_Description'];
    echo '&lt;br/&gt; &lt;B&gt;Duration:&lt;/B&gt; '.$row['C_Duration'];
    echo '&lt;br/&gt; &lt;B&gt;Entry Requirements:&lt;/B&gt; '.$row['C_Entry_Req'];
    echo '&lt;br/&gt; &lt;B&gt;Course Cost: '.$row['C_Cost'];
        echo '&lt;br/&gt; &lt;B&gt;Course Progression: '.$row['C_Progression'];    
    echo '&lt;br/&gt;&lt;br/&gt;';
    }
?&gt;

If you echo $query onto the page - is it a valid query? Does that query bring back results?

These 2 quotes clash, because this suggests that you could have a tag e.g. “English” in more that one place in your table.