How do I make a good SQL search?

I have a search field, and using that single text box I want the user to be able to find both galleries and images by their name. The problem I'm having is that I don't seem to be able to get any results on my queries, even though the syntax is fine and I get no errors.

Here's the scenario: My group name is "Liten gruppe!", and the search string is "liten". Both the search string and result string is switched to lower case for non-case sensitive search.

Something in the syntax must be doing something I'm not expecting.

SELECT `id`
FROM gallery_groups
WHERE   `name` LIKE LOWER('%$searchstring%') OR
        `date_created` LIKE LOWER('%$searchstring%');

Thanks for any help

EDIT: OK, that was a stupid mistake. But the correction still yields no results:

SELECT `id`
FROM gallery_groups
WHERE   LOWER(`name`) LIKE '%$searchstring%' OR
        LOWER(`date_created`) LIKE '%$searchstring%';

EDIT: Interesting! Copying the resulting query directly into PhpMyAdmin results in a match. It does NOT result in any rows when called from PHP however. How can that be?

EDIT: OK, it turns out that the query does indeed return 1 result. I was fooled by the dump output of the result:

object(mysqli_result)[3]
  public 'current_field' => null
  public 'field_count' => null
  public 'lengths' => null
  public 'num_rows' => null
  publ开发者_StackOverflowic 'type' => null

If all the fields in the result are null, how can I possibly know if there are any results other than running fetch_object() ?

You can issue case insensitive searches in MYSQL without LOWER:

WHERE   `name` COLLATE UTF8_GENERAL_CI LIKE '%$searchstring%' OR
        `date_created` COLLATE UTF8_GENERAL_CI LIKE '%$searchstring%';

AS for debugging: Try to print the SQL queries as they are passed to the database to see, if you really use the intended search string.

I'm assuming you're using msql_query to get your result object, in which case you can use mysql_num_rows to get the number of rows in the result.

http://php.net/manual/en/function.mysql-query.php

not sure if this helps you to find out if your query returns any rows. but this is how i do it:

$strSQL = "SELECT id "; $strSQL .= "FROM gallery_groups "; $strSQL .= "WHERE LOWER(name) LIKE '%$searchstring%' OR "; $strSQL .= " LOWER(date_created) LIKE '%$searchstring%'";

$rec = mysql_query($strSQL,$oConn);

if (mysql_num_rows($rec)>0) { $strName = mysql_result($rec,0,"name"); $strDateCreated = mysql_result($rec,0,"date_created"); }