Alphabetically Browse Selection

kona72

Hi All,

I have on my site an area where it allows you to search the database alphabetically for the product you want.

Here is my query...

$query_Recordset1 = sprintf("SELECT * FROM products WHERE description LIKE '%s' ORDER BY description ASC", $colname_Recordset1);

I can't get it to return result even though I have 350 products to browse...

Here is table structure...

Field Type Null Default
ID int(11) No

prodnum double Yes NULL

description varchar(255) Yes NULL

quantity varchar(255) Yes NULL

genericdescription varchar(255) Yes NULL

price

HELP??

Thanks in advance!!!

bpat1434

well, perhaps you should have your query be:

 $query_Recordset1 = sprintf("SELECT * FROM products WHERE description LIKE '\%%s\%' ORDER BY description ASC", $colname_Recordset1);

kona72

No Dice...
I get...
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''%sRDER BY description ASC' at line 1

My big conundrum here is I am unsure as to what the %s variable is. I didn't create this db I am just taking over for the last guy...

Sxooter

Do any of those products end in the letter s?

kona72

I'm sure a few do, but i am not sure that is the problem.
I looked up the %s

% - a literal percent character. No argument is required.
s - the argument is treated as and presented as a string

I have tried this

$query_Recordset1 = sprintf("SELECT * FROM products WHERE description LIKE '%%%s%%' ORDER BY description ASC", $colname_Recordset1);

but now i get ALL the products not just the letter I had selected.... AArrgghh!!!

bpat1434

Why use sprintf? Why not just:

$query_Recordset1 = "SELECT * FROM products WHERE description LIKE '%".mysql_real_escape_string($colname_Recordset1)."%' ORDER BY description ASC";

kona72

Wellll.....
It kinda works. I get the whole db again. I need it to just be able to select a, b, c and so on...
This is troubling.....

bpat1434

well, if you're searching for just a letter, like where the first letter is A, but nothing else, use this:

 $query_Recordset1 = "SELECT * FROM products WHERE description LIKE '".mysql_real_escape_string($colname_Recordset1)."%' ORDER BY description ASC";

The % is a wildcard character in mySQL searches. So that query should match anything that fits A*

Understand?

kona72

I don't think I am supposed to post urls but here i go for this example.
www.canadianprescriptiondrugstore.com the alphanbet on the right side of the screen is where i am running into problems.
It calls search_alpha.php with this command (/search_alpha.php?search=a) for every letter of the alpha bet. The we get in to the query form above....

kona72

I have been looking at the code and the search_alpha.php is right in the root folder yet when it is called it is ../search_alpha.php any issues??

bpat1434

The calling of it wouldn't be an issue. The issue is in your query.

Try running this query in phpMyAdmin:

SELECT * FROM products WHERE description LIKE 'A%'

Then try:

SELECT * FROM products WHERE description LIKE '5%'

The first should return a whole bunch, but the second, something like:

   5-ASA (ASACOL GENERIC) 400MG 100 NOVO    	100  	$57.90
   5-ASA (ASACOL GENERIC) 400MG 200 NOVO  	200 	$116.90
   5-ASA (ASACOL GENERIC) 400MG 300 NOVO  	300 	$173.90
   5-ASA (ASACOL GENERIC) 400MG 600 NOVO  	600 	$343.90
   5-ASA (ASACOL GENERIC) 400MG 900 NOVO  	900 	$509.90

Do the results come out fine? If not, you may have to turn on search indexing for that column.

kona72

Nothing and...nothing..

how do I turn on indexing??

bpat1434

In phpMyAdmin, if you browse the structure of the products table, you should see in the far right column 4 little icons after the red X:

Key: Primary Key
Lightning bolt: Index
U: Unique
T: FullText

Just click the "T" image and it should do it. Then try the queries again.

kona72

Does this have any bearing on the problem???

Indexes : Keyname Type Cardinality Field
PRIMARY PRIMARY 3542 ID

description INDEX 3542 description

description_2 INDEX 3542 description

description_3 INDEX 3542 description

description_4 INDEX 1771 description

bpat1434

no... did you enable FullText? If not, do it. Then try the queries over again...

kona72

This is what the table look like... I have tried it again and still nothing....

Keyname Type Cardinality Action Field
PRIMARY PRIMARY 3542 ID

description INDEX 3542 description

description_5 FULLTEXT 1771 description