PHP / MySQL Problem

Hi Guys

I wonder if someone could help me.

I have a mysql database with 5 million records. One field consists of identity numbers, which are entered as follow:

xxxxxx xxxx xxx

When users conduct a search they will enter the identity number as follow:

xxxxxxxxxxxxx

How can I remove the spaces between the numbers in the mysql field or get the users to be able to search the field.

Currently they get no results when they search without spaces.

 

If your field in the database contains spaces, then searching the database for the number both with and without spaces at the same time will be very slow, because you will have almost no index coverage.

Probably the best idea is to add a new field to the table that contains the ID number with no spaces. A single update can fill that in, and in the future you can do it with a trigger or by changing your application's insert/update code (the trigger being preferable).

Then always remove the spaces from the string the user has entered before searching, and just match against that new field.

 

Thanks SmallPotatoes, but how will I do this with Mysql?

 

alter table mytable add id_without_spaces char(13);
update mytable set id_without_spaces = replace(id, ' ', '');
create trigger remove_spaces_insert before insert on mytable for each row set new.id_without_spaces = replace(new.id, ' ', '');
create trigger remove_spaces_update before update on mytable for each row set new.id_without_spaces = replace(new.id, ' ', '');

Code (markup):

(triggers only work in MySQL 5 and later. If you are still using 4 for some reason, then you'll have to do the space removal on inserts and updates in your PHP program)

Then in your PHP code just make sure you remove spaces from the user input before matching against the id_without_spaces database field.

$search_id = preg_replace('/[^\d]/', '', $_POST['id']);
$sql = "select * from mytable where id_without_spaces = '{$search_id}' limit 1";

Code (markup):

 

Just to clarify, that trigger ensures that the field "id_without_spaces" is automatically populated with the same thing that was in field "id", only with the spaces removed. You do not need to put anything into id_without_spaces in your inserts and updates; it will be handled by MySQL.

 

Hi SmallPotatoes

I am indebted to you!

This worked just fine! Although Mysql gave me some errors, it did create the new field and imported the ID numbers without the spaces!

Thank you very much!