Get first letter of surname

[WebWatcher]

If i have a database of name and they are saved as one i.e Joe Blog,
how can I get it so i can search the database and get all names starting with a particular letter.

I.e 'b' would give me Joe Blog

Thanks in advance

 

[Corazu]

Try grabbing all the names, stick em in an array. Loop through the array grabbing both the first/last name and stick them in a name object, or something to keep them together. Then have all of those objects in an array, quicksort. Find the first record starting with letter b (modified binary search should work), and loop through until you find the next letter.

 

[stuffradio]

mysql_query("SELECT * FROM `table` WHERE `name` LIKE '%$input%'");

$input would be the name of the input field that you're using to search with.

 

[krakjoe]

I make some assumptions, read the comments ...

<?php
class Search_By_Name
{
    const FIRST = 1 ;
    const LAST = 2 ;
    
    protected static $db ;
    protected static $mode ;
    protected static $letter ;
    
    protected static $results ;
    
    public function __construct( $letter, $db, $mode = SEARCH_BY_NAME::LAST )
    {
        self::$db = $db;
        self::$mode = $mode ;
        self::$letter = $letter ;
        self::$results = array( );
        
        if( $db )
        {
            /**
             * I'll assume:
             *     id - integer identifier
             *     name - varchar / text name
             *     table - the table name that you wanna search
             */
            if( ( $result = mysql_query( "SELECT name,id FROM `table`", self::$db ) ) )
            {
               	if( mysql_num_rows( $result ) )
               	{
               	 	while( $row = mysql_fetch_assoc( $result ) )
	                {
	                    if( self::match( $row['name'] ) )
	                    {
	                        self::$results[]=$row['id'];
	                    }
	                }
               	}
                mysql_free_result( $result );
            }
            else die( sprintf( "Failed to query MySQL database: %s", mysql_error( self::$db ) ) );
            
        }
        else die( sprintf( "The database variable passed to %s is invalid.", __METHOD__ ) );
    }
    public function next(  )
    {
        if( self::results( ) and ( $id = array_shift( self::$results ) ) )
        {
            /**
             * I'll assume:
             *     id - integer identifier
             *     table - the table name that you wanna search
             */
            if( ( $query = mysql_query( sprintf(
                "SELECT * FROM `table` WHERE `id` = '%d' LIMIT 1",
                $id
            ), self::$db ) ) )
            {
                if( mysql_num_rows( $query ) )
                {
                	return mysql_fetch_assoc( $query );
                }
            }
        }    
        else return null ;
    }
    public function results( )
    {
        if( count( self::$results ) )
        {
            return true;
        }
    }
    
    protected function match( $string )
    {
        $name = array(
            'first' => current( explode( ' ', $string ) ),
            'last' => end( explode( ' ', $string ) )
        );
        if( $name['first'] and $name['last'] )
        {
            switch( self::$mode )
            {
                case SEARCH_BY_NAME::FIRST:
                    return ( strtolower( substr( $name['first'], 0, 1 ) ) == strtolower( self::$letter ) );
                    break;
                    
                case SEARCH_BY_NAME::LAST:
                    return ( strtolower( substr( $name['last'], 0, 1 ) ) == strtolower( self::$letter ) );
                    break;
            }
        }
    }
}
/**
 * Connecting to the database, you shouldn't need to do this ...
 */
$db = mysql_connect( "localhost", "root", "" );
/**
 * Selecting database ^^ dito ....
 */
if( mysql_select_db( "names", $db ) )
{
    /**
     * Search for last names beginning with a b
     */
    $search = new Search_By_Name( 'b', $db );
    /**
     * Check there is results to use
     */
    if( $search->results( ) )
    {
        /**
         * Loop through each result $next is an assoc array of all data in table
         */
        while( $next = $search->next( ) )
        {
            echo "<pre>";
            print_R( $next );
            echo "</pre>";
        }
    }
    else echo "no results\r\n";
}
?>

 

[themoose]

that's a lot of code joe.. here's what I'd do

function LastLetterSurname($fullname) {
  $nameparts = explode(" ", $fullname);
  $lastname = $nameparts[count($nameparts)-1];
  return substr($lastname, 0, 1);
}
echo "The first letter of the last name of '".$fullname."' is '".LastLetterSurname($fullname)."'";

 

[krakjoe]

Yeah, I think you misunderstood the question ....

 

[themoose]

Just read it fully.. I did! :lol:

In that case, assuming that there's no middle names.. and the database is tidy, the query would basically be

mysql_query("SELECT * FROM `table` WHERE `name` LIKE '% $letter%'");

Notice the space before $letter.. that's basically looking for the last name, as first names don't have spaces before them.

 

[Corazu]

Just read it fully.. I did! :lol:

In that case, assuming that there's no middle names.. and the database is tidy, the query would basically be

mysql_query("SELECT * FROM `table` WHERE `name` LIKE '% $letter%'");

Notice the space before $letter.. that's basically looking for the last name, as first names don't have spaces before them.

Ugh, yea..I was trying to think of the SQL query for it using like..it was so late I didn't even think of using the space.