PDA

View Full Version : alphabet query??


kona72
01-25-2006, 09:45 PM
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!!!

desertdirk
01-25-2006, 11:09 PM
Can you offer some more details? How is the search done? how are the search results suppose to display. When you say it does not return any results, what is happening?

instead of using a variable %s, try a constant to see if that returns any results

WHERE description LIKE "apple"

kona72
01-26-2006, 02:28 AM
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....

Creative Insanity
01-26-2006, 03:14 AM
Yeah I see what you have done wrong. I will do something here and then post the code with an explianation of what I am doing.

kona72
01-26-2006, 05:58 PM
Hi CI,

Anything on this yet?? I am still quite stumped?!?!

Creative Insanity
01-26-2006, 06:51 PM
Sorry got tied up yesterday will have alook today.

kona72
01-26-2006, 07:04 PM
you're a lifesaver!!!! :P

desertdirk
01-27-2006, 12:14 AM
kona
on your search results page (search_alpha.php) what is the var $colname set to?

kona72
01-27-2006, 12:20 AM
Here is the search_alpha.php code...MAN!! Why didnt' I post this sooner??

<?php require_once('Connections/prescription.php'); ?>
<?php session_start();?>
<?php
$colname_Recordset1 = "1";
if (isset($_GET['search'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset1 = sprintf("SELECT * FROM products WHERE description LIKE '%s' ORDER BY description ASC", $colname_Recordset1);
$Recordset1 = mysql_query($query_Recordset1, $prescription) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

Creative Insanity
01-27-2006, 12:47 AM
I am doing this VTM next.. patiance my son, patiance.

desertdirk
01-27-2006, 01:36 AM
Kona
try this in your select statement:
SELECT * FROM products WHERE description LIKE '%s%%' ORDER BY description ASC", $colname_Recordset1

notice the additional 2 %% after the LIKE

Don't know if this makes a difference but you are using the parameter name "search" for both the search box and the alpha-list. You may want to change that to something like "searchalpha"
dd

desertdirk
01-27-2006, 03:08 AM
And on further thought
you will need two different record sets if you want your search box to search differently than the alpha search. each record set will need a different LIKE statement

They way you have it now, both searches use the same record set and will preform the same
dd

kona72
01-27-2006, 03:31 AM
Something like this??

<?php require_once('Connections/prescription.php'); ?>
<?php session_start();?>
<?php
$maxRows_Recordset1 = 100;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "1";
if (isset($_GET['search'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset1 = sprintf("SELECT * FROM products WHERE description LIKE '%%%s%%' OR description = '%s' ORDER BY description ASC", $colname_Recordset1,$colname_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $prescription) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$colname_Recordset2 = "1";
if (isset($_GET['search'])) {
$colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset2 = sprintf("SELECT * FROM products WHERE genericdescription LIKE '%%%s%%' OR genericdescription = '%s' ORDER BY description ASC", $colname_Recordset2,$colname_Recordset2);
$Recordset2 = mysql_query($query_Recordset2, $prescription) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>

desertdirk
01-27-2006, 02:53 PM
First
what do you want people to search on using the search box? If you intend to have them enter a drug name, then you would use = instead of LIKE.

What is the difference between genericdesciption and description?

you have too many % in the LIKE statement. It should be %s%%.

post an answer and I will work on it
dd

kona72
01-27-2006, 03:13 PM
Hi DD,

The search box is intended for people to search drug names like you said.

Description is the actual drug description whereas generic description is the form (ie: capsule, liquid)

I will try the %% fix right now...


BTW...The search by name function seems to work just fine.

kona72
01-27-2006, 03:19 PM
Ok i have changed the %% to your suggestion and now something very wierd is happening. When I click on the A i get ALL the products in the db. When I click on any of the other letters...nothing.

desertdirk
01-27-2006, 04:19 PM
ok
2 record sets. One for the box and one for the alphabet:
first for the search box:

$colname_Recordset1 = "1";
if (isset($_GET['searchbox'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['searchbox'] : addslashes($_GET['searchbox']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset1 = sprintf("SELECT * FROM products WHERE description = '%s' ORDER BY description ASC", $colname_Recordset1);
$Recordset1 = mysql_query($query_Recordset1, $prescription) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


I changed search to searchbox and I changed LIKE to =
you will need to change your parameter from seach to searchbox

Second record set for the alpha


$colname_Recordset2 = "1";
if (isset($_GET['search'])) {
$colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset2 = sprintf("SELECT * FROM products WHERE description LIKE '%s%%' ORDER BY description ASC", $colname_Recordset2);
$Recordset2 = mysql_query($query_Recordset2, $prescription) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);



Doing it this way you won't have to change your parameter for the links for the alphabet. You have 2 different $_GET 's. one is $_GET['searchbox'] and one is $_GET['search']

the search box looks for an exact match when a drug name is entered. The links to the alphabet look for a match beginning with the letter selected.

dd

kona72
01-27-2006, 04:29 PM
now the search by word function doesn't work....
Oh my...

desertdirk
01-27-2006, 05:21 PM
post your code that you have now
dd

kona72
01-27-2006, 05:25 PM
Thanks so much for all this help. I think i would have thrown up a few times by now if it wasn't for all the help recieved by this board.

Here's the code now....


<?php
$maxRows_Recordset1 = 100;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "1";
if (isset($_GET['searchbox'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['searchbox'] : addslashes($_GET['searchbox']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset1 = sprintf("SELECT * FROM products WHERE description = '%s' ORDER BY description ASC", $colname_Recordset1);
$Recordset1 = mysql_query($query_Recordset1, $prescription) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$colname_Recordset2 = "1";
if (isset($_GET['search'])) {
$colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset2 = sprintf("SELECT * FROM products WHERE description LIKE '%s%%' ORDER BY description ASC", $colname_Recordset2);
$Recordset2 = mysql_query($query_Recordset2, $prescription) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>

desertdirk
01-27-2006, 06:18 PM
ok
looks like you added a new page- seach-alpha.php. What is the code on that page for the select?
dd

kona72
01-27-2006, 06:28 PM
The search_alpha page has always been there....
That was the first code i posted...

<?php
$colname_Recordset1 = "1";
if (isset($_GET['search'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset1 = sprintf("SELECT * FROM products WHERE description LIKE '%s' ORDER BY description ASC", $colname_Recordset1);
$Recordset1 = mysql_query($query_Recordset1, $prescription) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

desertdirk
01-27-2006, 06:30 PM
For the search box you did not change "search" to "searchbox" in the form tag when you submit the form. look at the URL after you type in drug name and it will show:
http://www.canadianprescriptiondrugstore.com/search.php?search=Crestor&submit=GO

now change search to searchbox in the url and it will list everything.

Creative Insanity
01-27-2006, 06:34 PM
This vtm is coming today. Just be patiant.. I have other things to do also.

desertdirk
01-27-2006, 06:36 PM
ok
I am sorry I didn't catch that it was a seperate page. This is what you want on your search_alpha.php page:


$colname_Recordset2 = "1";
if (isset($_GET['search'])) {
$colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['search'] : addslashes($_GET['search']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset2 = sprintf("SELECT * FROM products WHERE description LIKE '%s%%' ORDER BY description ASC", $colname_Recordset2);
$Recordset2 = mysql_query($query_Recordset2, $prescription) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);


And you only want the first record set on your search.php page:


$colname_Recordset1 = "1";
if (isset($_GET['searchbox'])) {
$colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['searchbox'] : addslashes($_GET['searchbox']);
}
mysql_select_db($database_prescription, $prescription);
$query_Recordset1 = sprintf("SELECT * FROM products WHERE description = '%s' ORDER BY description ASC", $colname_Recordset1);
$Recordset1 = mysql_query($query_Recordset1, $prescription) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);




so you should only have one record set in each page
dd

kona72
01-27-2006, 06:39 PM
Hi CI,

I am anxiously awaiting; not trying to add extra pressure just been posting to see if I can figure it out. You know how it is when something gets under your skin....

Thanks so much!!

desertdirk
01-31-2006, 02:48 PM
Kona
looks like you got it all squared away
dd

kona72
01-31-2006, 03:03 PM
Yeah.... Not a fun weekend for me. Learned alot though!!
Thanks again for all your help!

Creative Insanity
01-31-2006, 05:25 PM
The VTM is done click on the logo and then on the VTM link and look for one called 'Conditional Links'

kona72
01-31-2006, 08:49 PM
Hey CI,

Thanks for the VTM. I'm sure i'll have a question or two once i get a chance to try it out.

Thanks again!!

Creative Insanity
01-31-2006, 11:21 PM
Welcome.