Query wih Like

iceman2g

I have a query that uses LIKE to get items with the same id, located in a given category.

Problem is that using LIKE I can only get so close in terms of accuracy.

Here's what I mean.
$id = 22

Now I have table in my database that has each unique id on a new line.
2
32
3
22

This is my query:

 $get= mysql_query "SELECT * FROM inventory where id LIKE '%$id%"

Only the first and last characters need to match in order for result to be returned, using this query.

By now you see that this has some problems. Mainly to do with the fact that 22 will return a result for items located in category 2.

I've tryed a few diffenrent ways to get this working but so far nothing i've done seems to work.

If there's some way I could include new line in that search for a lil bit more accuracy I think I could get it to work as i'd like.

Any help is appreciated.

nemonoman

are you saying that you store this data as single lines in a single field of the records of your database?

What database are you using? Relational and hierarchical databases have been the norm for 40 years -- why not try one of those?

Maybe you could download MySQL for starters.

Each of those "lines" belongs in a separate, related table. Then you can query like a champ.

mourisj

Of course you have each unique id in one row, that's how databases work

The query you show (apart some errors) should show exvery line where the id contains '22': with the data you showed, only id 22. But ids like 4897622578 or 122 or 2222 are also supposed to be returned.

This will not return the lines whrere the first and last character match! The first and the last characters can be ANY character (even none), that what the % means.

BTW, this query will not work for numerical IDs. You have to convert to character format the IDs if the field is a numeric field.

JJ Mouris

iceman2g

Sorry I wasn't clearer by uniqe id I refer to the category id. But i've added a new table that stores several different category id.

I've done this so a given item can be placed in several categories.

The table is set using tinytext so that each category id is on a new line.

Also about the Like % sorry about that I just realised that only the first numbers have to match.

iceman2g

Sorry bout that.

Ok $id is reference to a category id (in our case 22). This is unique in that there will only be one category with this id. Each item at current can be assigned one category id. To get around this i've added a new table (sec_cat) that stores several category id based on users selection. Each category id in this new table is on a new line (\n)

i.e.
2
32
3
22

Now when you visit that category i'm using LIKE to search this table for matches that correspond to the category id ($id).

So basically if you visit category 22 and there's 22 present in the new table (sec_cat) then that item will be displayed.

What I want to do is search each new line individually so to get exact matches.

mourisj

I think I don't understand your problem right now.

If you want to have an exact match, don't use "like"; use "=".

You can use "like" only with character IDs (which is usually not a good choice). Like does not work with numbers.

JJ Mouris