[RC2] Database error in Subs-Post.php

[Daniel0]

I've got a lot of these in the error log:

Database Error: 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 'ORDER BY null' at line 4
File: /var/www/smf/Sources/Subs-Post.php
Line: 1828

It's preventing some people from posting in some cases.

[Arantor]

Any mods?

[Daniel0]

Not anything that interferes with Subs-Post.php.

I had the member email me the post he tried to make.

Below is a form which search restaurants by state, zip code, name, type of food and offering, the code is working perfectly. It pulls all the states, zip code, type of food and offering data from the database, as you will see in the queries in the script. There is a query that pull the restaurant list according to the user's selections in the form. When the user get the selection in the form it suppose to display the list of restaurants names and images but I can't get to display it. Help!

This is the query that search for the list of restaurants according to the user's selection in the form.

Query that search for restaurants

Code Select Expand



?<php
// 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':''
    );
   
   
   
    $arrResult = mysql_query($strSQL);
    while($arrRow = mysql_fetch_assoc($arrResult)) {
        $arrRestaurants[] = $arrRow;
    }
       
}
?>

html frame i have set up that will display the restaurants name and image

Code Select Expand

<div id="container4">
  <div class="wrap">
<?php

// print search query
if(!empty($strSQL)) { printf('<p>%s</p>',$strSQL);
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++;
}
if ($i > 1 && $i % 3 == 0 )
{
 echo "<div class=\"clearer\"></div>";

}

}

}?>
</div>
</div>



the whole code

Code Select Expand

<?php
$strSQL = '';
$arrRestaurants = array();

// place holder form data variables
$strName = '';
$strZipCode = '';
$strState = '';
$arrFoodTypes = array();
$arrOfferings = array();

// food types and offerings drop down build arrays
$arrRestaurantsFoodTypes = array('values'=>array(),'output'=>array());
$arrStates = array('','AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY',);
$arrRestaurantsOfferings = array();

$arrResult = mysql_query('SELECT restaurant_food_types_id,name FROM RESTAURANT_FOOD_TYPES');
while($arrRow = mysql_fetch_assoc($arrResult)) {
   $arrRestaurantsFoodTypes['values'][] = $arrRow['restaurant_food_types_id'];
   $arrRestaurantsFoodTypes['output'][] = $arrRow['name'];
}


$arrResult = mysql_query('SELECT restaurant_offerings_id,name FROM RESTAURANT_OFFERINGS');
while($arrRow = mysql_fetch_assoc($arrResult)) {
   $arrRestaurantsOfferings[] = $arrRow;
}




// reset form
$boolReset = isset($_POST['frmSearch']) && isset($_POST['frmSearch']['reset'])?true:false;

if($boolReset === false && isset($_POST['frmSearch'])) {

   // Extract POST variables and escape
   $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();

   // WHERE clause filters
   $arrSQLFilters = array();
   
   // whether or not zip codes table needs to be included
   $boolIncludeZipCodes = false;

   // Zipcode filter
   if(!empty($strZipCode)) {
       $boolIncludeZipCodes = true;
   
       $arrSQLFilters[] = sprintf(
           "z.zip LIKE '%s'"
           ,"%$strZipCode%"
       );
   }
   
   // State filter
   if(!empty($strState)) {
       $boolIncludeZipCodes = true;
   
       $arrSQLFilters[] = sprintf(
           "z.state = '%s'"
           ,$strState
       );
   }

   // Restaurants name filter
   if(!empty($strName)) {
       $arrSQLFilters[] = sprintf(
           "r.restaurantname LIKE '%s'"
           ,"%$strName%"
       );
   }

   // Food types filter
   if(!empty($arrFoodTypes) && !empty($arrFoodTypes[0])) {
   $arrSQLFilters[] = sprintf(
         'r.restaurants_id IN
              (SELECT
                    DISTINCT restaurants_id
                 FROM
                    restaurants_restaurant_food_types
                WHERE
                    restaurants_food_types_id IN (%s)
                GROUP
                   BY
                    restaurants_id
               HAVING
                    COUNT(DISTINCT restaurants_id) = %u)'
           ,/*mysql_real_escape_string(*/ implode(',',$arrFoodTypes) /*)*/
           ,count($arrFoodTypes)
       );
   }

   // Offerings Filter ie. eat-in, lunch, dinner, etc
   if(!empty($arrOfferings)) {
     $arrSQLFilters[] = sprintf(
         'r.restaurants_id IN
              (SELECT
                    DISTINCT restaurants_id
                 FROM
                    restaurants_to_restaurant_offerings
                WHERE
                    restaurant_offerings_id IN (%s)
                GROUP
                   BY
                    restaurants_id
               HAVING
                    COUNT(DISTINCT restaurants_id) = %u)'
           ,/*mysql_real_escape_string(*/ implode(',',$arrOfferings) /*)*/
           ,count($arrOfferings)
       );
   }

   // 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':''
   );
   
   
   
   $arrResult = mysql_query($strSQL);
   while($arrRow = mysql_fetch_assoc($arrResult)) {
       $arrRestaurants[] = $arrRow;
   }
       
}
?>
<div id="wrapper">
<form class="abajo"  name="frmSearch" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">

<fieldset class="primero">
<legend class="primerosub">Find A Restuarant</legend>
        <fieldset class="segundo1" style="border-color:#FFFFFF" > 
<legend>Location Details</legend>
        <ol>
            <li class="restaurants-name">
                <label for="restaurants-name">Name</label>
                <input type="text" name="frmSearch[name]" value="<?php echo $strName; ?>" id="restaurants-name">
            </li>
            <li class="restaurants-zipcode">
                <label for="restaurants-zipcode">Zip</label>
                <input type="text" name="frmSearch[zipcode]" value="<?php echo $strZipCode; ?>" maxlength="5" id="restaurants-name">
            </li>
           
            <?php if(!empty($arrStates)) { ?>
                <li class="restaurants-state">
                    <label for="restaurants-state">State</label>
                    <select name="frmSearch[state]" id="restaurants-state">
                        <?php
                       foreach($arrStates as $strStateAbb) {
                           printf(
                               '<option value="%s"%s>%s</option>'
                               ,$strStateAbb
                               ,strcmp($strState,$strStateAbb) == 0?' selected="selected"':''
                               ,strcmp($strStateAbb,'')==0?'--':$strStateAbb
                           );
                       }    
                       ?>
                    </select>
                </li>
            <?php } ?>
           
            <?php if(!empty($arrRestaurantsFoodTypes)) { ?>
                <li class="restaurants-food-types">
                    <label for="restaurants-food-types">Food Type</label>
                    <select name="frmSearch[food_types][]" id="restaurants-food-types">
                        <?php
                       foreach($arrRestaurantsFoodTypes['values'] as $intIndex=>$intFoodTypesId) {
                           printf(
                               '<option value="%s"%s>%s</option>'
                               ,$intFoodTypesId
                               ,in_array($intFoodTypesId,$arrFoodTypes)?' selected="selected"':''
                               ,$arrRestaurantsFoodTypes['output'][$intIndex]
                           );
                       }    
                       ?>
                    </select>
                </li>
                </ol>
            <?php } ?>
            </fieldset>
           
            <?php if(!empty($arrRestaurantsOfferings)) { ?>
<fieldset class="tercero" style="" >
<legend>Services</legend>

         
               
                    <?php
                   foreach($arrRestaurantsOfferings as $arrRestaurantsOffering) {
                       printf(
                           '<ol><li class="restaurants-offerings-%u">
                                 <input type="checkbox" name="frmSearch[offerings][]" value="%u" id="restaurants-offerings-%u"%s>
                                 <span for="restaurants-offerings-%u" class="checkboxes23">%s</span>
                           </li></ol>'
                           ,$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']
                       );
                   }
               ?>
               
         
                        <?php } ?></fieldset>
           
            <li class="submit">
                <input type="submit" value="Submit" name="frmSearch[submit]">
            </li>
           
            <li class="reset">
                <input type="submit" value="reset" name="frmSearch[reset]">
            </li>
           
        </ul>
    </fieldset>
</form>
</div>
<div id="container4">
  <div class="wrap">
<?php

// print search query
if(!empty($strSQL)) { printf('<p>%s</p>',$strSQL);
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++;
}
if ($i > 1 && $i % 3 == 0 )
{
 echo "<div class=\"clearer\"></div>";

}

}

}?>
</div>
</div>


I have been trying for a while to figure out how to display the data, I can see all the queries and php codes inside the script but seem to lack some organization of my part to get it to display.

Any suggestion and help will be greatly appreciated

This is the query that fails in Subs-Post.php:

Code Select Expand

$smcFunc['db_insert']('',
'{db_prefix}messages',
array(
'id_board' => 'int', 'id_topic' => 'int', 'id_member' => 'int', 'subject' => 'string-255', 'body' => (!empty($modSettings['max_messageLength']) && $modSettings['max_messageLength'] > 65534 ? 'string-' . $modSettings['max_messageLength'] : 'string-65534'),
'poster_name' => 'string-255', 'poster_email' => 'string-255', 'poster_time' => 'int', 'poster_ip' => 'string-255',
'smileys_enabled' => 'int', 'modified_name' => 'string', 'icon' => 'string-16', 'approved' => 'int',
),
array(
$topicOptions['board'], $topicOptions['id'], $posterOptions['id'], $msgOptions['subject'], $msgOptions['body'],
$posterOptions['name'], $posterOptions['email'], time(), $posterOptions['ip'],
$msgOptions['smileys_enabled'] ? 1 : 0, '', $msgOptions['icon'], $msgOptions['approved'],
),
array('id_msg')
);

[Arantor]

Are you using MySQL or PostgreSQL?

[Daniel0]

MySQL. I wasn't even aware SMF supported PostreSQL actually.

Edit: I just checked on a clean install on localhost. It fails there as well.

[SleePy]

Open Subs-Db-mysql.php

Find and remove:

Code Select Expand


// Use "ORDER BY null" to prevent Mysql doing filesorts for Group By clauses without an Order By
if (strpos($db_string, 'GROUP BY') !== false && strpos($db_string, 'ORDER BY') === false)
{
// Add before LIMIT
if ($pos = strpos($db_string, 'LIMIT '))
$db_string = substr($db_string, 0, $pos) . "\t\t\tORDER BY null\n" . substr($db_string, $pos, strlen($db_string));
else
// Append it.
$db_string .= "\n\t\t\tORDER BY null";
}


This code was supposed to help speed up some queries as it appears mysql gets more optimizations when you order by null for a group by.  It wasn't properly implanted though and is a known RC2 bug.

[Daniel0]

Thanks. How come it only broke it for some queries/posts though?

[SleePy]

It breaks when "GROUP BY" exists in the message.  The above code doesn't properly check by looking at the last x bytes and/or ensuring this is only a SELECT (not a insert, update or alter).