Numbered products not showing in correct order, also affecting search results

The store does not list numbered products in actual numerical order. It reads each character instead of recognizing the whole thing as a number.

I am selling baseball cards and each card has its own individual card number. If I were to just list them as #1, #2, #3....etc., the product listings that customers see will start from #1 and then jump to #10-19, then to #100-199. Only after all of the numbers beginning with 1 are shown, does the site start listing #2, #20-29, #200-299, and so on for all numbers. I realized that this was an issue early on, mostly because I use Excel and it is the same way there. I eliminated this incorrect ordering by making the first card #001 instead of #1. If the set is larger than 999 cards, I have to make #1 into #0001. It works this way, but I am not listing the true card numbers for customers to search for. It also takes a lot of time to adjust all of my numbers in each spreadsheet by adding 0's instead of just using them the way that should be listed. Is there a way to fix this?

Just in case someone doesn't understand what I am saying above it looks like this:

#1
#10
#11
#12
#13
#14
#15
#16
#17
#18
#19
#100
#101
#102
#103
#104
#105
#106

So I have to do this to get it to show up in the correct order:

#001
#002
#003
#004
#005
#006
#007
#008
#009
#010
#011
#012
#013
#014
#015
#016
#017
#018

I hate doing this because these are not the true card numbers and it is affecting search results tremendously. When a customer searches for card #1, it brings up any cards with a number starting with #1. But it does NOT bring up the actual card #1, because I have to list it as #001. The search engine only recognizes the exact characters entered. I also know that the # does make some difference right now, but it would not if I could list these in the correct order. Fixing this would help me out tremendously and save me significant amounts of time when adding future products.

Zen Cart use alphabetic orders to fetch the Title. You want to look into the page which you have the problem with and remove the function that sort the items in alphabetic order hence 1, 10, 11 and so on, the database is set to treat the title as text not integer.

You would have to do some digging in the function and header files. I have never faced that problem but if your lucky one of the official Zen Support would have a straight answer to you.

So, am I looking to change something in Zen Cart and the database, or will changing one of them actually change them both?

We can also just wait for one of the "Officials" to swing by this thread and we will test their knowledge on the subject.

In other things that I have messed with, I remember that having either alpha or numbers set for the format didn't make a difference. But I am sure that it wasn't Zen Cart, so that probably won't matter for this issue. We'll see if someone else knows more details about the location of this function.

I am not an expert or a developer so my knowledge in pure trial and error. But what happens if you change the product_name type in the database to TEXT instead of VARCHAR???

That is a great question. I don't have the answer to it. Should one of us change it and see if it makes a difference, or just wait for someone else that does have the answer for sure?

I am tempted to try it, but I don't want to screw up my live store to find the answer to something that is not a certain fix. Even if we changed it from VARCHAR to TEXT, would that make a difference with my numbering?

Are there any other things that we can change that to instead of just VARCHAR or TEXT?

BenhamCollectibles:

So, am I looking to change something in Zen Cart and the database, or will changing one of them actually change them both?

We can also just wait for one of the "Officials" to swing by this thread and we will test their knowledge on the subject.

In other things that I have messed with, I remember that having either alpha or numbers set for the format didn't make a difference. But I am sure that it wasn't Zen Cart, so that probably won't matter for this issue. We'll see if someone else knows more details about the location of this function.

I have the same issue with Scott Catalog Numbers. What I did for an easy fix was to insert a space (instead of the 0's that you mentioned). My largest SC# is 4 digits so a single digit # gets 3 spaces, tens digits get two, etc. Just modify as needed. Only the 1st space shows on the product listings, so it really doesn't look terribly bad. To see how this looks, click on my site link below.

I use EP to upload all of my products, so editing the text file is not too big of a hassle. Manually, that might be a different animal!