searching and comparing from several tables

[tulipputih]

Hello,
Anyone know how to solve this problem?

I have several tables.
1. the main table namely lesson contains several fields that used for searching.
2. weight is assigned by users for each keyword for searching purpose.
3. users' keyword will be compared with the record in the database. each of them has their own similarity.(like lookup table)

table1- lesson
field: form - subject - skills - time - ability

table 2.
similarity table for form
fields: query- case -similarity
e.g : form1 form1 1
form1 form2 0.67

table 3.
similarity table for subject
fields: query- case - similarity
e.g : science science 1
science math 0.3

table 4- similarity table for skills
table 5- similarity table for time
table 6- similarity table for ability

thanks for your help

[cwarn23]

I read that you have some tables and the layout of the tables. So exactly what is it your trying to do? Just that you haven't posted a question other than to solve the problem.

[tulipputih]

ok, thanks.
I need to do searching from the database records.
users need to key-in keywords together with the weight of each searched keyword.
the result should be shown in ranking..

So I need to compare the similarity of the keywords and the records in database by referring to another tables with 3 main fields (query, case, similarity)

furthermore each of the keyword has weight assigned by users.

hope this is clear..if not..feel free to ask.
really need a solution

[cwarn23]

Sorry if this reply is a bit brief because it is getting late and so as a quick answer, if you have lets say one keyword called 'bob', your query should look like the following:

mysql_query('SELECT * FROM `table` WHERE `column1`="bob" AND `column2`="bob" AND `column3`="bob"');

If however you have 3 keywords and they are 'bob', 'test', 'fred' then your query would look like the following:

mysql_query('SELECT * FROM `table` WHERE (`column1`="bob" OR `column1`="test" OR `column1`="fred") AND (`column2`="bob" OR `column2`="test" OR `column2`="fred") AND (`column3`="bob" OR `column3`="test" OR `column3`="fred")');

I know that using the explode function to make an array of words then looping through the words to make the query string should do the job. If the above is what your looking for then let me know and tomorrow I'll construct the full code for you. But for now good night.

[tulipputih]

ok,
but users' search should contains the fields in the first table with weight-
form - (user's input- eg: form2) :5
subject - (user's input- eg: science) :4
skills - (user's input- eg: analysing):5
time - (user's input- eg: 60minutes):3
ability-(user's input- eg: 60minutes):2

after comparing and got the similarity they will be times to the weight, got the total and ranked ..
how to genrate this using php mysql?

[cwarn23]

The only thing that I'm slightly confused about your question is your table structure. Could you send a sample sql file or something? Also I would recommend having two columns per recorded keyword. So below is an example structure

valuefield | keyword1 | word1strength | keyword2 | word2strength
test       | fred     | 23            | george   | 11

However I can't see something along those lines in your database structure and can't even see how your tables relate to each other as there is no index key. So perhaps changing the design of your database might make it more efficient or do you disagree?

[dasatti]

for selecting and comparing fields from several tables use simlar query

mysql_query("SELECT * FROM table1,table2,table3 WHERE table1.column_name = table2.column_name AND table3.column_name='$usre_input'");

[tulipputih]

Hi cwarn23,
The only thing that I'm slightly confused about your question is your table structure. Could you send a sample sql file or something?
attached here is one of the table- form and the main table: lessonplan
the other 4 tables (subject ,skills ,time ,ability) follow the same structure...however the record need to be retrieved from lessonplan table first, than the values are compared to related lookup table
form.txt

.

Hope you can help me with this.
many thanks,

[cwarn23]

I have just checked the database and now must check before I continue is the following form what your looking for or could you change it to what this code should be. So by the sounds of it, each column has a seperate text box to search in. The sample html code of what I am talking about is as follows:

<form method="post">
<table border=1 cellpadding=4 cellspacing=0>
<tr><td colspan=2 bgcolor="#CCCCCC">Enter Keywords here:</td></tr>
<tr><td>Subject:</td><td><input type="text" name="subject"></td></tr>
<tr><td>Learning Area:</td><td><input type="text" name="learningarea"></td></tr>
<tr><td>Ability:</td><td><select name="ability" style="width:100%"><option value="excellent">Excellent
<option value="good">Good
<option value="poor">Poor</td></tr>
<tr><td>Skills:</td><td><input type="text" name="skills"></td></tr>
<tr><td>Time (minutes):</td><td><input type="text" name="time"></td></tr>
<tr bgcolor="#CCFFCC"><td>Search Now:</td><td align="right"><input type="submit" value="Search"></td></tr>
</table></form>

If that html form contains the fields you would like then I shall create the php script that processes it.

[tulipputih]

So by the sounds of it, each column has a seperate text box to search in.

yes , you are right. Furthermore each column has another textbox for the weight.

If that html form contains the fields you would like then I shall create the php script that processes it

yes please..really appreciate it

thank you

[cwarn23]

Try the following code:

<?
mysql_connect('localhost','root','');
mysql_select_db('mydatabase');
if (isset($_POST) && !empty($_POST)) {
    function generate($sql) {
        $dat='';
        $var=mysql_query($sql);
        while ($data=mysql_fetch_assoc($var)) {
            $dat.='<tr><td>'.$data['lessonID'].'</td><td>'.$data['subject'].'</td><td>'.$data['learningArea'].'</td><td>'.$data['ability'].'</td><td>'.$data['form'].'</td><td>'.$data['skills'].'</td><td>'.$data['time'].'</td></tr>';
            }
        return $dat;
        }
    $table=0;
    $string='';
    if ($_POST['weight']['subject']>0 && !empty($_POST['subject'])) {
        $sql='SELECT * FROM `lessonplan` WHERE `subject` LIKE "%'.mysql_real_escape_string($_POST['subject']).'%"';
        $string.='`subject` NOT LIKE "%'.mysql_real_escape_string($_POST['subject']).'%" AND ';
        $sqlarray[$_POST['weight']['subject']][]=$sql;
        }
    if ($_POST['weight']['learningarea']>0 && !empty($_POST['learningarea'])) {
        $sql='SELECT * FROM `lessonplan` WHERE '.$string.'`learningarea` LIKE "%'.mysql_real_escape_string($_POST['learningarea']).'%"';
        $string.='`learningarea` LIKE "%'.mysql_real_escape_string($_POST['learningarea']).'%" AND ';
        $sqlarray[$_POST['weight']['learningarea']][]=$sql;
        }
    if ($_POST['weight']['ability']>0 && !empty($_POST['ability'])) {
        $sql='SELECT * FROM `lessonplan` WHERE '.$string.'`ability`="'.mysql_real_escape_string($_POST['ability']).'"';
        $string.='`ability`!="'.mysql_real_escape_string($_POST['ability']).'" AND ';
        $sqlarray[$_POST['weight']['ability']][]=$sql;
        }
    if ($_POST['weight']['skills']>0 && !empty($_POST['skills'])) {
        $sql='SELECT * FROM `lessonplan` WHERE '.$string.'`skills` LIKE "%'.mysql_real_escape_string($_POST['skills']).'%"';
        $string.='`skills` NOT LIKE "%'.mysql_real_escape_string($_POST['skills']).'%" AND ';
        $sqlarray[$_POST['weight']['skills']][]=$sql;
        }
    if ($_POST['weight']['time']>0 && !empty($_POST['time'])) {
        $sql='SELECT * FROM `lessonplan` WHERE '.$string.'`time` LIKE "%'.mysql_real_escape_string($_POST['time']).'%"';
        $sqlarray[$_POST['weight']['time']][]=$sql;
        }
    if ($table==1) {
        echo '</table>';
        }
    if (!empty($sqlarray) && isset($sqlarray)) {
        echo '<table border=1 cellpadding=4 cellspacing=0><tr bgcolor="#CCCCCC"><td>ID</td><td>Subject</td><td>Learning Area</td><td>Ability</td><td>Form</td><td>Skills</td><td>Time</td></tr>';
        krsort($sqlarray);
        foreach ($sqlarray AS $array) {
            foreach ($array AS $sql) {
                echo generate($sql);
                }
            }
        echo '</table><p>';
        }
    }
?>
<style type="text/css">
.class {
      border:0px;
      }
</style>
<form method="post">
<table border=1 cellpadding=0 cellspacing=0>
<tr>
    <td colspan=4 bgcolor="#CCCCCC">Enter Keywords here:</td>
</tr><tr>
    <td>Subject:</td>
    <td><input class="class" type="text" name="subject" value="<? echo htmlentities($_POST['subject'], ENT_QUOTES); ?>"></td>
    <td><select name="weight[subject]" style="width:100%">
        <option value="9">9
        <option value="8">8
        <option value="7">7
        <option value="6">6
        <option value="5">5
        <option value="4">4
        <option value="3">3
        <option value="2">2
        <option value="1">1
        <option value="0">0
    </select></td>
</tr><tr>
    <td>Learning Area:</td>
    <td><input class="class" type="text" name="learningarea" value="<? echo htmlentities($_POST['learningarea'], ENT_QUOTES); ?>"></td>
    <td><select name="weight[learningarea]" style="width:100%">
        <option value="9">9
        <option value="8">8
        <option value="7">7
        <option value="6">6
        <option value="5">5
        <option value="4">4
        <option value="3">3
        <option value="2">2
        <option value="1">1
        <option value="0">0
    </select></td>
</tr><tr>
    <td>Ability:</td>
    <td><select name="ability" style="width:100%">
        <option value="excellent">Excellent
        <option value="good">Good
        <option value="poor">Poor
    </select></td>
    <td><select name="weight[ability]" style="width:100%">
        <option value="9">9
        <option value="8">8
        <option value="7">7
        <option value="6">6
        <option value="5">5
        <option value="4">4
        <option value="3">3
        <option value="2">2
        <option value="1">1
        <option value="0">0
    </select></td>
</tr><tr>
    <td>Skills:</td>
    <td><input class="class" type="text" name="skills" value="<? echo htmlentities($_POST['skills'], ENT_QUOTES); ?>"></td>
    <td><select name="weight[skills]" style="width:100%">
        <option value="9">9
        <option value="8">8
        <option value="7">7
        <option value="6">6
        <option value="5">5
        <option value="4">4
        <option value="3">3
        <option value="2">2
        <option value="1">1
        <option value="0">0
    </select></td>
</tr><tr>
    <td>Time (minutes):</td>
    <td><input class="class" type="text" name="time" value="<? echo htmlentities($_POST['time'], ENT_QUOTES); ?>"></td>
    <td><select name="weight[time]" style="width:100%">
        <option value="9">9
        <option value="8">8
        <option value="7">7
        <option value="6">6
        <option value="5">5
        <option value="4">4
        <option value="3">3
        <option value="2">2
        <option value="1">1
        <option value="0">0
    </select></td>
</tr><tr bgcolor="#CCFFCC">
    <td colspan=2>Search Now:</td>
    <td bgcolor="#FFFFFF"><input type="submit" value="Search"></td>
</tr></table></form>

[tulipputih]

thanks a lot.
I am trying to run it.

[tulipputih]

Hi,
I need to refer to the lookup table for each keyword inserted by user
to compare query and record in database and find the similarity.
this similarity is then times by the weight chose by user.
How can we refer to this lookup table ?
(separate lookup table for each field)

thank you.

[cwarn23]

Could you please explain in more detail what each column does. What I so far get is that there is one table per keyword box and the simularity field is times by the keyword strength and the id field is the linker between the lookup table and the lessonplan table. What I dont get is what are the fields 'query' and 'case' used for?

[tulipputih]

ok..thanks..
Attached herein is the interface of the search page.
the similarity should be reffered to other tables
(similarity table)

[tulipputih]

What I dont get is what are the fields 'query' and 'case' used for?

query is the keyword that user key-in
cases are records in database..
query and cases are compared..the similarity is taken from the llokup table..
& overall similarity is calculated.

many thanks

[cwarn23]

I just check the picture you sent and it has 12 searchable fields where as the mysql database has only 10 fields. How is that suppose to work? Also what might make this a lot easier since how you have a picture is if you write ontop of each box in the picture what mysql column the input box is meant to refer to because this is becomming a mind warp.

[tulipputih]

it has 12 searchable fields where as the mysql database has only 10 fields. How is that suppose to work

yup..that is just a sample interface..it will be editted later according to the databse structure

The main problem is I do not know how to refer to several lookup tables. How can I do this ?
many thanks.

[tulipputih]

Result of the search will be displayed in rank.
The most similar record to the query will be at the top of the result list..followed by the 2nd most similar and so on.
this similarity is calculated by referring to the weight and similar match of the keyword (query) and records in database.

Your code is good, but I need a suggestion on how to integrate the code to the lookup tables (which contain similarity)
thanks

[cwarn23]

Your code is good, but I need a suggestion on how to integrate the code to the lookup tables (which contain similarity)
thanks

Well I'm not sure if I can do much more on this topic but all the hard code I have posted in post #11. And as for how to do it. It should be just as simple as adding a bunch of lines of code into my script (post #11). But what those lines should be I'm not sure because you haven't go an easy database structure. But I noticed that I seem to be the only one replying to this topic. I wonder why? Perhaps somebody else could continue on with this topic as I am finding it difficult to understand the database structure. Good luck though. :)

[tulipputih]

ok, thanks for your reply.
really appreciate it.
I am trying to expand the code so that it works according to my need.

Many thanks.

[tulipputih]

Hello,
Anyone can help me to solve this problem?

thank you.