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.
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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.
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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?
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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'");
dasatti
Junior Poster in Training
57 posts since Dec 2008
Reputation Points: 10
Solved Threads: 5
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.
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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>
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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?
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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.
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
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. :)
cwarn23
Occupation: Genius
3,033 posts since Sep 2007
Reputation Points: 413
Solved Threads: 259
