[RESOLVED] need help with mysql

[jasondavis]

Working on a script to receive rating points
Around line 15 im lost as what to do
I need to get the value of total_value which will be a numeric value and add the value of $v to it then save it back to the DB

<?php
require_once("../config/functions.php");

$v = $_GET['v'];  //vote number
$t = $_GET['t']; //user its to
$f = $info['auto_id']; //user its from

$query=mysql_query("SELECT t FROM friend_votes_who WHERE t='$t' AND f='$f'")or die(" Error: ".mysql_error());
	if (mysql_num_rows($query) == 0) {
		//insert votes
		$sql="INSERT INTO friend_votes_who (t, f, v) VALUES ('$t', '$f', '$v')";
		$result = mysql_query($sql);
		//get vote count
		$query=mysql_query("SELECT total_value FROM friend_votes WHERE u='$t'")or die(" Error: ".mysql_error());
			//im lost here
			//need to get the value of total_value then + $v then save it 
			if (mysql_num_rows($query) == 1) {
				$sql = "INSERT INTO friend_votes (`u`,`times`, `total_value`) VALUES ('$profileid', '0', '0')";
				$result = mysql_query($sql);
			}
	}

?>

[jasondavis]

ok my new code now

Is there a more elegant performance way to do this?

<?php
require_once("../config/functions.php");

$v = $_GET['v'];  //vote number
$t = $_GET['t']; //user its to
$f = $info['auto_id']; //user its from

$query=mysql_query("SELECT t FROM friend_votes_who WHERE t='$t' AND f='$f'")or die(" Error: ".mysql_error());
if (mysql_num_rows($query) == 0) {
	//insert votes
	$sql="INSERT INTO friend_votes_who (t, f, v) VALUES ('$t', '$f', '$v')";
	$result = mysql_query($sql);
	//get vote count
	$sql= "SELECT total_value FROM friend_votes WHERE u='$t'";	
	$tvalue=getSingleResult($sql);
	//update vote count
	$tvalue=$tvalue+$v;
	$sql= "UPDATE friend_votes set totalvalue='$tvalue' WHERE u='$t'";	
	executeQuery($sql);
	$msg="Thanks for rating";
	$_SESSION['sess_msg']=$msg;
	header("location: index.php?action=memberprofile&type=common&userid=$t&unid=$unid");
} else {
	$msg="Sorry you already rated this User!";
	$_SESSION['sess_msg']=$msg;
	header("location: index.php?action=memberprofile&type=common&userid=$t&unid=$unid");
}

?>

[jasondavis]

I just ran into another issue, how can I prevent a user from going to

rateuser.php?t=1&v=5

And putting any number they want where the 5 is for value of the vote like

rateuser.php?t=1&v=6565

ok n/m I guess

if ($v) > 5) {

}

[cahva]

Your quess is right but the syntax had error.
I would check like this:

// Check that value is a number and below 5(maybe add checking that its not negative also :)
if (!is_numeric($v) || ($v < 0 || $v > 5)) {
    die('WOOT! Trying to cheat?');
}

About optimizing. Yes you can optimize it a bit
Change this:

    //get vote count
    $sql= "SELECT total_value FROM friend_votes WHERE u='$t'";    

    $tvalue=getSingleResult($sql);
    //update vote count
    $tvalue=$tvalue+$v;
    $sql= "UPDATE friend_votes set totalvalue='$tvalue' WHERE u='$t'"; 

..to this:

    //update vote count
    $sql= "UPDATE friend_votes set totalvalue=totalvalue + $v WHERE u='$t'";

BTW, in your code you have mysql field total_value and totalvalue. Typo?

[jasondavis]

thanks a bunch thats amazing you turned my 6 liners into 1 line 🙂

[jasondavis]

Ok finished making the DB and the backend script is done

now Im working on adding to a profile.

Below is code I put on a users profile that will make the rate table if they don't have one for there profile, but I want to do more here.

$query=mysql_query("SELECT u FROM friend_votes WHERE u='$profileid'")or die(" Error: ".mysql_error());
if (mysql_num_rows($query) == 0) {
$sql = "INSERT INTO friend_votes (`u`,`times`, `total`) VALUES ('$profileid', '0', '0')";
$result = mysql_query($sql);
}

• I need to also select the total field from friend_votes and show it like "Rated 234 Points"

• I need to SELECT $myid from friend_votes_who WHERE t=$profileid AND f=$myid and if a result exist, I would like to not show rate form and if no resul, then show my rate form

any help with best optimized code for this would be appreciated very much 🙂