Jump to content

Archived

This topic is now archived and is closed to further replies.

abandonship

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

Recommended Posts


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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Can you post the code that is causing you problems? Please surround your code by [b][/code][/b] (at the end) and [b][code] [/b](at the start) tags.

Ken

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.