I have an Syntax error on my script probably cuased by character encoding.

[co.ador]

there is an error at the query when variable value is pass and it has a character such as  "  '  " for instance

 

Nike Air Max LTD-Men's

 

if the title contains an ' then it will display the error below? I know it should be an character encoding issue. Have not idea where to start to solve this problem..

 

Rating:

ERRORS:

Query Failed at 12/16/2009 22:47:55

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 's '' at line 1

 

Free Results Error at 12/16/2009 22:47:55

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 's '' at line 1

 

Query Failed at 12/16/2009 22:47:55

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 's ' AND `ip_address`='67.87.59.12'' at line 1

 

Free Results Error at 12/16/2009 22:47:55

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 's ' AND `ip_address`='67.87.59.12'' at line 1

[The Little Guy]

What is the SQL query?

[PFMaBiSmAd]

You ALWAYS need to escape string data put into a query. See mysql_real_escape_string

[co.ador]

It should be one of the two methods above causing the  '  no to get into the database, Right now everything is in utf8 don't get why is not coming in.

 <?php 
     public static function RateItem($varItem, $varRating, $varClasses)
      {
        $newClassNames = $varClasses;
        
        // Verify $varName was provided
        if ($varItem != null && strlen(trim($varItem)) != 0
          && $varRating != null && strlen(trim($varRating)) != 0 && is_numeric($varRating) 
          && $varClasses != null && strlen(trim($varClasses)) != 0)
        {
          // Check if Magic Quotes is ON
          if (!get_magic_quotes_gpc())
          {
            $varItem = addslashes($varItem);
          }

if (Rating::CheckRatingsByIp($varItem) == 0)
          {
            $ipAddress = $_SERVER['REMOTE_ADDR'];
            
            Database::ExecuteQuery("INSERT INTO `rating` (`item_name`, `rating`, `ip_address`, `date_rated`) VALUES ('{$varItem}', {$varRating}, '{$ipAddress}', NOW())", "InsertRating");
            Database::FetchResults("InsertRating");
            Database::FreeResults("InsertRating");
            Database::RemoveSavedResults("InsertRating");
            
            // Information for the Output
            $averageStars  = Rating::CalculateAverageRating($varItem);
            $newClassNames = "rated " . Rating::ShowStars($averageStars);
          }
        }?>

 

 

[The Little Guy]

Try this:

 

            $ipAddress = $_SERVER['REMOTE_ADDR'];
            $varItem = mysql_real_escape_string($varItem);
            Database::ExecuteQuery("INSERT INTO `rating` (`item_name`, `rating`, `ip_address`, `date_rated`) VALUES ('{$varItem}', {$varRating}, '{$ipAddress}', NOW())", "InsertRating");

[co.ador]

I have put mysql_real_escape_string before the three query in the script. One is a INSERT and two SELECT

 

$varItem = mysql_real_escape_string($varItem); 

 

Now at least the error is not displaying but still is not rating the items that has the ' or other special characters...

 

<?php 
<?php header('Content-type: text/html; charset=utf-8');?>
<?php
class Rating
  {
    ## PRIVATE VARIABLES
    ## END PRIVATE VARIABLES

    ## PUBLIC METHODS
      // Output the Rating information
      // Returns a string of HTML
      public static function OutputRating ($varItem)
      {
        // Verify $varItem was provided
        if ($varItem != null && strlen(trim($varItem)) != 0)
        {
          // Check if Magic QUotes is ON
          if (!get_magic_quotes_gpc())
          {
            $varItem = addslashes($varItem);
          }
          
          // Information for the Output
          $averageStars = Rating::CalculateAverageRating($varItem);
          
          // Check to see that the user has not already rated this item
          if (Rating::CheckRatingsByIp($varItem) == 0)
          {
            $classes      = "rating " . Rating::ShowStars($averageStars);
            
            // Write Output HTML for the Rating Data
            $output  = "\r\n";
            $output .= "<ul class=\"{$classes}\" id=\"{$varItem}\">\r\n";
            $output .= "  <li class=\"one\"><a   href=\"javascript:RateItem('{$varItem}', 1);\" title=\"1 Star\">1</a></li>\r\n";
            $output .= "  <li class=\"two\"><a   href=\"javascript:RateItem('{$varItem}', 2);\" title=\"2 Stars\">2</a></li>\r\n";
            $output .= "  <li class=\"three\"><a href=\"javascript:RateItem('{$varItem}', 3);\" title=\"3 Stars\">3</a></li>\r\n";
            $output .= "  <li class=\"four\"><a  href=\"javascript:RateItem('{$varItem}', 4);\" title=\"4 Stars\">4</a></li>\r\n";
            $output .= "  <li class=\"five\"><a  href=\"javascript:RateItem('{$varItem}', 5);\" title=\"5 Stars\">5</a></li>\r\n";
		$output .= "</ul>\r\n";
          }
          else
          {
            $classes      = "rated " . Rating::ShowStars($averageStars);

            
            // Write Output HTML for the Rating Data
            $output  = "\r\n";
            $output .= "<ul class=\"{$classes}\" id=\"{$varItem}\">\r\n";
            $output .= "  <li class=\"one\">1</li>\r\n";
            $output .= "  <li class=\"two\">2</li>\r\n";
            $output .= "  <li class=\"three\">3</li>\r\n";
            $output .= "  <li class=\"four\">4</li>\r\n";
            $output .= "  <li class=\"five\">5</li>\r\n";
					            		
            $output .= "</ul>\r\n";
          }
        }
        else
        {
          $output = "";
          // This is a major issue. NO information can be retrieve if an item name is not passed.
          Error::LogError("Variable Missing", "You must provide the item name for this function to find the average.");
        }
        
        return $output;
      }

      // Rate an Item
      // Returns the name/value pair of new class names and the item name
      public static function RateItem($varItem, $varRating, $varClasses)
      {
        $newClassNames = $varClasses;
        
        // Verify $varName was provided
        if ($varItem != null && strlen(trim($varItem)) != 0
          && $varRating != null && strlen(trim($varRating)) != 0 && is_numeric($varRating) 
          && $varClasses != null && strlen(trim($varClasses)) != 0)
        {
          // Check if Magic Quotes is ON
          if (!get_magic_quotes_gpc())
          {
            $varItem = addslashes($varItem);
          }
          
          // Check to see that the user has not already rated this item
          if (Rating::CheckRatingsByIp($varItem) == 0)
          {
            $ipAddress = $_SERVER['REMOTE_ADDR'];
             $varItem = mysql_real_escape_string($varItem);
            Database::ExecuteQuery("INSERT INTO `rating` (`item_name`, `rating`, `ip_address`, `date_rated`) VALUES ('{$varItem}', {$varRating}, '{$ipAddress}', NOW())", "InsertRating");
		mysql_real_escape_string($varItem); 
            Database::FetchResults("InsertRating");
            Database::FreeResults("InsertRating");
            Database::RemoveSavedResults("InsertRating");
            
            // Information for the Output
            $averageStars  = Rating::CalculateAverageRating($varItem);
            $newClassNames = "rated " . Rating::ShowStars($averageStars);
          }
        }
        else
        {
          // This is a major issue. NOT enough information was sent to log the item
          Error::LogError("Variable(s) Missing", "You must provide all of the information to log the rating of this item.");
        }
        
        // Build Name/Value Pair to return
        $nameValue = "classes={$newClassNames}&item={$varItem}";
        return $nameValue;
      }
    ## END PUBLIC METHODS
    
    ## PRIVATE METHODS
      // Calculate Average Rating
      // Returns the number of stars to show
      private static function CalculateAverageRating($varItem)
      {
        $averageStars = 0;
        
        // Query Average Rating for a specific Item
	$varItem = mysql_real_escape_string($varItem);
        Database::ExecuteQuery("SELECT AVG(`rating`) AS `averageRating` FROM `rating` WHERE `item_name`='{$varItem}'", "AverageRating");
        $results = Database::FetchResults("AverageRating");
        Database::FreeResults("AverageRating");
        Database::RemoveSavedResults("AverageRating");
        
        // Round the Average into a Whole Number
        if (sizeof($results) == 1)
        {
          if ($results[0]['averageRating'] != null)
          {
            $averageStars = round($results[0]["averageRating"], 0);
          }
        }
        else
        {
          // This is simply a warning, as it isn't vital if no results were found, as the item may be new.
          Error::LogWarning("Rating Data Missing", "No entries were found for '{$varName}', this might be the first entry.");
        }
        
        return $averageStars;
      }
      
      // Show Stars
      // Returns the class information for the number of stars to show
    private static function ShowStars($varStars)
{
    $aStars = array(
        1    =>    'onestar',
        2    =>    'twostar',
        3    =>    'threestar',
        4    =>    'fourstar',
        5    =>    'fivestar'
    );
    return (true === array_key_exists((integer)$varStars, $aStars)) ? $aStars[(integer)$varStars] : 'nostar' ;
} 

  
      // Check Ratings By IP Address
      // Returns the number of ratings for an item by an ip address
      private static function CheckRatingsByIp($varItem)
      {
        $ipAddress = $_SERVER['REMOTE_ADDR'];
        $varItem = mysql_real_escape_string($varItem);
        Database::ExecuteQuery("SELECT COUNT(*) AS `totalRatings` FROM `rating` WHERE `item_name`='{$varItem}' AND `ip_address`='{$ipAddress}'", "AlreadyRated");

        $results = Database::FetchResults("AlreadyRated");
        Database::FreeResults("AlreadyRated");
        Database::RemoveSavedResults("AlreadyRated");
        
        // Check to see that the user has not already rated this item
        if ($results != null && $results[0]['totalRatings'] != null)
        {
          return $results[0]['totalRatings'];
        }
        
        return 0;
      }
    ## END PRIVATE METHODS
  }
?> 
?>

 

is the above set up alright?