Keywords search over multiple tables

[jim.booth.5209]

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

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

[broj1]

Can you clarify this: you have a table C_Search containing a set of keywords. Do you want to search by all of them or just a few of them?

[jim.booth.5209]

@broj1 Yes that's right. I would like to use all of the keywords I have set for the different courses available. I know I could cheat and just move everything into 1 table but I wouldn't learn that way.

[broj1]

OK, so first read all the keywords from the C_Search (I hope it is not too many of them) and use the resulting array to build your search query:

// 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 keyword 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);
...

Well, something like that, I haven't tested it. If it does not work OK, we'll go into detail

[jim.booth.5209]

Hey, I thought that was it but I getthis when I run a rearch for a known keyword:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/jimbooth/public_html/search.php on line 74

Warning: mysql_num_rows() expects parameter 1 to be resource, null given in /home/jimbooth/public_html/search.php on line 79
Sorry, No results found.

The whole code again is:

<?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 keyword 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);

if (mysql_num_rows($sql) <= 0) {
// no results
echo 'Sorry, No results found.';
} else
while ($row = mysql_fetch_array($sql)){
    echo '<br/> Course Title: '.$row['Course_Name'];
    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/>';
    }
?>

Feeling a bit dumd with this one lol. I know I am looking straight at the problem, just can't see the wood for the trees! Thanks for the help :)

[broj1]

Change line 16 from:

if (mysql_num_rows($sql) <= 0) {

to:

if (mysql_num_rows($res2) <= 0) {

and line 20 from:

while ($row = mysql_fetch_array($sql)){

to:

while ($row = mysql_fetch_array($res2)){

I changed the $sql to $res2 to be more descriptive (it represents a result).

[broj1]

Another note: if the keyboards in the table are whole words already you might not need a % wildcard character in a query and the query can be changed to the following form:

$query = "select * from C_Info WHERE keyword IN ($keyword_list)";

The $keyword_list variable would be a comma delimited string of keywords. You would build it using php implode function which would join resulting array using a comma as a separator.

[jim.booth.5209]

When I run the query now, I get a list of all courses lol. There is the error message of:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/jimbooth/public_html/search.php on line 74

which displays before the list of search results.

The code as it stands now is:

<?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 keyword 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);

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/>';
    }
?>

So it looks like it is displaying all of the information for all courses and disregarding the keywords. ARGHhhhhhhh lol

[broj1]

Can you insert this DEBUG code on line 15 (as above):

die($query);

and post the result here. It will display the query in its final form.

[jim.booth.5209]

Morning :)

I have inserted the 'die' and now there are no results but I do get this:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/jimbooth/public_html/search.php on line 74
select * from C_Info WHERE 1

<?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 keyword 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/>';
    }
?>

Thanks for your help, Sorry to be a pain, Jim

[broj1]

What is the keyword column name in the C_Search table? I named it keyword just as an example. You might have to change it to the real column name in the query on line 9:

$res1 = mysql_query("SELECT keyword FROM C_Search");

And a note: do you want to find results matching ALL the keywords or just at least ONE of them. In later case you might want to replace AND with OR in the sql.

[jim.booth.5209]

Sorry, that was such a dumbass mistake to make. I read through it and completely overlooked the 'keyword' I have renamed it now to reflect what it's called in the table.

<?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/>';
    }
?>

When I search for a keyword now I get:

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

Maybe I should admit defeat and just move the keywords into the same table as the rest of the details?

[broj1]

No, do not admit defeat yet. Make a new test file like this

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

$res1 = mysql_query("select C_Key_Words from C_Search");
while ($keyword_row = mysql_fetch_assoc($res1)) {
    echo "{$keyword_row['keyword']} <br />";
}
?>

Post what gets displayed.

[jim.booth.5209]

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

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

I have used this code in the search.php and no results were returned at all.

[broj1]

Have you tried the test from my previous post? Can you post the output?

Can you also post some of the current data in the C_Search table (just a few rows).

[jim.booth.5209]

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 - 1
C_Key_Words - Business, Studies, BA, Hons, Human, Resource, Management, Finance
C_NLC_Ref_No - University of Lincoln
Awarding_Body - 0
C_UCAS_Code - NLC

There are a list of keywords separated by a coma. 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.

<?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) <= 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/>';
    }
?>

[broj1]

In the code above you have errors:
- a ) and ; missing on line 7
- first part of the main query has gone

Does the C_Key_Words field in the C_Search table contain multiple keywords separated by comma in each row? Please confirm, since the query has to be changed then.

[jim.booth.5209]

Morning, the list of keywords are separated by a comma in a list. These are found in the field C_Search.

[broj1]

I hope this is what you are after:

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

// you get users to supply keywords, probably using a form, I'll just make them up
// in this test case the user is searching by two keywords: Business and Human
$userKeywords = array('Business', 'Human');

// find course IDs from the C_Search table that contain above keywords
// start the first part of the query including the dummy WHERE condition
$query = 'SELECT Course_ID FROM C_Search WHERE 1';

// add each of the keywords that the user supplied
foreach($userKeywords as $kwd) {

    $query .= "  OR C_Key_Words LIKE '$kwd'";
}

// shoot the query
$res1 = mysql_query($query);

// initialize the string that will hold the course IDs that match the keywords
// IDs will be separated by comma
$courseIdList = '';

// retrieve course IDs that match the keywords and add them to $courseIdList
while ($row = mysql_fetch_array($res1)){

    $courseIdList .= $row[0] . ',';
}

// remove the trailing comma from the $courseIdList
$courseIdList = rtrim($courseIdList, ',');

// now when you have all the found course IDs, read the data about the courses
$query = 'SELECT * FROM C_Info';

// add the course IDs only if they exist in the $courseIdList
if($courseIdList != '') {

    $query .= " WHERE Course_ID IN ($courseIdList)";
}

// shoot the query
$res2 = mysql_query($query);

// display the keywords
echo '<p>You searched by: <strong>';
echo implode(', ', $userKeywords);
echo '</strong></p>';

// read each row and display the data
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/>';
}

Most of code is explained in the comments. But basically you get the keywords from the user, put them in an array, get the list of Course IDs that match the keywords (from C_Search table), then select the rows with course IDs that are in the list.

[jim.booth.5209]

Okay, cool. It returns a list of all of the courses in the database so far with a comment on top which says:
You searched by: Business, Human. It does this regardless of whether the word is in the Key_Words field or not.

How can I get it to use the registered keywords against what the customer types into the search box?
The temporary link to the search site is http://www.jimbooth.x10.mx/Index.html which may make it easier for you.

Thank you for all your help. You'll be glad to know that I'm going to find myself a decent PHP tutorial and work through that. I've already bought PHP + MYSQL for Dummies :)

[broj1]

You searched by: Business, Human. It does this regardless of whether the word is in the Key_Words field or not.

In the above code I made up two keywords for testing:

// you get users to supply keywords, probably using a form, I'll just make them up
// in this test case the user is searching by two keywords: Business and Human
$userKeywords = array('Business', 'Human');

You have to put the keywords from the search form into the userKeywords array.

I've already bought PHP + MYSQL for Dummies :)

OK, that is good for start. But later switch to advanced books. I used this one:

http://www.amazon.com/PHP-MySQL-Dynamic-Web-Sites/dp/032152599X

Also check out the phpacademy. A cool way to learn.