Suit to Matto and his brilliant brain, here his the beginning solution to do a multiple
words query on a mutiple tables with Mysql/PHP.
But I need the community to get more accurate results.
You can try the following address, where my search test page is:
http://www.ambilao.com/imagebank/search9.php
What I want, and what I don't know how to do is, when i do a query with 2 words contained in the comments field
and 1 word in the "im_catgen" field, I get more result than I want and it's normal.
How can I do to get the most precise result, when the words are contained in 1 field of a table,
or when I get a composite word like "still life" in the "im_catgen" category?
What I want too is if one or more words are contained in one of the 2 tables, I get a result.
Another thing is how to treat in the query all these kind of caracters like " ' é à \ + - *, etc...
And how do I filter the "and" or "+" , "Or" or "-", in the query?
Could you be kind enough to help a young programmer as myself?
I am waiting for your suggestions or any comments about it.
But for the god given developper here is the code:
//The connection:
<?php require_once('../Connections/conn_phototek.php'); ?>
<?php
mysql_select_db($database_conn_phototek, $conn_phototek);
// The Matto script to get the query:
$keywords = explode(' ', $searchstring);
//Counting the words
$compteur = count ($keywords);
// For 1 word the condition is OR else 2 words or more the condition is AND to get more possibilty
if($compteur < 2) {
$operateur = " OR";
} else {
$operateur = " AND";
}
//The Matto script to get a search on several tables modified by myself for the conditions:
$clause = "AND ((comments like '%"
.implode("%' or comments like '%", $keywords)
."%')";
$clause2 = " (catgen.im_catgen like '%"
.implode("%' or catgen.im_catgen like '%", $keywords)
."%'))";
$query_rsSearch = "SELECT phototek.*, catgen.im_catgen FROM phototek, catgen WHERE catgen.catgenID=phototek.catgenID "
."$clause"
."$operateur"
."$clause2";
// This part is desactived for the test
//."formatID in(select catgen.im_format from catgen where $clause2)";
// ."id_category2 in(select id from category2 where $clause) or "
// ."id_category3 in(select id from category3 where $clause)";
$rsSearch = mysql_query($query_rsSearch, $conn_phototek) or die(mysql_error());
$row_rsSearch = mysql_fetch_assoc($rsSearch);
$totalRows_rsSearch = mysql_num_rows($rsSearch);
?>
<html>
<head>
<title>Search Test Page of Marc-André</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
// this line to checkup the query and how mysql is responding:
<?php echo $query_rsSearch ?>
//the form:
<form method="post" action="search9.php">
<input type="text" name="searchstring">
<input type="submit" name="submit" value="Search...">
</form>
//we list the results:
<?php do { ?>
<p><?php echo $row_rsSearch['ImageID']; ?>, <?php echo $row_rsSearch['url_folder']; ?>, <?php echo $row_rsSearch['im_name']; ?>, <?php echo $row_rsSearch['comments']; ?>, <?php echo $row_rsSearch['im_catgen']; ?></p>
<?php } while ($row_rsSearch = mysql_fetch_assoc($rsSearch)); ?>
<p> </p>
</body>
</html>
<!-- to clear the memory:-->
<?php
mysql_free_result($rsSearch);
?>
