Using LIKE for matching

[lexer]

Hi

I've got a Mysql table with numbers (table codes) and each number has and ID, I'm reading a file line by line using PHP (fopen),Each line has number, I want to match the number read in line with number on the table and then get the ID of the number, for example:

The line has the following number 0018697808976, And the table has the following numbers:

table codes:

number ID
001809 100
001869 101
0018X 102
00155 103
00153 104
002 105

The number on the line match with the number 001869 on the table, the number 0018697808976 should take ID 101.

Below part of the code that I'm using:

//$Numberx Contain part of the number to find out the ID
$Numberx=substr($Number,0,6);
$querye = "SELECT *, COUNT(ID) FROM codes WHERE codes.number LIKE '$Numberx' GROUP BY 	ID"; 
$resulte = mysql_query($querye) or die(mysql_error());

		// Print out result
  while($row = mysql_fetch_array($resulte)){
  $count=$row['COUNT(ID)'] ;

		}
		if ($count  == 1)
		{
                  $queryf = "SELECT * FROM codes WHERE codes.number LIKE '$Numberx%'"; 
	 	 $resultf = mysql_query($queryf) or die(mysql_error());
		while($row = mysql_fetch_array($resultf)){
				$Idx=$row['Id'] ; //Get ID
				$count=0;
			}
                 }

My problem is that the numbers on the table don`t have the same length, another problem It's that if the number on the line doesn't match any number on the table is has to take an ID (wild card), for example if the number on the linea is: 00187123456565, this number has to take ID 102.

Any Ideas?

 

[hvass]

Matching records is what a database does so your approach to read a line from a file then form a sql query, submit the query, read the result is a bit of long way around.

Depending on the relative size of the fopen file vs the database table

Either
load your file (that you were reading line by line) into a database table
do a database match using sql script to generate a results table
issue a single php query to retrieve the results

Or
Read the whole database table into php array
Use regexpressions to match input file row to php array

Looking at your example am I correct in my guess these are something like telephone numbers and what you mean by 0018X 102
is you want it to take a value of 102 if number starts 0018* but does not match exactly 001809 or 001869 or am I over complicating this? This is less easy not impossible to do in pure SQL (i.e. without regexp support).

If you are interested in doing this in sql then post some 'real' example data as sql statements that anyone can just pick up and run to create some test data - so write the
create table
insert into
statemnets and people will be more able to help.