SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Feb 2013
    Location
    S****horpe, United Kingdom
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    The code I have so far is:

    PHP Code:
    <?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
    Last edited by cpradio; Feb 27, 2013 at 06:30. Reason: Added [php] tags

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    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

    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?
    PHP Code:
    $term $_POST['term'];
     
    $term $_POST['term']; 
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  4. #4
    SitePoint Member
    Join Date
    Feb 2013
    Location
    S****horpe, United Kingdom
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    +1

    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?
    PHP Code:
    $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 Code:
    <?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
    Last edited by cpradio; Feb 28, 2013 at 04:17. Reason: Added [php] tags

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  6. #6
    SitePoint Member
    Join Date
    Feb 2013
    Location
    S****horpe, United Kingdom
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

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

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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?

  8. #8
    SitePoint Member
    Join Date
    Feb 2013
    Location
    S****horpe, United Kingdom
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

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

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

    Quote Originally Posted by Jim Booth
    I have a normalized database which is based on a learning environment.
    Quote Originally Posted by Jim Booth
    There are a list of keywords separated by a comma.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •