5 column name row not returning all results

[richrock]

Hi,

 

I've got a 5 column name table, and need to search it.  Simple in theory, but it's not returning all the names.

 

For example, if I have the name 'John Andrew Smith', 'John Smith', 'John Edward Smith'  and search for Smith, it won't return John Andrew Smith...

 

It's part of a search system which calls a script to search based on what is typed:  Here's the mysql query:

 

$searchq		=	strtoupper($_GET['q']);
//$getRecord_sql	=	'SELECT * FROM '.$SQL_FROM.' WHERE '.$SQL_WHERE.' LIKE "'.$searchq.'%" GROUP BY surname LIMIT 30';
$getRecord_sql	= "SELECT surname, firstname, secondname, thirdname, fourthname, fifthname FROM ".$SQL_FROM." WHERE ";
    $getRecord_sql .= "`surname` LIKE '".$searchq."%' OR";
    $getRecord_sql .= " `firstname` LIKE '".$searchq."%' OR";
    $getRecord_sql .= " `secondname` LIKE '".$searchq."%' OR";
    $getRecord_sql .= " `thirdname` LIKE '".$searchq."%' OR";
    $getRecord_sql .= " `fourthname` LIKE '".$searchq."%' OR";
    $getRecord_sql .= " `fifthname` LIKE '".$searchq."%'";
//$getRecord_sql .= " ORDER BY surname ASC";
    $getRecord_sql .= " GROUP BY firstname ORDER BY surname LIMIT 30";
//$getRecord_sql .= " LIMIT 15";

 

I've set the LIKEs to wildcard the one side, so it's quite speedy, but can't figure why it doesn't show all results.  Not sure if it's something to do with being distinct on the surname, or the grouping needs changing.  Any ideas appreciated.  Thanks

[GeoffOs]

Because your grouping clause only specifies firstname then duplicates will be removed from the results where the firstname matches. I would have thought you would not need group by, unless you specify all the fields.

 

The group by clause is explained http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

 

Are you expecting duplicates across all the name fields?

[richrock]

Thanks for the reply.

 

I have multiple entries for each name, but only need to list it once... I tried removing GROUP BY - it wasn't right... I'll see if I can extract some examples from the DB (don't worry, they're all dead musicians, so no privacy issues there AFAIK  )

 

If I group by surname, it seems to miss out differing sets of names, it's really hard to explain....

[fenway]

You only have a single table -- you can't be getting back "multiple".

[richrock]

What I mean is :

 

John Smith 1858-1890 Vienna

John Andrew Smith 1870-1932 Paris

John Robert Smith 1792-1826 Dusseldorf

John Andrew Smith 1870-1932 Paris

John Robert Smith 1792-1826 Dusseldorf

John Andrew Smith 1870-1932 Paris

John Robert Smith 1792-1826 Dusseldorf

John Smith 1858-1890 Vienna

 

So there are duplicate entries for the same name in the same table - maybe multiple was the wrong word...

 

So if I were to search for  "John Robert" - I should get 1 result, if I search for "John", I should get 3 results.  Likewise, if I search for "Robert", I should also get 1 result.

[kickstart]

Hi

 

Think a version of you SQL like this would give you what you want:-

 

SELECT surname, firstname, secondname, thirdname, fourthname, fifthname, concat_ws(" ",ifnull(surname,'') , ifnull(firstname,'') , ifnull(secondname,'') , ifnull(thirdname,'') , ifnull(fourthname,'') , ifnull(fifthname,'') ) ConcatName 
FROM namestable 
WHERE 
`surname` LIKE 'john%' OR
`firstname` LIKE 'john%' OR
`secondname` LIKE 'john%' OR
`thirdname` LIKE 'john%' OR
`fourthname` LIKE 'john%' OR
`fifthname` LIKE 'john%'
GROUP BY ConcatName 
ORDER BY surname 

 

This is a misuse of group by, and you probably should use a distinct instead:-

 

SELECT distinct surname, firstname, secondname, thirdname, fourthname, fifthname
FROM namestable 
WHERE 
`surname` LIKE 'john%' OR
`firstname` LIKE 'john%' OR
`secondname` LIKE 'john%' OR
`thirdname` LIKE 'john%' OR
`fourthname` LIKE 'john%' OR
`fifthname` LIKE 'john%'
ORDER BY surname 

 

All the best

 

Keith

[fenway]

So there are duplicate entries for the same name in the same table - maybe multiple was the wrong word...

 

And you're sure that these are actually the same record in the table? that seems impossible....