Searching by characters

[Alicia]

Hi,

 

Can some gurus give me an idea how I can accomplish the following with a mysql query in php?

 

I have a column with `numbers` (5 int)

when the user submit numbers maybe 3 digits, the query will check the database for the numbers columns above and return number of records found (permutation allowed)

e.g :

 

User submitted 251

 

The query should count 1 for each of the following :

12512

24451

12577

15211

and etc... as long as the record has 2, 5, and 1 digit or any integer submitted by users, it should count as one.

 

Can somebody please advise how can I do such character search with php mysql?

 

Thank you.

 

 

 

[Alex]

Here's an example:

 

$input = '251';
$sql = "SELECT COUNT(id) FROM table WHERE column LIKE '%" . implode("%' OR '%", str_split($input)) . "%'";

 

I misunderstood what you were asking. Instead try something like:

 

$input = '251';
$sql = "SELECT COUNT(id) FROM table WHERE column LIKE '%" . implode("%' AND '%", str_split($input)) . "%'";

[Alicia]

how can I echo the number of records found by using the query above? Using mysql_num_rows()?

 

PLease advise and thanks

[Alex]

Once you changed the table and column name to yours you would just do:

 

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
echo $row['COUNT(id)'];

[Alicia]

it returns 0, =(

 

the count(id) , id is the column name ? can i chnage it to count(*) ?

[Alex]

Yea, or just changed it to COUNT(numbers) and $row['COUNT(numbers)'];

[Alicia]

Not working. It shows 0 even the record has the input entered.

[Alex]

Can you post your code?

[Alicia]

Now I use the query with OR and it shows the count as long as 1 digit matches the whole number but that is not what want. We need it to count as 1 only if all digits submitted is in the number. I tried the other AND query you proposed and it returns 0 even the number is there.

 

e.g :

submit number : 123

the value fetch from a column 0001 is counted as one in the 'OR query' you proposed since there is 1 in the number. But we dont want this to be counted in since the integer 2 and 3 are not in the number 0001 fetched.

 

Please advise. Thank you.

[JustLikeIcarus]

Change the code to:

 

$sql = "SELECT COUNT(id) FROM table WHERE column LIKE '%" . implode("%' AND column LIKE '%", str_split($input)) . "%'";

 

See if that works.