Warning! What's is wrong with this query?

[co.ador]

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\store\pruebadeoddz2.php on line 173

 

the above warning is displaying at the screen

 

it said that line 173

 

    // Run search query    
    $arrResult = mysql_query($strSQL);

    while($arrRow = mysql_fetch_assoc($arrResult)) { // line 173 has supplied argument is not a valid MySQL result resource
        $arrRestaurants[] = $arrRow;
    }

 

The $strSQL variable contain the fallowing query.

$strSQL = sprintf(
    'SELECT
          r.restaurants_id
          ,r.restaurantname
          ,r.image
       FROM
          restaurants r
         %s
         %s
         %s'
     ,$boolIncludeZipCodes === true?'INNER JOIN zip_codes z ON r.restaurants_id = z.restaurants_id':''
     ,empty($arrSQLFilters)?'':' WHERE '.implode(' AND ',$arrSQLFilters)  
     ,$boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':''
    );

   

 

This warning is displaying only when the Offerings arrSQLFilters has an value assigned or one of the checkboxes is checked..

 

The script below is inside the form and is the one that populates the offerings unordered list and is supposed to compared the entered value with the one values in the database

[rajivgonsalves]

you should echo your query also put a or die(mysql_error()) on mysql_query to find out the error

[co.ador]

I have echo the $arrSQLFilters variable below the script below and the results in the browsers is equal to "Array"

 

 

 

<?php if(!empty($arrOfferings)) {
      $arrSQLFilters[] = sprintf(
          'r.restaurants_id IN
               (SELECT
                     restaurants_id, 
                 FROM
                     restaurants_to_restaurant_offering
		     
                 WHERE
                     restaurant_offerings_id IN (%s)
                 GROUP
                    BY
                     restaurants_id
                HAVING 
                     COUNT(*) = %u)'
            ,/*mysql_real_escape_string(*/ implode(',',$arrOfferings) /*)*/
            ,count($arrOfferings) );
    echo $arrSQLFilters;  }
?>

[rajivgonsalves]

try print_r($arrSQLFilters)

[premiso]

Just a side note:

 

$arrSQLFilters[] = sprintf(

 

That statement you set it equal to an array index of $arrSQLFilters, if you do not want it to be an array, remove the []. If you wanted it to be an array, then you have to call the index to use it:

 

echo $arrSQLFilters[0];

 

Assuming that the index at 0 is what you want to use. You can also do a foreach to loop through the array.

[co.ador]

this is the results of

 

print_r($arrSQLFilters);

 

 

is

 

 

Array ( [0] => r.restaurants_id IN (SELECT restaurants_id, FROM restaurants_to_restaurant_offering WHERE restaurant_offerings_id IN (6) GROUP BY restaurants_id HAVING COUNT(*) = 1) )

[co.ador]

Notice: Undefined offset: 3 in C:\wamp\www\nyhungry\pruebadeoddz2.php on line 153

 

is the results of

 

echo $arrSQLFilters[3];

[rajivgonsalves]

why don't you print $strSQL instead since there is where you are getting the error ?

 

 

  // Run search query   
   echo $strSQL . "<br />"; 
   $arrResult = mysql_query($strSQL) or die(mysql_error());
   
    while($arrRow = mysql_fetch_assoc($arrResult)) { // line 173 has supplied argument is not a valid MySQL result resource
        $arrRestaurants[] = $arrRow;
    }

[co.ador]

I have used as an array because it will populates severals values from the database.

 

Below is the code

 

// Part I---- This is the query and while loop that populates or list the offerings from the database in the form.. Look that the variable that contain the array values in this query and while loop is  $arrRestaurantsOfferings and it is used in the form to populate the list or array.
<?php
$arrResult = mysql_query('SELECT restaurant_offerings_id,name FROM restaurant_offerings ORDER BY name ASC');
while($arrRow = mysql_fetch_assoc($arrResult)) {

    $arrRestaurantsOfferings[] = $arrRow;
}

// Part II ---- Extract POST variables and escape from the form or user entry
    $strName = isset($_POST['frmSearch']['name'])?/*mysql_real_escape_string(*/$_POST['frmSearch']['name']/*)*/:'';
    $strZipCode = isset($_POST['frmSearch']['zipcode'])/*mysql_real_escape_string(*/?$_POST['frmSearch']['zipcode']/*)*/:'';
    $strState = isset($_POST['frmSearch']['state'])/*mysql_real_escape_string(*/?$_POST['frmSearch']['state']/*)*/:'';
    $arrFoodTypes = isset($_POST['frmSearch']['food_types'])?$_POST['frmSearch']['food_types']:array();
    $arrOfferings = isset($_POST['frmSearch']['offerings'])?$_POST['frmSearch']['offerings']:array();

// part III--- part of the script that select the offering from the databse according to the user entry
if(!empty($arrOfferings)) {
      $arrSQLFilters[] = sprintf(
          'r.restaurants_id IN
               (SELECT
                     restaurants_id, 
                 FROM
                     restaurants_to_restaurant_offering
		     
                 WHERE
                     restaurant_offerings_id IN (%s)
                 GROUP
                    BY
                     restaurants_id
                HAVING 
                     COUNT(*) = %u)'
            ,/*mysql_real_escape_string(*/ implode(',',$arrOfferings) /*)*/
            ,count($arrOfferings) );
     
}
?>
// Part IV--- this is the Part of the form that display the offerings coming from Part I or the $arrRestaurantsOffering variable found in Part I throught this form users input the data to the script and it is pick up by the Part II or $_POST variables above then send to Part III processed there for compering and then that comparinson is sent to Part V proccesed to choose the Restaurants that match those users input and then display it in part VI.. But then  
<?php if(!empty($arrRestaurantsOfferings)) { ?>
            <li class="restaurants-offerings">
                <ul>
                    <?php
                    foreach($arrRestaurantsOfferings as $arrRestaurantsOffering) {
                        printf(
                            '<li class="restaurants-offerings-%u">
                                  <input type="checkbox" name="frmSearch[offerings][]" value="%u" id="restaurants-offerings-%u"%s>
                                  <label for="restaurants-offerings-%u">%s</label>
                            </li>'
                            ,$arrRestaurantsOffering['restaurant_offerings_id']
                            ,$arrRestaurantsOffering['restaurant_offerings_id']
                            ,$arrRestaurantsOffering['restaurant_offerings_id']
                            ,in_array($arrRestaurantsOffering['restaurant_offerings_id'],$arrOfferings)?' checked="checked"':''
                            ,$arrRestaurantsOffering['restaurant_offerings_id']
                            ,$arrRestaurantsOffering['name']
                        );
                    }
                ?>
                </ul>
            </li>
            <?php }
    //Part V Build search query and embed filters
    $strSQL = sprintf(
    'SELECT
          r.restaurants_id
          ,r.restaurantname
          ,r.image
       FROM
          restaurants r
         %s
         %s
         %s'
     ,$boolIncludeZipCodes === true?'INNER JOIN zip_codes z ON r.restaurants_id = z.restaurants_id':''
     ,empty($arrSQLFilters)?'':' WHERE '.implode(' AND ',$arrSQLFilters)  
     ,$boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':''
    );
   // Run search query    
    $arrResult = mysql_query($strSQL);

    while($arrRow = mysql_fetch_assoc($arrResult)) {// line 173 has supplied argument is not a valid MySQL result resource
        $arrRestaurants[] = $arrRow;
    }


//Part VI print search query
<div id="container4">
  <div class="wrap">
<?php

if(!empty($strSQL)) { 
printf('<p>%s</p>',$strSQL);
$i = 1;

foreach($arrRestaurants as $arrRestaurant) {

  echo "<div class=\"shoeinfo1\">
   <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
      <h2 class=\"infohead\">". $arrRestaurant['restaurantname'] . "</h2>
      <div class=\"pic\"><img class=\"line\" src= ". $arrRestaurant['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

      
    </div>";
$i++; 
echo $arrRestaurant;

if ($i > 1 && $i % 3 == 0 ) 
{
  echo "<div class=\"clearer\"></div>";

}
}


}



?>
</div>
</div>


?>

 

Now in the display part the Warning message I have shown above comes up on line 173. Help still doesn't display anything when only offerings is cheked

 

 

[co.ador]

This are the restuls

SELECT r.restaurants_id ,r.restaurantname ,r.image FROM restaurants r WHERE r.restaurants_id IN (SELECT restaurants_id, FROM restaurants_to_restaurant_offering WHERE restaurant_offerings_id IN (3) GROUP BY restaurants_id HAVING COUNT(*) = 1)

 

when I

 

echo $strSQL . "<br />";

 

 

It looks like the query is working ok why the Warning!

 

Also you can read the analisys I have done in the post above it might help to figure out the problem

[rajivgonsalves]

it looks okay to me did you try the or die(mysql_error()) on your mysql_query ?

[co.ador]

now How would you set it up?

[rajivgonsalves]

something like this

 

// Run search query   

    $arrResult = mysql_query($strSQL) or die("Cannot execute:". mysql_error());

[co.ador]

it displays like this with the warning below it looks like the query is working ok but the information going inside the database is not working ok.

 

SELECT r.restaurants_id ,r.restaurantname ,r.image FROM restaurants r WHERE r.restaurants_id IN (SELECT restaurants_id, FROM restaurants_to_restaurant_offering WHERE restaurant_offerings_id IN (1) GROUP BY restaurants_id HAVING COUNT(*) = 1)

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\nyhungry\pruebadeoddz2.php on line 173

 

 

[co.ador]

It dies!

 

 

And the reason because it dies is below

 

 

SELECT r.restaurants_id ,r.restaurantname ,r.image FROM restaurants r WHERE r.restaurants_id IN (SELECT restaurants_id, FROM restaurants_to_restaurant_offering WHERE restaurant_offerings_id IN (1) GROUP BY restaurants_id HAVING COUNT(*) = 1)
Cannot execute:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM restaurants_to_restaurant_offering ' at line 11

[premiso]

SELECT restaurants_id, FROM

 

Note the , before the FROM, remove that and see if it works.

 

SELECT restaurants_id FROM

[co.ador]

Thank you guys the comma was obstructing in there Thank you guys