are indexes screwing up my tables?

[Davidc316]

Hi hi,

I have a MySQL database that contains a table called store_items.

One of the columns on this table happens to be a column called "category".

When I issue a MySQL search which says "select * from store_items where category='whatever'".... it only ever produces 1 solitary row as the result.

Even if I've got a million items with the category "whatever" it only displays one row every time.

Can anyone explain why this is so?

[paulnaj]

Show us the piece of code that causes the offence!!

[Davidc316]

I appreciate that kind offer, but before I bombard you with loads of code, can I share some more infomation about the problem?

Ok, let's imagine I'm trying to record the details of an item.

Let's imagine that the item has an id of "32" and is in a category called "whatever category". Ok, now when I go into the MySQL command line and execute the command "select category from store_items where id=32", then I get some strange results.

Sometimes MySQL will say that's it has found the following row...

+-------------------+
| whatever category |
+-------------------+

That's fine and it works ok.

But there are other times when MySQL will produce the following result...

+-------------------------+
whatever category|
+-------------------------+

The important thing to note here is the wonky verticle lines!

So, if I have more that one item in the category of "whatever category", then the records which are contained within a wonky looking table will NOT show up if I later do a search to display all from the category of "whatever category".

Does that make sense?

[ctimmer]

Looks like you may have nulls in your data.

Try " category like 'whatever%' " and see if you get better results.

If so, some data scrubbing will fix the problem.