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

abandonship · March 27, 2006

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

abandonship · March 27, 2006

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.

kenrbnsn · March 27, 2006

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

abandonship · March 27, 2006

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]

Sign In

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

Recommended Posts

abandonship

Link to comment

Share on other sites

abandonship

Link to comment

Share on other sites

kenrbnsn

Link to comment

Share on other sites

abandonship

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information