Help with database search

[kathanon]

Hi

I am trying to amend a search of an image database. The search works fine on single keywords, but if I add two separate keywords in the search it will come up with a result of 0, even though both keywords are in the database separately. I am new to this and am not sure whether I can add both words together as another key in the database, or what is the best way to allow the search to pick up the images. It seems to me that there must be a simpler and more elegant and better way of doing this with the SQL and PHP code?


if(isset($HTTP_POST_VARS['searchme']) ) { // true if search has been submitted
if ($HTTP_POST_VARS['keys'] == "") {
$Criteria = "";
} else {
$Criteria = " AND ((k.key_word LIKE '%".$HTTP_POST_VARS['keys']."%')";
$Criteria .= " OR (i.img_desc LIKE '%".$HTTP_POST_VARS['keys']."%')";
$Criteria .= " OR (i.img_title LIKE '%".$HTTP_POST_VARS['keys']."%'))";
}
if ($HTTP_POST_VARS['img_category'] <> "999") { //search a specific category
$Criteria .= " AND k.key_word=".GetSQLValueString($HTTP_POST_VARS['img_category'], "text")." ";
}


Any help would be very gratefully received.

Thank you

 

[kathanon]

I have just noticed another strange thing, on the main index.php where the search is entered, it only shows 108 images in the database, ie. It will return say---9 out of a possible 108. However, when you go to the next page, to view the rest of the search results, it then shows the figure 128 - ie. 9 out of a possible 128.

 

[kathanon]

This is the part that returns the number. Sorry if this is rambling and imprecise, I am a novice and trying to sort out some problems with this


<td nowrap>Images <b><?php echo ($startRow_imgs) ?></b> to <b><?php echo ($startRow_imgs + $totalRows_imgs) ?></b> returned out of <strong><?php echo $allRows_imgs ?></strong>

 

[Itshim]

As for your first question about multiple words in one search query...you can use the expode() function to get an array of words to search, then loop through the array created by the explode() function searching the database. This may come up with duplicate results, depending how the database is setup. If that is the case, use the array_unique() to remove duplicate elements in the array.

This can get confusing, from my experience, array_unique() does not condense the array. So if you have an array with 10 elements, element 1 and 3 are duplicates, array_unique() will remove element 3 and leave elements 4-9 'where they are'. If you then use count() on the array it will say you have 8 elements, but the elements are 0-2 and 4-9.

To 'fix' this I get a count() of the array before using the array_unique() function and then you can loop through the array and create a new array with the null elements removed.

As for your other question, can you post the code which creates the variables:
$startRow_imgs, $totalRows_imgs, $allRows_imgs

Hope this helped
Itshim

 

[kathanon]

Thank you so much for the speedy reply. I will look into the first part and learn a bit more about exploding arrays and how to do as you suggest to search on more than one keyword. I have copied your suggestion into my php study notes.

You mention duplicate results, I know that there was a problem with the search returning 3 results instead of 1, a while ago, before I started to look after the website and that this was amended. Now it doesn't show enough!!

I have managed to find a solution for the second problem. On looking through the database I found some records with the date not specified. This meant these records were not showing. I have managed a workaround for this by adding a date to these records and then using an 'if $date >......' statement to only echo the existing dates, as the dates I am adding are fictitious (I am sure there is a much better way, but have not learnt enough about php yet)

When I have more time and have learnt a bit more about the code I will alter it, but it is on a members' area and needs to working properly by tomorrow.

Thank you again

Kathy

 

[kathanon]

I am sorry to ask a long winded question but I wonder if anyone could point me in the direction of a good tutorial, or have any ideas how to search on more than one term when using php with mysql.

I thought I had some time to learn, but now have to get this search engine working properly very fast and need to be able to use more than one keyword at a time.

This used to search on more than one term, but then gave multiple identical images. I think these parts may be the commented out parts in the script.

I wanted to find out more about Itshim's suggestion of using array unique or to somehow get this into the code.

I have pasted the code below.

Thank you for any input or any comments.

$editFormAction = $HTTP_SERVER_VARS['PHP_SELF'];
/*if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
$editFormAction .= "?" . $HTTP_SERVER_VARS['QUERY_STRING'];
}*/

mysql_select_db($database_dbase, $dbase);

if(isset($HTTP_POST_VARS['searchme']) ) { // true if search has been submitted
if ($HTTP_POST_VARS['keys'] == "") {
$Criteria = "";
} else {
$Criteria = " AND ((k.key_word LIKE '%".$HTTP_POST_VARS['keys']."%')";
$Criteria .= " OR (i.img_desc LIKE '%".$HTTP_POST_VARS['keys']."%')";
$Criteria .= " OR (i.img_title LIKE '%".$HTTP_POST_VARS['keys']."%'))";
}
if ($HTTP_POST_VARS['img_category'] <> "999") { //search a specific category
$Criteria .= " AND k.key_word=".GetSQLValueString($HTTP_POST_VARS['img_category'], "text")." ";
}

$FromDate = $HTTP_POST_VARS['year']."-".$HTTP_POST_VARS['mth']."-".$HTTP_POST_VARS['day']." 00:00:00";
$ToDate = $HTTP_POST_VARS['toyear']."-".$HTTP_POST_VARS['tomth']."-".$HTTP_POST_VARS['today']." 23:59:59";
$Criteria .= " AND i.img_taken BETWEEN ".GetSQLValueString($FromDate, "date")." AND ".GetSQLValueString($ToDate, "date")." ";

$Orderby = " ORDER BY img_taken DESC";
switch ($HTTP_POST_VARS['orderby']) {
case "alph":
$Orderby = " ORDER BY img_title";
break;
case "prec":
$Orderby = " ORDER BY img_created DESC";
break;
case "olde":
$Orderby = " ORDER BY img_created ASC";
break;
}
$query_imgs = sprintf("SELECT DISTINCT (i.img_ID), i.img_ID, i.img_title, i.img_file, i.img_taken, i.img_type FROM images i, keywords k, keylinks l WHERE k.key_ID=l.key_ID AND i.img_ID=l.item_ID AND i.img_archive='0' %s %s", $Criteria, $Orderby);
//echo $query_imgs; //$query_limit_obs = sprintf("%s LIMIT 0, 10", $query_obs);
} else {
$query_imgs = "SELECT * FROM images WHERE img_archive='0' ORDER BY img_created DESC";
}