Jump to content


Photo

I need a liferaft...stored sql query string generating an invalid MySQL result ERROR


  • Please log in to reply
3 replies to this topic

#1 abandonship

abandonship
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 March 2006 - 08:17 PM


//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!


#2 abandonship

abandonship
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 March 2006 - 08:28 PM

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.

#3 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 27 March 2006 - 09:21 PM

Can you post the code that is causing you problems? Please surround your code by [/code] (at the end) and [code=auto:0] (at the start) tags.

Ken

#4 abandonship

abandonship
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 March 2006 - 09:50 PM

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:

<?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>";  


?>




The search.php form page is as follows:
<?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>
");

?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users