mysql query help

[jannoy]

hi guyz just want to ask. Im having trouble in my sql query.

How can i extract data that is only needed to show ?

my table structure :
table: mytable
field : category (varcar (10))
: name (varcar (100))

Value of mytable
category name
1|3|6 what
2|11|15 test
<?
// Here is my query
$query="Select * from mytable where category like '%1';

// when i use this query it display both

?>

What will be the right query (by not using explode("|",category)?

[BuzzLY]

put pipes around the outside of your category, too:

|1|3|6| what
|2|11|15| test

Then have your query search for "|1|" which will not match 11 or 15. This is a direct answer to your question.

However, a more indirect question would be why do you have multiple data in one field in your database? This is not a best practice. You need to normalize your data.

[swr]

Having "1|3|6" in a single column to associate the record with multiple catagories is not the right way to do it. That is why you are having problems now. Even if you can get your "like" query to do what you want, you won't be able to make use of indexes in your query, so your DB will have to scan the whole table.

This seems to be a fairly common mistake for people new to relational databases.

Here is an article on this very site:
http://www.phpbuilder.com/columns/barry20000731.php3

For more info, Google for "database normalization":
http://www.google.com/search?q=database%20normalization

It may seem more complicated, but once you get used to it (and the SQL queries over multiple tables) it's actually easier. It's also faster, because your DB can make use of indexes. Speed-wise it can be a huge difference on large tables (analogy: looking up someone's phone number in a phone book sorted by name (do a "binary search"), verses looking it up in a completely unsorted phone book (go over each entry until you find the right one)).

[jannoy]

hey guyzz thank you very much for the info
i should have | pipe in between the number

got the problem and solved already