[SOLVED] Code returning false mysql result

[DaveLinger]

I'm working on code which will allow users to "rate" something, for instance, on a scale of 1-5. When they click on a star (1-5), it sends them to the following PHP script, with ?rid=&rating=

 

rid is the id of the item they are rating, and rating is the integer 1, 2, 3, 4, or 5. Problem is... even with an empty db table, it returns "Rating added successfully." - but doesn't add anything. If I add a row with uid=1 rid=2 and rating=3, and am logged in as uid=1, it still says "Rating added successfully.", and does nothing. It SHOULD HAVE updated the existing row and told me so. Help

 

Thanks

 

<?php

include('includes/config.php');

session_start(); 

$rid = $_GET['rating'];
$rating = $_GET['rating'];

if(isset($_SESSION['uid'])){

$uid = $_SESSION['uid'];

if (!$link = mysql_connect($sqlserver, $sqluser, $sqlpassword)) {

   echo 'Could not connect to mysql';

   exit;

}



if (!mysql_select_db($sqldb, $link)) {

   echo 'Could not select database';

   exit;

}



$query="SELECT * FROM ratings WHERE uid = $uid, rid = $rid";

$result = mysql_query($query);

if(empty($result)){

$query="INSERT INTO ratings
(`rid`, `uid`, `rating`)
values
(`$rid`, `$uid`, `$rating`)";
$result = mysql_query($query, $link);

if (!result) {
echo "DB Error, could not query the database\n";
echo 'MySQL Error: ' . mysql_error();
exit;
}

echo "Rating added successfully.";

}else{

$query="UPDATE `ratings` SET `rating` = \"$rating\" WHERE uid = $uid, rid = $rid";
$result = mysql_query($query, $link);

if (!result) {
echo "DB Error, could not query the database\n";
echo 'MySQL Error: ' . mysql_error();
exit;
}

echo "Rating updated successfully.";

}

}else{

echo "You must be logged in to rate.";

}

?>

[bbaker]

try changing the query to:

 

$query="UPDATE ratings SET rating = '$rating' WHERE uid = $uid AND rid = $rid";

 

also you not getting you DB Error message because your missing a $ here:

 

if (!$result) {

echo "DB Error, could not query the database\n";

echo 'MySQL Error: ' . mysql_error();

exit;

}

[pocobueno1388]

You are not doing a valid query to the DB with your select statement.

$query="SELECT * FROM ratings WHERE uid = $uid, rid = $rid";

 

Change it to this:

$query="SELECT * FROM ratings WHERE uid = $uid AND rid = $rid";

 

You never try catching errors with either query.

 

Change this:

$result = mysql_query($query);

 

To

$result = mysql_query($query)or die(mysql_error());

 

And change this:

$result = mysql_query($query, $link);

 

To this:

$result = mysql_query($query, $link)or die(mysql_error());

 

Post back any errors that you get.

 

[DaveLinger]

Thanks so much - I hate it when I forget one little character.

 

DB Error, could not query the database MySQL Error: Unknown column '1' in 'field list'

 

Now I know where to look!

[pocobueno1388]

Did it give you a line number? Could you post your updated code, and also give the full error?

[DaveLinger]

Now it's saying "Rating updated successfully.".

 

Here's the new code:

 

<?php

include('includes/config.php');

session_start(); 

$rid = $_GET['rid'];
$rating = $_GET['rating'];

if(isset($_SESSION['uid'])){

$uid = $_SESSION['uid'];

if (!$link = mysql_connect($sqlserver, $sqluser, $sqlpassword)) {
   echo 'Could not connect to mysql';
   exit;
}

if (!mysql_select_db($sqldb, $link)) {
   echo 'Could not select database';
   exit;
}

$query="SELECT * FROM ratings WHERE uid = $uid AND rid = $rid";
$result = mysql_query($query);

if(empty($result)){

$query="INSERT INTO ratings
(`rid`, `uid`, `rating`)
values
(`$rid`, `$uid`, `$rating`)";
$result = mysql_query($query, $link)or die(mysql_error());

echo "Rating added successfully.";

}else{

$query="UPDATE `ratings` SET `rating` = \"$rating\" WHERE uid = $uid AND rid = $rid";
$result = mysql_query($query, $link)or die(mysql_error());

echo "Rating updated successfully.";

}

}else{

echo "You must be logged in to rate.";

}

?>

[bbaker]

change this

$query="UPDATE `ratings` SET `rating` = \"$rating\" WHERE uid = $uid AND rid = $rid";

 

to this

$query="UPDATE ratings SET rating = '$rating' WHERE uid = $uid AND rid = $rid";

[DaveLinger]

Same result. Says "Rating Updated Successfully" every time, regardless of the inputs.

[DaveLinger]

I added echo $query, and it says "UPDATE `ratings` SET `rating` = '3' WHERE uid = 1 AND rid = 9 Rating updated successfully."

 

maybe it's the mismatching quotes? The query seems right

[trq]

Because $result will never be empty, you just set it to something above. Change this....

 

if(empty($result)){

 

to...

 

if (!$result) {

[DaveLinger]

Because $result will never be empty, you just set it to something above. Change this....

 

if(empty($result)){

 

to...

 

if (!$result) {

 

Fixed. Same result.

[DaveLinger]

I added echo $query, and it says "UPDATE `ratings` SET `rating` = '3' WHERE uid = 1 AND rid = 9 Rating updated successfully."

 

maybe it's the mismatching quotes? The query seems right

 

Come to think of it, it shouldn't be trying to update. Because no rows are present, a row where uid=1 and rid=9 can't exist, so there should be an error.

[trq]

Sorry. I messed that.

 

<?php

include('includes/config.php');

session_start(); 

$rid = $_GET['rid'];
$rating = $_GET['rating'];

if(isset($_SESSION['uid'])) {
  $uid = $_SESSION['uid'];
  if (!$link = mysql_connect($sqlserver, $sqluser, $sqlpassword)) {
    echo 'Could not connect to mysql';
    exit;
  }

  if (!mysql_select_db($sqldb, $link)) {
    echo 'Could not select database';
    exit;
  }

  $query="SELECT * FROM ratings WHERE uid = $uid AND rid = $rid";
  if ($result = mysql_query($query)) {
    if (!mysql_num_rows($result)) {
      $query="INSERT INTO ratings
      (`rid`, `uid`, `rating`)
      values
      (`$rid`, `$uid`, `$rating`)";
      mysql_query($query, $link)or die(mysql_error());
      echo "Rating added successfully.";
    } else {
      $query="UPDATE `ratings` SET `rating` = \"$rating\" WHERE uid = $uid AND rid = $rid";
      $result = mysql_query($query, $link)or die(mysql_error());
      echo "Rating updated successfully.";
    }
  }
} else {
echo "You must be logged in to rate.";
}
?>

[DaveLinger]

Sorry. I messed that.

 

<?php

include('includes/config.php');

session_start(); 

$rid = $_GET['rid'];
$rating = $_GET['rating'];

if(isset($_SESSION['uid'])) {
  $uid = $_SESSION['uid'];
  if (!$link = mysql_connect($sqlserver, $sqluser, $sqlpassword)) {
    echo 'Could not connect to mysql';
    exit;
  }

  if (!mysql_select_db($sqldb, $link)) {
    echo 'Could not select database';
    exit;
  }

  $query="SELECT * FROM ratings WHERE uid = $uid AND rid = $rid";
  if ($result = mysql_query($query)) {
    if (!mysql_num_rows($result)) {
      $query="INSERT INTO ratings
      (`rid`, `uid`, `rating`)
      values
      (`$rid`, `$uid`, `$rating`)";
      mysql_query($query, $link)or die(mysql_error());
      echo "Rating added successfully.";
    } else {
      $query="UPDATE `ratings` SET `rating` = \"$rating\" WHERE uid = $uid AND rid = $rid";
      $result = mysql_query($query, $link)or die(mysql_error());
      echo "Rating updated successfully.";
    }
  }
} else {
echo "You must be logged in to rate.";
}
?>

 

Unknown column '9' in 'field list'

 

(when I pass 9 as rid on GET)

[trq]

Change you die()'s so your erros are a little clearer. eg; Instead of...

 

die(mysql_error())

 

Use...

 

die("INSERT Failed<br />". mysql_error() . "<br />" . $query)

[DaveLinger]

INSERT Failed

Unknown column '91' in 'field list'

INSERT INTO ratings (`rid`, `uid`, `rating`) values (`91`, `1`, `3`)

[DaveLinger]

That's when I use 91 as my rid

[trq]

rid doesn't happen to be an auto incrementing field does it? If so... leave it out altogether when INSERTing.

[DaveLinger]

yes, it was a auto_increment field - it wasn't supposed to be. It's not any more, just a normal text field, same error.

[trq]

just a normal text field

 

Seems it shouldn't be. An auto_incrementing filed should be used for record id's.

[DaveLinger]

the rid is used to correspond to items in another table. For each row in the ratings table, I'm getting:

 

Their User Id

The Id of the item they are rating

The rating

 

The record does not need an ID, so no field needs to auto increment.

[DaveLinger]

Still need help =/

[trq]

Your using `backticks` around your values... these need to be quotes. eg;

 

$query="INSERT INTO ratings
(`rid`, `uid`, `rating`)
values
('$rid', '$uid', '$rating')";

[DaveLinger]

Here's my current code:

 

<?php

include('includes/config.php');

session_start(); 

$rid = $_GET['rid'];
$rating = $_GET['rating'];

if($rid == "" || $rating == ""){
echo "Invalid vote";
}else{

if(isset($_SESSION['uid'])) {
$uid = $_SESSION['uid'];

if (!$link = mysql_connect($sqlserver, $sqluser, $sqlpassword)) {
echo 'Could not connect to mysql';
exit;
}

if (!mysql_select_db($sqldb, $link)) {
echo 'Could not select database';
exit;
}

$query="SELECT * FROM ratings WHERE uid = $uid AND rid = $rid";
$result = mysql_query($query);
if(!$result){

$query="INSERT INTO ratings
('rid', 'uid', 'rating')
values
('$rid', '$uid', '$rating')";
mysql_query($query, $link)or die("INSERT Failed<br />".mysql_error() . "<br />" . $query);
echo "Rating added successfully.";

} else {

$query="UPDATE ratings SET rating = $rating WHERE uid = $uid AND rid = $rid";
$result = mysql_query($query, $link)or die("UPDATE Failed<br />".mysql_error() . "<br />" . $query);
echo "Rating updated successfully.";
}

} else {
echo "You must be logged in to rate.";
}

}
?>

 

I'm fairly sure that the problem lies in the if (!$result) part, because $result echoes "Resource id #3", when it SHOULD be finding no rows. Because $result is not showing up as "!", it's trying to UPDATE a row instead of ADD one.

[corbin]

The function mysql_query returns true if it executed, not if it returned rows, thus you are correcting in thinking that is the problem.

 

Try changing if(!$result){ to if(mysql_num_rows($result) < 1){