Rating System sql syntax error

supermerc · April 6, 2007

Hey, im trying to make this code work, its for a rating system, but im getting this error:

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 'WHERE id='20'' at line 1

I dont know whats wrong ???

This is my code

<?php
$rating_posted=$_GET['vote'];//pased variable by the the stars value
$id=(INT)$_GET['member_id'];
$query=mysql_query("SELECT total_votes, total_value, used_ips FROM $tableName WHERE id='".$id."' ")or die(" Error: ".mysql_error());
$numbers=mysql_fetch_assoc($query);
$checkIP=unserialize($numbers['used_ips']);
$count=$numbers['total_votes'];//how many votes total
$current_rating=$numbers['total_value'];//total number of rating added together and stored
$sum=$rating_posted+$current_rating;// add together the current vote value and the total vote value
$tense=($count==1) ? "vote" : "votes";//plural form votes/vote
$voted=@mysql_fetch_assoc(@mysql_query("SELECT title FROM $tableName WHERE used_ips LIKE '%".$_SERVER['REMOTE_ADDR']."%' AND id='$id' ")); //Pattern match ip:suggested by Bramus! http://www.bram.us/ - this variable searches through the previous ip address that have voted and returns true or false

if($voted){
echo "<div class=\"rating\">".
"<ul class=\"star-rating\">".
"<li class=\"current-rating\" style=\"width:". @number_format($current_rating/$count,2)*20 ."px;\">Current rating.</li>".
     "<li class=\"one-star\">1</li>".
     "<li class=\"two-stars\" >2</li>".
     "<li class=\"three-stars\">3</li>".
     "<li class=\"four-stars\">4</li>".
     "<li class=\"five-stars\">5</li>".
"</ul>".
"<p>Rating: <strong>".@number_format($current_rating/$count,2)."</strong> {".$count." ".$tense." cast} <br />You have previously voted.</p></div>";//show the current value of the vote with the current numbers
}else{

if(isset($_GET['vote'])){

if($sum==0){
$added=0;//checking to see if the first vote has been tallied
}else{
$added=$count+1;//increment the current number of votes
}

if(is_array($checkIP)){
array_push($checkIP,$_SERVER['REMOTE_ADDR']);//if it is an array i.e. already has entries the push in another value
}else{
$checkIP=array($_SERVER['REMOTE_ADDR']);//for the first entry
}

$insert=serialize($checkIP);
mysql_query("UPDATE $tableName SET total_votes='".$added."', total_value='".$sum."', used_ips='".$insert."' WHERE id='".$_GET['id']."'");

echo 	"<div class=\"rating\"><p>Rating: <strong>".@number_format($sum/$added,2)."</strong> {".$added." ".$tense." cast} <span>Thank you for your vote!</span></p></div>";//show the updated value of the vote
}else{
?>

<div class="rating">
<p>How clear was this tutorial?</p>
<ul class="star-rating">
<li class="current-rating" style="width:<?php echo @number_format($current_rating/$count,2)*20 ?>px;">Current rating</li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 1 star out of 5" class="one-star">1</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 2 stars out of 5" class="two-stars" >2</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 3 stars out of 5" class="three-stars" >3</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 4 stars out of 5" class="four-stars" >4</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 5 stars out of 5" class="five-stars" >5</a></li>
</ul>
<?php
echo	"<p>Rating: <strong>".@number_format($sum/$count,2)."</strong> {".$count." ".$tense." cast}</p></div>";//show the current updated value of the vote
}	// end isset get vote	
}	//end voted true, false
?>

Please help me!

AndyB · April 6, 2007

If that's all your code ... you need to connect to the database and select a database and define the tablename before you execute a query.

supermerc · April 6, 2007

it does connect I got external files connectdb closedb etc.

fert · April 6, 2007

where is $tablename defined

trq · April 6, 2007

Firstly. Calling functions within functions like this....

$voted=@mysql_fetch_assoc(@mysql_query

Is just begging for trouble. You have absolutely no error handling in place.

As for which query is failing. I'll assume its the fist one. The syntax for running a SELECT should be something like...

<?php
$sql = "SELECT total_votes, total_value, used_ips FROM $tableName WHERE id = $id";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $row = mysql_fetch_assoc($result);
    // display data.
  } else {
    echo "No results found";
  }
} else {
  echo "Query failed<br />$sql<br />". mysql_error();
}
?>

This will print the actual query if there is a problem.

supermerc · April 6, 2007

Ok thx a lot thorpe it did change something although im getting a new error now,

Query failed

SELECT total_votes, total_value, used_ips FROM WHERE id = 19

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 'WHERE id = 19' at line 1

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/randomy/public_html/star_rating.php on line 15

trq · April 6, 2007

Look at the outputted query.

SELECT total_votes, total_value, used_ips FROM WHERE id = 19

As others have posted, you have failed to define $tableName.

supermerc · April 6, 2007

its in another file, ill try defining it in that page

trq · April 6, 2007

If its in another file, are you including that file? Whatever your doing $tableName is empty or is out of scope.

supermerc · April 6, 2007

heh, well no i forgot to include... anyways I fixed that now im getting

Parse error: syntax error, unexpected T_VARIABLE in /home/randomy/public_html/star_rating.php on line 4

Line 4 is $rating_posted=$_GET['vote'];//pased variable by the the stars value

AndyB · April 6, 2007

Bet there's a problem with line 1 or line 2 or line 3 - none of which we know - like a missing ;

supermerc · April 6, 2007

you were right there was, but now theres a new error,

No results found

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/randomy/public_html/star_rating.php on line 16

<?php
include'config.php';
$tableName=ratings;
$rating_posted=$_GET['vote'];//pased variable by the the stars value
$id=(INT)$_GET['member_id'];
$sql = "SELECT total_votes, total_value, used_ips FROM $tableName WHERE id = $id";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $row = mysql_fetch_assoc($result);
  } else {
    echo "No results found";
  }
} else {
  echo "Query failed<br />$sql<br />". mysql_error();
}
$numbers=mysql_fetch_assoc($query);
$checkIP=unserialize($numbers['used_ips']);
$count=$numbers['total_votes'];//how many votes total
$current_rating=$numbers['total_value'];//total number of rating added together and stored
$sum=$rating_posted+$current_rating;// add together the current vote value and the total vote value
$tense=($count==1) ? "vote" : "votes";//plural form votes/vote
$voted=@mysql_fetch_assoc(@mysql_query("SELECT title FROM $tableName WHERE used_ips LIKE '%".$_SERVER['REMOTE_ADDR']."%' AND id='$id' ")); //Pattern match ip:suggested by Bramus! http://www.bram.us/ - this variable searches through the previous ip address that have voted and returns true or false

if($voted){
echo "<div class=\"rating\">".
"<ul class=\"star-rating\">".
"<li class=\"current-rating\" style=\"width:". @number_format($current_rating/$count,2)*20 ."px;\">Current rating.</li>".
     "<li class=\"one-star\">1</li>".
     "<li class=\"two-stars\" >2</li>".
     "<li class=\"three-stars\">3</li>".
     "<li class=\"four-stars\">4</li>".
     "<li class=\"five-stars\">5</li>".
"</ul>".
"<p>Rating: <strong>".@number_format($current_rating/$count,2)."</strong> {".$count." ".$tense." cast} <br />You have previously voted.</p></div>";//show the current value of the vote with the current numbers
}else{

if(isset($_GET['vote'])){

if($sum==0){
$added=0;//checking to see if the first vote has been tallied
}else{
$added=$count+1;//increment the current number of votes
}

if(is_array($checkIP)){
array_push($checkIP,$_SERVER['REMOTE_ADDR']);//if it is an array i.e. already has entries the push in another value
}else{
$checkIP=array($_SERVER['REMOTE_ADDR']);//for the first entry
}

$insert=serialize($checkIP);
mysql_query("UPDATE $tableName SET total_votes='".$added."', total_value='".$sum."', used_ips='".$insert."' WHERE id='".$_GET['id']."'");

echo 	"<div class=\"rating\"><p>Rating: <strong>".@number_format($sum/$added,2)."</strong> {".$added." ".$tense." cast} <span>Thank you for your vote!</span></p></div>";//show the updated value of the vote
}else{
?>
<link href="star_rating.css" rel="stylesheet" type="text/css" />


<div class="rating">
<p>How clear was this tutorial?</p>
<ul class="star-rating">
<li class="current-rating" style="width:<?php echo @number_format($current_rating/$count,2)*20 ?>px;">Current rating</li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 1 star out of 5" class="one-star">1</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 2 stars out of 5" class="two-stars" >2</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 3 stars out of 5" class="three-stars" >3</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 4 stars out of 5" class="four-stars" >4</a></li>
     <li><a href="<?php echo $_SERVER['PHP_SELF'] . "?" .$_GET['section'] . "&id=" . $_GET['id'] . "&vote=1";?>" title="Rate this 5 stars out of 5" class="five-stars" >5</a></li>
</ul>
<?php
echo	"<p>Rating: <strong>".@number_format($sum/$count,2)."</strong> {".$count." ".$tense." cast}</p></div>";//show the current updated value of the vote
}	// end isset get vote	
}	//end voted true, false
?>

AndyB · April 6, 2007

$tableName = "ratings";

If you used sensible error-trapping like echoing the query when there's an error you would save lots of time.

trq · April 6, 2007

Just copying and pasting code without looking at how it works won't help you. The code I posted was s simple example, you need to make it work within the context of what YOU already have.

$sql = "SELECT total_votes, total_value, used_ips FROM $tableName WHERE id = $id";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $numbers = mysql_fetch_assoc($result);
    $checkIP = unserialize($numbers['used_ips']);
    $count = $numbers['total_votes'];//how many votes total
    $current_rating = $numbers['total_value'];//total number of rating added together and stored
    $sum = $rating_posted+$current_rating;// add together the current vote value and the total vote value
    $tense = ($count==1) ? "vote" : "votes";//plural form votes/vote
  } else {
    echo "No results found";
  }
} else {
  echo "Query failed<br />$sql<br />". mysql_error();
}

Sign In

Rating System sql syntax error

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information