Not inserting with correct index

[freelance84]

I have a MySQL table named "name_gender" with fields "name" and "gender".

 

Field "name" is unique.

 

I originally had around 6000 names and everything worked fine.

 

I have just added 5000 more names via a loop in php and there is a problem... When i search for one of the new names it returns nothing, however they are there.

 

THE PROBLEM:

When viewing the table in phpMyAdmin; if i simply click the browse tab it displays the table contents in the order they were added, oldest first.

 

When i then click the field header named "name" it orders the whole table alphabetically irrespective of entry date.

 

Now at the top of the table is the, name - 'Aabha'  &  gender - '0'. This name is one of the second batch added to the table (one of the 5000). However if i search the table for Aabha it says nothing has been found.

 

A little further down the table is the entry, name - 'Aaron' & gender - '1'. This name is one of the first batch added to the table, when i search for this name it finds it no problem.

 

What is even wierder is with the name 'Aabha' entered via the php loop, if i try to add name - 'Aabha'  &  gender - '0' to the table again from phpMyAdmin it lets me do it. After adding the name with phpMyAdmin if i try to add the name again it say duplicate (which is what should happen the first time).

 

This indicates that my original script is adding the entries to the table but without the unique index. Here is the code used:

 

foreach($definite_female as $fename)
{
	$query_check_name = mysql_query("SELECT name FROM name_gender WHERE name='$fename'");
	if(!mysql_num_rows($query_check_name)  && !empty($fename))
		{
			$query_insert_female = mysql_query("INSERT INTO name_gender (name, gender) VALUES ('$fename','0')");
			++$female_count;
		}
	else{
			++$female_count_fail;
		}
}

 

 

Any help here would be brilliant, i'm a little lost. Should i be adding a seperate ID column to the table (even thought the names themselves are unique)?

[PFMaBiSmAd]

You didn't show or state where the data is coming from, but you probably have some white-space characters (space, new-line) as part of the data and should trim() the data before you insert it.

[Muddy_Funster]

You didn't show or state where the data is coming from, but you probably have some white-space characters (space, new-line) as part of the data and should trim() the data before you insert it.

 

100% agree.  Try and query your table using LIKE and I bet you get your results (...WHERE name LIKE '%Aabrha%')

[freelance84]

Exactly correct, a simple trim sorted it out.

 

Thanks.