Lack of persistence in query

Hello everyone. I am in great need at this moment. My deadline for completion has come and gone while I am stumbling over a ridiculous script debugging problem. If I can't find it, I cant' fix it!!

I have used PHP to script search functions of a catalog DB using MySQL as the backend. There are three search functions: 1- category search using a select-option menu; 2- an individual item search by item number; 3- a sub-category search using radio buttons to key the value of the sub-category search.

All functions work as intended, except for the final presentation/pagination of the search results by way of the radio buttons.(This pagination does work for the category search.) The first page of search results appears as expected with page links at bottom to move to next page display. On clicking the page 2 link I get the log error below.

I have set up the page so as to be viewed by someone interested in helping. The page has echo statements to trace the value of the selected radio button, as well as statistics displayed regarding SQL queries and results.

If you think you can help please follow this link:
http://www.sarahcavender.com/Catalog/Search/find2.php
Temporary user/password: sarah/sarah

Apache error log reporting:
[Wed May 8 08:52:40 2008] [error] PHP Warning: mysqli::query() [<a href='function.mysqli-query'>function.mysqli-query</a>]: Empty query in /Library/WebServer/Documents/dsarahcavender.com/Catalog/Search/mydb.php on line 67

Regards to all,
Photofio

I was going to see if I could help, but I could not access the site.

I tried:
username <blank>
pwd: guest

username guest
pwd guest

username guest
pwd <blank>

username photofio
pwd guest

My fault, obviously.
user:sarah
pass:sarah

OK I can offer some observations.

1. The sql created from the special search has a basic flaw in it:

WHERE bug_id = 1 LIKE '%' ORDER BY item_id LIMIT 0,39

I would think this would give some sort invalid sql error.

2 Actually you have an error in the category search as well. With the category selection the limit is 0,10 and shows a total of 2 pages. However if you click on page 2 you then get an option for page 3. If you click on page 3 you then get an option for page 4. etc.

That's what this second pair of eyes came up with. <grin>

Hope it helps!

dykebert, thanks for taking the trouble to look.....

Well, your not wrong in the observation; the display of images is limited spacially to nine (3 table rows of three images); this is enforced by a $pagesize variable with a value of 9, and a query limit of $size(9)+1.
Because of the limit, the query only returns 9+1 rows at a time.

I hard-coded the query limit in special search to reflect the known number of pages available for that radio button criteria, and have not done so for the category search.(Actually, can not do so, given the way the code is written.) That is the difference there.

What I cannot explain or track down is why the special search query seems to disappear (no rows returned) after clicking the href link for page 2????? and the variable values for the radio buttons disappear as well.

I believe I have traced all the relevant values, the href for pg 2 is properly coded as a result of the initial query, but.....then?????

I'd be happy to send you the code involved!

photofio

OK I get the limit part.

Have you tried fixing the SQL syntax error?

WHERE bug_id = 1 LIKE '%' ORDER BY item_id LIMIT 0,39

It should either be:

WHERE bug_id = 1 ORDER BY item_id LIMIT 0,39

or based on your other queries

WHERE bug_id = 1 AND item_id LIKE '%' ORDER BY item_id LIMIT 0,39

The only other thing I noticed is that the very first line of the sub cat searches has mixed string

1flower_id3flower_id

Where as the ones that run properly have just integers

1

As you've probably already noticed the SQL for page 2 is never displayed but you do just get the integer like in the working queries.

Other than the above issue with the sql the rest of the queries look OK, which makes me think there's something in the code that is the issue.

probably not much help.

I did fix the sql syntax; you were correct. It should be:
WHERE bug_id = 1 AND item_id LIKE '%' ORDER BY item_id LIMIT 0,39
---no discernible difference, and no difference in terms of the full pagination functionality that I'm looking for.

The mixed string you refered is in the following statement:
echo '2',($searchSpecial_option); //the value of the selected radio

I added the numerals in an attempt to keep track of where the value was in the code sequence. There is no value for 'searchSpecial_option' until the search button is clicked and functions called.

So... when a radio button is selected and the search special button is pressed, searchSpecial_option has persistent value through the code sequence and a number of rows are returned. At least initially---- everything is properly valued until after the href for page 2 is triggered. Notice that as the cursor goes over the link for pg 2, the status bar indicates link values: ?bug_id = 1&etc&etc... Then after activation all that disappears. Damn!

If it is in the code rather than query structure--I suppose it could be placement, where in the code sequence something is placed.

Dykebert, thanks for your effort,

photophio

You posted the GET vars as "etc&etc", but the link is
http://www.sarahcavender.com/Catalog/Search/find2.php?flower_id=1&searchSpecial_option=&specialPattern=%25&page=2
The header looks correct
GET /Catalog/Search/find2.php?flower_id=1&searchSpecial_option=&specialPattern=%25&page=2 HTTP/1.1
Since "%25" is the urlencoded percent sign, I don't know where the urlencoding is coming from (you coded it, or the browser is doing it), and it may be that you are passing it to use in the query, and it may even be OK in the query (I didn't look at view-source to see if your rendered debug line was % or %25), but I don't think I would pass a wildcard anyway since you don't need it here.
From the MySQL documentation

This is fine for something like %cane or sugar% or %garca%,
but having a query like "LIKE %" is unneccessary as you don't need a "like everything".
Try passing a value like "all" and using a conditional "if" to assign the query.

Mittineague, Thanks for your help

The following is a cut and paste of code for relevant aspects:

<code>
//this is the call to basic functions and queries that build a presentation page based on a radio button value

if($specialPattern || $specialID || $searchSpecial_option) {

$sql = build_special_query($specialPattern, $specialID, $searchSpecial_option, $page, $pagesize);
$rows = $db->queryObjectArray($sql);

show_special($rows, $pagesize);

//this is the query used to populate the hrefs for pagination of results
$query = "$searchSpecial_option=1&searchSpecial_option=$specialID&specialPattern=" . urlencode($specialPattern);
show_page_links($page, $pagesize, sizeof($rows), $query);

echo '<p class="hText" align="center"><a style="color:#A0522D;" href="find2.php">Back to search form</a><br /><span style="font-size:9px;color:#000000;">Mouse over image reveals item details.</span></p>', "\n";

} // end if($specialPattern

else {

echo "<p class=\"searchHeader\">Search catalog by category or item number<BR /><span class=\"hText\">Select a category or enter an item number into the text box.</span></p><BR /><BR /><BR /><BR />\n";


//this function called above in if($specialPattern on basis of searchSpecial_option defined by radio button value
function build_special_query($pattern, $specialID, $searchSpecial_option, $page, $size) {
global $db;

if ($searchSpecial_option == 'flower_id') {

$flower_id_status = 'checked';
// get all flowers
$sql = "SELECT item_id, item_category, flower_id, item_description, item_price, item_file_name FROM sc_item
WHERE flower_id = 1 AND item_id LIKE " . $db->sql_string($pattern) .
" ORDER BY item_id ";
$sql .= "LIMIT " . (($page-1) * $size) . "," . ($size + 162);
return $sql;
$rows = $db->queryObjectArray($sql);
// build array:

foreach($rows as $row) {
$searchSpecial_option[$rows->flower_id] = $row->item_id;
}
} //end if selected_radio flower id


//this function called above in if($specialPattern ||
function show_special($special, $pagesize) {
global $db, $searchSpecial_option;

echo "<p class='searchHeader'>Search special results:</p>\n";
if(!$special) {
echo "<p class='searchcr'>Sorry, no items found.</p>\n";
return;
}

// build comma-separated string with specialIDs as special
$items = min($pagesize, sizeof($special));
for($i=0; $i<$items; $i++)
if($i==0)
$specialIDs = $special[$i]->item_id;
else
$specialIDs .= "," . $special[$i]->item_id;

// build comma-separated string according to $searchSpecial_option variable
$sql =
"SELECT item_id, item_category, $searchSpecial_option, item_description, item_price, item_file_name " .
"FROM sc_item " .
"WHERE $searchSpecial_option = 1 AND item_id IN ($specialIDs) " .
"ORDER BY item_id";
$rows = $db->queryObjectArray($sql);

// show all specialIDs in 9 predefined DIV elements
foreach($specialrows as $special) {

if($special->item_id)
$original = $special->item_description;
$lower = strtolower($original);
$first = ucfirst($lower);
echo spdiv("<a href=\"javascriptnclick=window$special->item_id();\" class=\"addToolTip2\" title=\"<div id='ToolTipTextWrap2'>Item Details</div> <p class='ev16'>Description:&nbsp;$first
<br />Item price:&nbsp;$special->item_price
<br />Category:&nbsp;$special->item_category
<br />Item number:&nbsp;$special->item_id</p>\"><img src=\"$special->item_category/$special->item_file_name\" width=\"166\" height=\"166\" border=\"0\" alt=\"specialSearch\" /></a>");

} //end foreach

} // end of show_special

//resulting href page link---
//"http://www.sarahcavender.com/Catalog/Search/find2.php?flower_id=1&searchSpecial_option=$specialPattern=%&page=2"

function show_page_links($page, $pagesize, $result, $query) {
global $db, $searchSpecial_option;

if(($page == 1 && $result <= $pagesize) || $result == 0)
return FALSE;

echo ("<p class=\"searchcr\"><span style=\"color:#a0552d\">More results</span>, go to page: ");
if($page>1) {
for($i=1; $i<$page; $i++)
echo build_href("find2.php", $query . "&page=$i", $i); " ";
echo (" | " . $page . " | ");
}
if($result > $pagesize) {
$totalPages = ceil($result/$pagesize);

$nextpage = $page + 1;
echo build_href("find2.php", $query . "&page=$nextpage", $nextpage); " ";
echo ("&nbsp;|&nbsp;Total pages: " . $totalPages . "</p>" );

}
}
</code>

Perhaps this is to much info--don't know what else to do as way of providing the larger code-view; the only things missing are the three additional queries for three additional radio buttons(coded in same fashion) and the form itself---that would be to much here. I'm hoping this explains the use of wildcard '%' as part of pattern matching, and may help identify more clearly where the breakdown in code is.

Link generated by show_page_links function:
"http://www.sarahcavender.com/Catalog/Search/find2.php?flower_id=1&searchSpecial_option=$specialPattern=%&page=2"

photophio