abandonship

appreciate this... I haven't been able to find any tutorial or article on a search with several parameters. a search without any of the form parameters selected works fine, when I try to create a dynamic query string on the fly based on user input... well... thats why I'm asking for help... maybe there is an easier way... I have a lot of debugging flags included... hope they help. the search_results.php is as follows: [code] <?php session_save_path("/nfs/cust/1/21/55/555121/tmp/session"); session_start(); include('../includes/test_shared_functions.php'); include('includes/admin_functions.php'); print("<a href='search.php'>Return To Search Page</a>"); echo "<br>"; // Quote variable to make safe function quote_smart($value) {    // Stripslashes    if (get_magic_quotes_gpc()) {        $value = stripslashes($value);    }    // Quote if not integer    if (!is_numeric($value)) {        $value = "'" . mysql_real_escape_string($value) . "'";    }    return $value; } /////////////////////////////////////////////////////////// ///  CLASS PAGER  ///// /////////////////////////////////////////////////////////// class Pager   {              function getPagerData($numHits, $limit, $page)              {              $numHits  = (int) $numHits;              $limit    = max((int) $limit, 1);              $page     = (int) $page;            echo "inside function and page value is: ".$page."<br>";            $numPages = ceil($numHits / $limit);               echo "inside function and numpagesis: ".$numPages."<br>";            $page = max($page, 1);            echo "inside function and max page is: ".$page."<br>";              $page = min($page, $numPages);              echo "inside function and min page is: ".$page."<br>";              $offset = ($page - 1) * $limit;            echo "inside function and offset is: ".$offset."<br>";              $ret = new stdClass;              $ret->offset   = $offset;              $ret->limit    = $limit;              $ret->numPages = $numPages;              $ret->page     = $page;              return $ret;          }   } echo "Page number is: ".$page."<br>"; $_SESSION["PAGE_HITS"]++; echo "Total page hits are: ".$_SESSION["PAGE_HITS"]."<br>"; /////////////////////////////////////////////////////////////////////////////////// //////////////////// CREATE & APPEND GET RECORD COUNT QUERY /////////////////////// ///////////////////////////////////////////////////////////////////////////////////     if ( $_SESSION["PAGE_HITS"] == 1 ) { //only create query string the first time echo "Inside create page query <br>"; $schooldist = $_GET['schooldist']; $bedrooms = $_GET['bedrooms']; $baths = $_GET['baths']; $minprice = $_GET['minprice']; $maxprice = $_GET['maxprice']; if ( $schooldist == 'any' AND $bedrooms == 'any' AND $baths == 'any' AND $minprice == 'any' AND $maxprice == 'any' ) { $_SESSION["SQL_STR_PAGE"] = "select count(*) from PROPERTY";  //DO NOT ADD SEMICOLON! echo "<br><br>RECORD ***COUNT*** QUERY IN CREATE SECTION IS:<br />".$_SESSION["SQL_STR_PAGE"]."<br><br>"; }else { $form_fields = array_keys($_GET); $form_values = array_values($_GET);     $_SESSION["SQL_STR_PAGE"] = "select count(*) from PROPERTY WHERE ";              for ($i = 0; $i < (sizeof($form_fields)-1); $i++) {         $thisField = $form_fields[$i]; // key formfields[0] is schooldist         $thisValue = $_GET[$thisField];                                         //if( $thisField !="submit" ){//this doesn't work... why?         if ( $i >= 1 ) {         $_SESSION["SQL_STR_PAGE"] .= " AND ";         }                  $_SESSION["SQL_STR_PAGE"] .= " ".$thisField."='".$thisValue."' ";         echo "<br />".$_SESSION["SQL_STR_PAGE"];     } // end for      echo "<br><br>RECORD ***COUNT*** QUERY IN CREATE SECTION IS:<br />".$_SESSION["SQL_STR_PAGE"]."<br><br>";         }// end else } //end page hit test and creation of pagequery     /////////////////////////////////////////////////////////////////////////////////// ///////////////////////////// RUN QUERY - RECORD COUNT  /////////////////////////// ///////////////////////////////////////////////////////////////////////////////////     db_connect();         // get the pager input values       $page = $_GET['page'];       echo "Page at $_GET[page] section just prior to record count run is: ".$page."<br><br>";     $limit = 5;     //Run *Record*Count Query     $getrecords_str = $_SESSION["SQL_STR_PAGE"];     $getrecords_str .= ";";            //Append semicolon here, not in create section.  why???                         echo "<br><br>RECORD ***COUNT*** QUERY STRING JUST PRIOR TO RUN IS: ".$getrecords_str."<br><br>";     $recordcount = mysql_query($getrecords_str); // the page count query     $total = mysql_result($recordcount, 0, 0);        echo "Total num of records retrieved is: ".$total."<br><br>";     // work out the pager values       $pager  = Pager::getPagerData($total, $limit, $page);       $offset = $pager->offset;       $limit  = $pager->limit;       $page   = $pager->page;     echo "Offset after Pager Function is:".$offset."<br>Limit is ".$limit."<br><br>";          mysql_close();                /////////////////////////////////////////////////////////////////////////////////// ///////////////////////// CREATE GET PROPERTIES QUERY ///////////////////////////// ///////////////////////////////////////////////////////////////////////////////////     if ( $_SESSION["PAGE_HITS"] == 1 ) { //create query string first time only echo "Inside create property query <br>"; if ( $schooldist == 'any' AND $bedrooms == 'any' AND $baths == 'any' AND $minprice == 'any' AND $maxprice == 'any' ) { $_SESSION["SQL_PROPERTIES"] = "select * from PROPERTY";//DO NOT ADD SEMICOLON! echo "GET PROPERTIES QUERY STRING IN CREATE SECTION IS:  ".$_SESSION["SQL_PROPERTIES"]."<br><br>"; }else { $form_fields = array_keys($_GET); $form_values = array_values($_GET);     //property query $_SESSION["SQL_PROPERTIES"] = "select * from PROPERTY WHERE ";     for ($i = 0; $i < (sizeof($form_fields)-1); $i++) {//minus 1 to omit submit field         $thisField = $form_fields[$i];         $thisValue = $_GET[$thisField];                                     //if( $thisField !="submit" ){                                         //if( $thisField !="submit" ){//this doesn't work... why?         if ( $i >= 1 ) {         $_SESSION["SQL_PROPERTIES"] .= " AND ";         }         $_SESSION["SQL_PROPERTIES"] .= " ".$thisField."='".$thisValue."' ";         echo "<br />".$_SESSION["SQL_PROPERTIES"];              } // end for          echo "<br><br>GET PROPERTY QUERY STRING IN CREATE PROPERTY SECTION: ".$_SESSION["SQL_PROPERTIES"]."<br>";     }// else } // end pagehit test /////////////////////////////////////////////////////////////////////////////////// /////////////////////////////  RUN QUERY - GET RECORDS  /////////////////////////// ///////////////////////////////////////////////////////////////////////////////////     db_connect();     $str = $_SESSION["SQL_PROPERTIES"];     $str .= " limit $offset, $limit;";  //Note semi colon is required!     echo "GET PROPERTIES STRING JUST PRIOR TO QUERY RUN IS: ".$str."<br>";     $result = mysql_query( $str ); //Note: use $str here! NOT $getpages_str     if( !$result){      echo "Search produced no results.";      return; //return...      } // use $result here to output page content   print('<table width="750" border="1" cellspacing="0" cellpadding="0">');    while( $row = mysql_fetch_array($result) ) {          $vimage_name = $row['image_name'];     $vschooldist = $row['schooldist'];     $vbedrooms = $row['bedrooms'];     $vbaths = $row['baths'];     $vprice = $row['price'];      print("<tr>     <td width='75' align='center'><img src='../images/".$vimage_name."' width='75'/></td>     <td width='100' align='center'>$vschooldist</td>     <td width='50' align='center'>$vbedrooms</td>     <td width='50' align='center'>$vbaths</td>     <td width='450' align='left'>$vprice</td>   </tr>   ");     }  // end while loop      print('</table>'); //NOTE:  FORMAT PAGE LINKS IN A TABLE     // output paging system (could also do it before we output the page content)       if ($page == 1) // this is the first page - there is no previous page           echo "Previous";       else            // not the first page, link to the previous page           echo "<a href=\"search_results.php?page=" . ($page - 1) . "\">Previous</a>";       for ($i = 1; $i <= $pager->numPages; $i++) {           echo " | ";           if ($i == $pager->page)               echo " $i";           else               echo "<a href=\"search_results.php?page=$i\"> $i</a>";       }       if ($page == $pager->numPages) // this is the last page - there is no next page           echo "Next";       else            // not the last page, link to the next page           echo "<a href=\"search_results.php?page=" . ($page + 1) . "\"> Next</a>";   ?> [/code] The search.php form page is as follows: [code] <?php include('../includes/test_shared_functions.php'); include('includes/admin_functions.php'); session_save_path("/nfs/cust/1/21/55/555121/tmp/session"); if ( $_SESSION["PAGE_HITS"] >= 1 ) { session_destroy(); } session_start(); $_SESSION["PAGE_HITS"]=0; echo "page hits are:".$_SESSION["PAGE_HITS"]; print(" <form action='search_results.php' method='get'> School District: <select name='schooldist'> <option value='any'>Any</option> <option value='3'>3</option> <option value='11'>11</option> <option value='20'>20</option> <option value='49'>49</option> </select> <br><br> Bedrooms: <select name='bedrooms'> <option value='any'>Any</option> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> <option value='6'>6</option> </select> <br><br> Baths: <select name='baths'> <option value='any'>Any</option> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> <option value='6'>6</option> </select> <br><br> Min. Price: <select name='minprice'> <option value='any'>Any</option> <option value='0'>0</option> <option value='20000'>20,000</option> <option value='40000'>40,000</option> <option value='60000'>60,000</option> <option value='80000'>80,000</option> <option value='100000'>100,000</option> <option value='120000'>120,000</option> <option value='140000'>140,000</option> <option value='160000'>160,000</option> <option value='180000'>180,000</option> <option value='200000'>200,000</option> <option value='220000'>220,000</option> <option value='240000'>240,000</option> <option value='260000'>260,000</option> <option value='280000'>280,000</option> <option value='300000'>300,000</option> <option value='320000'>320,000</option> <option value='340000'>340,000</option> <option value='360000'>360,000</option> <option value='380000'>380,000</option> <option value='400000'>400,000</option> <option value='500000'>500,000</option> </select> <br><br> Max. Price: <select name='maxprice'> <option value='any'>Any</option> <option value='0'>0</option> <option value='20000'>20,000</option> <option value='40000'>40,000</option> <option value='60000'>60,000</option> <option value='80000'>80,000</option> <option value='100000'>100,000</option> <option value='120000'>120,000</option> <option value='140000'>140,000</option> <option value='160000'>160,000</option> <option value='180000'>180,000</option> <option value='200000'>200,000</option> <option value='220000'>220,000</option> <option value='240000'>240,000</option> <option value='260000'>260,000</option> <option value='280000'>280,000</option> <option value='300000'>300,000</option> <option value='320000'>320,000</option> <option value='340000'>340,000</option> <option value='360000'>360,000</option> <option value='380000'>380,000</option> <option value='400000'>400,000</option> <option value='500000'>500,000</option> </select> <br><br> <input name='submit' type='submit' value='Search'/> <input name='reset' type='reset' value='Clear Form' /> </form> "); ?> [/code]

When I store a "COMPLETE" complete sql statement in my session variable to be used as the string in the mysql_query parameter, I don't get the "mysql_result(): supplied argument is not a valid MySQL result resource" error. This works: $_SESSION["SQL_STR_PAGE"]="select count(*) from PROPERTY;"; and this works: $_SESSION["SQL_STR_PAGE"]="select count(*) from PROPERTY WHERE schooldist='".$_GET["schooldist"]."'"; But, when I append to the string using .=, it breaks down and gives me the error. Thanks if anyone can help me with this.

//I'M USING THE FOLLOWING CODE TO CREATE A SQL QUERY STATEMENT. IT APPENDS SEARCH PARAMETERS TO CREATE A QUERY STRING: //WHEN I RUN THE QUERY, I GET THE Warning: mysql_result(): supplied argument is not a valid MySQL result resource ERROR. CAN ANYONE TELL ME WHY AND HOW TO FIX IT??? //Run *Record*Count Query //$_SESSION["SQL_STR_PAGE"] stores sql query string. //I made it SESSION to retain the string over numerous page refreshes. $getrecords_str = $_SESSION["SQL_STR_PAGE"]; $getrecords_str .= ";"; $recordcount = mysql_query($getrecords_str); // the page count query ////THE LINE ABOVE IS WHERE THE ERROR IS. $total = mysql_result($recordcount, 0, 0); //PHP.net says this is proper use of mysql_result and it works fine if I don't append to create the $getrecords_str query string //TO GET THE SQL QUERY STRING, I USED THIS CODE: $form_fields = array_keys($_GET); $form_values = array_values($_GET); $_SESSION["SQL_STR_PAGE"] = "select count(*) from PROPERTY WHERE "; //I loop based on the number of array keys that were returned. This is always variable since I don't know how many search parameters will be selected. for ($i = 0; $i < (sizeof($form_fields)-1); $i++) { $thisField = $form_fields[$i]; // key formfields[0] is schooldist $thisValue = $_GET[$thisField]; if ( $i >= 1 ) { $_SESSION["SQL_STR_PAGE"] .= " AND "; //Appends an AND between each sql search parameter } $_SESSION["SQL_STR_PAGE"] .= " ".$thisField."='".$thisValue."' "; //THE ABOVE APPENDS EACH KEY-VALUE PAIR TO THE SQL QUERY } // end for loop // append semi colon for query $_SESSION["SQL_STR_PAGE"] .= ";"; THAT'S IT! Simple enough? ....help, I'm drowning!

My search results page and pagination work fine for page 1, but subsequent pages break due to the fact that my SQL string is regenerated each time the page is refreshed (I lose the values in my query string). How can I maintain the same SQL query string that is generated on the first page hit??? Anyone want to take a stab at this??? //I took out some pieces from yesterday to simplify it for debugging <?php include('../includes/test_shared_functions.php'); include('includes/admin_functions.php'); //include('functions/strMaker.php'); /////////////////////////////////////////////////////////// /// CLASS PAGER ///// /////////////////////////////////////////////////////////// class Pager { function getPagerData($numHits, $limit, $page) { $numHits = (int) $numHits; $limit = max((int) $limit, 1); $page = (int) $page; $numPages = ceil($numHits / $limit); $page = max($page, 1); $page = min($page, $numPages); $offset = ($page - 1) * $limit; $ret = new stdClass; $ret->offset = $offset; $ret->limit = $limit; $ret->numPages = $numPages; $ret->page = $page; return $ret; } } /////////////////////////////////////////////////////////// /// CREATE SCHOOLDIST SQL STRING APPEND ///// /////////////////////////////////////////////////////////// $searchparam_counter = 0; $array = array(0);//set array variable instantiate array and initialize index sub 0 value of 0 echo "value of array at zero is:".$array[0]; /////// CREATE SCHOOLDIST SQL STRING APPEND if ( $_GET['schooldist'] ){ $schooldist=$_GET['schooldist']; //store sql string in $array $array[$searchparam_counter] = " schooldist=$schooldist "; echo "What is in the array after saving schooldist <br>"; print_r($array); //echo $array[$searchparam_counter]; ++$searchparam_counter; } /* $arrayLength = count($arrayName); for ($i = 0; $i < $arrayLength; $i++){ echo "arrayName at[" . $i . "] is: [" .$arrayName[$i] . "]<br>\n"; } */ /////////////////////////////////////////////////////// // CREATE SQL QUERY TO CALC NUMBER OF PAGES /// // String saved as $getpages_str //////////////////////////////////////////////////////// if( $searchparam_counter == 0 ) { $getpages_str = "select count(*) from PROPERTY"; }else { $getpages_str = "select count(*) from PROPERTY WHERE "; for ( $i = 0; $i < $searchparam_counter; ++$i ) { if ( $i >= 1 ) { $getpages_str .= " AND "; } $getpages_str .= $array[$i]; if ( $i == $searchparam_counter-1 ){ $getpages_str .= ";"; } }//end for loop echo "final GETPAGE string is:".$getpages_str."<br>"; // use for debug } //$getpages_str = "select count(*) from PROPERTY where bedrooms=4 AND baths=3;"; db_connect(); // get the pager input values $page = $_GET['page']; $limit = 5; //Run *Page*Count Query $page_result = mysql_query($getpages_str); $total = mysql_result($page_result, 0, 0); echo "Total num of records retrieved is: ".$total; // work out the pager values $pager = Pager::getPagerData($total, $limit, $page); $offset = $pager->offset; $limit = $pager->limit; $page = $pager->page; echo "offset is: ".$offset; echo "Page is: ".$page; echo "Limit is: ".$limit; //////////////////////////////////////////////////////// // CREATE SQL QUERY STATEMENT TO RETRIEVE RECORDS /// //////////////////////////////////////////////////////// if( $searchparam_counter == 0 ) { $str = "select * from PROPERTY "; }else { $str = "select * from PROPERTY WHERE "; for ( $i = 0; $i < $searchparam_counter; ++$i ) { if ( $i >= 1 ) { $str .= " AND "; } $str .= $array[$i]; }//end for loop $str .= " limit $offset, $limit;"; // Note:you have to know $offset and $limit before you can run the query echo "Query Records string is:".$str; // use for debug } //////////////////// RUN MySQL QUERY RETRIEVE DATA //////////////////////// $result = mysql_query($str); //Note: use $str here! NOT $getpages_str // Test for no search results in a separate function if( !$result){ echo "Search produced no results."; return; //return... } // use $result here to output page content print('<table width="750" border="1" cellspacing="0" cellpadding="0">'); while( $row = mysql_fetch_array($result) ) { $vimage_name = $row['image_name']; $vschooldist = $row['schooldist']; $vbedrooms = $row['bedrooms']; $vbaths = $row['baths']; $vprice = $row['price']; print("<tr> <td width='75' align='center'><img src='../images/".$vimage_name."' width='75'/></td> <td width='100' align='center'>$vschooldist</td> <td width='50' align='center'>$vbedrooms</td> <td width='50' align='center'>$vbaths</td> <td width='450' align='left'>$vprice</td> </tr> "); } // end while loop print('</table>'); //NOTE: FORMAT PAGE LINKS IN A TABLE // output paging system (could also do it before we output the page content) if ($page == 1) // this is the first page - there is no previous page echo "Previous"; else // not the first page, link to the previous page echo "<a href=\"search_results_sunday.php?page=" . ($page - 1) . "\">Previous</a>"; for ($i = 1; $i <= $pager->numPages; $i++) { echo " | "; if ($i == $pager->page) echo " $i"; else echo "<a href=\"search_results_sunday.php?page=$i\"> $i</a>"; } if ($page == $pager->numPages) // this is the last page - there is no next page echo "Next"; else // not the last page, link to the next page echo "<a href=\"search_results_sunday.php?page=" . ($page + 1) . "\"> Next</a>"; ?> <?php include('../includes/test_shared_functions.php'); include('includes/admin_functions.php'); /* Use city as a category... City: <select name='Any'> <option value='Colorado Springs'>Colorado Springs</option> <option value='Denver Metro'>Denver</option> <option value='Castle Rock'>Castle Rock</option> </select> <br><br> */ print(" <form action='search_results_sunday.php' method='get'> School District: <select name='schooldist'> <option value=''>Any</option> <option value='3'>3</option> <option value='11'>11</option> <option value='20'>20</option> <option value='49'>49</option> </select> <br><br> Bedrooms: <select name='bedrooms'> <option value=''>Any</option> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> <option value='6'>6</option> </select> <br><br> Baths: <select name='baths'> <option value=''>Any</option> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> <option value='6'>6</option> </select> <br><br> Min. Price: <select name='minprice'> <option value=''>Any</option> <option value='0'>0</option> <option value='20000'>20,000</option> <option value='40000'>40,000</option> <option value='60000'>60,000</option> <option value='80000'>80,000</option> <option value='100000'>100,000</option> <option value='120000'>120,000</option> <option value='140000'>140,000</option> <option value='160000'>160,000</option> <option value='180000'>180,000</option> <option value='200000'>200,000</option> <option value='220000'>220,000</option> <option value='240000'>240,000</option> <option value='260000'>260,000</option> <option value='280000'>280,000</option> <option value='300000'>300,000</option> <option value='320000'>320,000</option> <option value='340000'>340,000</option> <option value='360000'>360,000</option> <option value='380000'>380,000</option> <option value='400000'>400,000</option> <option value='500000'>500,000</option> </select> <br><br> Max. Price: <select name='maxprice'> <option value=''>Any</option> <option value='0'>0</option> <option value='20000'>20,000</option> <option value='40000'>40,000</option> <option value='60000'>60,000</option> <option value='80000'>80,000</option> <option value='100000'>100,000</option> <option value='120000'>120,000</option> <option value='140000'>140,000</option> <option value='160000'>160,000</option> <option value='180000'>180,000</option> <option value='200000'>200,000</option> <option value='220000'>220,000</option> <option value='240000'>240,000</option> <option value='260000'>260,000</option> <option value='280000'>280,000</option> <option value='300000'>300,000</option> <option value='320000'>320,000</option> <option value='340000'>340,000</option> <option value='360000'>360,000</option> <option value='380000'>380,000</option> <option value='400000'>400,000</option> <option value='500000'>500,000</option> </select> <br><br> <input name='submit' type='submit' value='Search' /> <input name='reset' type='reset' value='Clear Form' /> </form> "); ?> /*Since I have more space, I've included my search.php code below: */ <?php print(" <form action='search_results6.php' method='post'> School District: <select name='schooldist'> <option value=''>Any</option> <option value='3'>3</option> <option value='11'>11</option> <option value='20'>20</option> <option value='49'>49</option> </select> <br><br> Bedrooms: <select name='bedrooms'> <option value=''>Any</option> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> <option value='6'>6</option> </select> <br><br> Baths: <select name='baths'> <option value=''>Any</option> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> <option value='6'>6</option> </select> <br><br> Min. Price: <select name='minprice'> <option value=''>Any</option> <option value='0'>0</option> <option value='20000'>20,000</option> <option value='40000'>40,000</option> <option value='60000'>60,000</option> <option value='80000'>80,000</option> <option value='100000'>100,000</option> <option value='120000'>120,000</option> <option value='140000'>140,000</option> <option value='160000'>160,000</option> <option value='180000'>180,000</option> <option value='200000'>200,000</option> <option value='220000'>220,000</option> <option value='240000'>240,000</option> <option value='260000'>260,000</option> <option value='280000'>280,000</option> <option value='300000'>300,000</option> <option value='320000'>320,000</option> <option value='340000'>340,000</option> <option value='360000'>360,000</option> <option value='380000'>380,000</option> <option value='400000'>400,000</option> <option value='500000'>500,000</option> </select> <br><br> Max. Price: <select name='maxprice'> <option value=''>Any</option> <option value='0'>0</option> <option value='20000'>20,000</option> <option value='40000'>40,000</option> <option value='60000'>60,000</option> <option value='80000'>80,000</option> <option value='100000'>100,000</option> <option value='120000'>120,000</option> <option value='140000'>140,000</option> <option value='160000'>160,000</option> <option value='180000'>180,000</option> <option value='200000'>200,000</option> <option value='220000'>220,000</option> <option value='240000'>240,000</option> <option value='260000'>260,000</option> <option value='280000'>280,000</option> <option value='300000'>300,000</option> <option value='320000'>320,000</option> <option value='340000'>340,000</option> <option value='360000'>360,000</option> <option value='380000'>380,000</option> <option value='400000'>400,000</option> <option value='500000'>500,000</option> </select> <br><br> <input name='submit' type='submit' value='Search' /> <input name='reset' type='reset' value='Clear Form' /> </form> "); ?>