I have this search box.
If it gets posted displays one of 2 results .
There are 2 queries. One if there is no errors.
This shows the cols in the parent table and related table.
HOwever, if there is no match the record is flagged with a 1 in error_flag.
The match key needs to be Updated so I can get the data in the related table.
I did this over using SQL_CALC_FOUND_ROWS.
IT works on the first page but dies if you hit any of the pages in the limit.
I think I know why but I don't know what the solution is.
This is the correct query on the first page. It gives me the right count of 1092
SELECT SQL_CALC_FOUND_ROWS bu, cart_id, nationalDrugCode, error_flag FROM cart_inventory_complete WHERE bu ='08174' AND error_flag='1' ORDER BY bu LIMIT 0 ,25
The rows total :1092
There is a limit of 25 as you can see so when I click 30 for example you can see the result in the die statement. It still gets the right page display but it tries to get the result from the
SELECT SQL_CALC_FOUND_ROWS bu, cart_id, nationalDrugCode, error_flag FROM cart_inventory_complete AND error_flag='1' ORDER BY bu LIMIT 625 ,25
It leaves out the WHERE clause. Also I have a question about the error_flag.
The default in the search form is error_flag=0. But do I need to add the error_flag to the GET in the page numbers at the bottom? The error_flag=1 on the search should stay posted from the query so the query should stay the same?
I think it is almost working since I have the right count but the wrong query on the 2nd page.
thanks,
$page=$_GET['page'];
echo "PAGE: ". $page;
//search box, with criteria, bu, nationalDrugCode, zonenm, zoneid, error_flag defaults to 0
if ( !$_POST && $_GET['page']=='') { die();} if there are no posted values display form only for user to post
//there is no matching ndc in onelink
if ($error_flag==1) {
echo "Error Flag: ".$error_flag."\n";
$sql="SELECT SQL_CALC_FOUND_ROWS bu, cart_id, nationalDrugCode, error_flag ";
$sql.="FROM cart_inventory_complete ";
if ($bu!=''){ $sqlWhere="WHERE bu ='$bu' ";}
if ($cart_id !='') { $sqlWhere.="AND cart_id='$cart_id' ";}
if (($zoneid !='') && ($zonenm!='')) { $sqlWhere.="AND zoneid ='$zoneid'AND zonenm='$zonenm' ";}
if ( $error_flag !=''){$sqlWhere.="AND error_flag='$error_flag' ";}
$sqlOrder="ORDER BY $order ";
$sql.=$sqlWhere;
if ($page==''){$page =1;}
$start_page= ($page * 25)-25;
$sqlLimit= "LIMIT " . $start_page ." ,25 ";
$sql.= $sqlOrder ." " .$sqlLimit ;
//die($sql);
$result = mysql_query($sql);
$sql = "SELECT FOUND_ROWS() AS `found_rows` ";
$rows = mysql_query($sql);
$rows = mysql_fetch_assoc($rows);
$noRecs = $rows['found_rows'];
echo "The rows total :" .$noRecs;
//TABLE display with HEADERS and WHILE LOOP with ROWS
echo $noRecs ."No Recs";
$noPages=$noRecs/25;
//$noPages=$_SESSION['recs']/25;
for ( $p=1;$p<=$noPages;$p++ ) {
echo " <a href='$me?page=$p&error_flag=$error_flag'>$p</a>\n";
}
}else{
