incrementing by number a row with a specific user name in a table row

silverglade · January 18, 2010

hi, ive looked all over google and cant find out how to do this in sql. i have a field with an id, an name, and several attributes about that person that people vote on. the problem is, if the user enters the same name, i dont know how to update that row with the new vote.

here is the code i have so far, any help GREATLY appreciated. thanks. derek

<?php
include("connect1.php");

////////////////////////////////////////
////////////////////////////////////////


// escape username and password for use in SQL//person said on board "looks fine" like this
//to prevent sql injections

$field2 = mysql_real_escape_string($field2);
$field3 = mysql_real_escape_string($field3);


/// query db and loop through rows example

$field2 = $_POST['friend'];
$field3   = $_POST['zip'];
$field4   = $_POST['grade1'];
$field5   = $_POST['grade2'];
$field6   = $_POST['grade3'];
$field7 = $_POST['grade4'];



$field4 = (int)$field4;
$field5 = (int)$field5;
$field6 = (int)$field6;
$field7 = (int)$field7;

    
$total = 4;
$sum = $field4 + $field5 + $field6 + $field7;       
$average = $sum/$total;

$grade = "null";


if ($average <= 1)  {
$grade = "F";
  echo "Your friend has a grade of <strong>F.</strong> ";
  
} else if ($average > 1 && $average <= 2) { 
  $grade = "D";
  echo "Your friend has a grade of <strong>D.</strong> ";

} else if ($average > 2 && $average <= 3) { 
   $grade = "C";
  echo "Your friend has a grade of <strong>C.</strong> ";

} else if ($average > 3 && $average <= 4) {
   $grade = "B"; 
  echo "Your friend has a grade of <strong>B.</strong> ";

} else if ($average > 4 && $average <= 5)  {
   $grade = "A";
  echo "Your friend has a grade of <strong>A.</strong> ";

} 

if(isset($_COOKIE['guest_cookie'])) { 
$cookie_ip = $_COOKIE['guest_cookie']; 
$user_ip = $_SERVER['REMOTE_ADDR']; 
if ($cookie_ip == $user_ip) { 
  echo "you have already voted for this friend"; 
} else { 
  
if(isset($_POST['Submit'])){
/* mysql_query("INSERT INTO friendgrade (grade , friend,zip,loyalty,courtesy , stability, attitude)VALUES('$grade', '$field2','$field3','$field4','$field5','$field6' , '$field7')");*/
UPDATE friendgrade SET courtesy = courtesy + $field4 ,  stability = 
stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7;
}//end isset

  }//end else
  
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update Statement</title>
<style type="text/css">
<!--
.style1 {color: #990000}
.style2 {color: #0066FF}
.style3 {color: #0000CC}
.style4 {color: #993399}
-->
</style>
</head>

<body>

</p>
<div align="center">WELCOME TO THE FRIENDGRADER</div>
<p>Please enter a friend, their zip code, and rate them with the following criteria.<span class="style4"></span><br />
</p>
<form id="form1" name="form1" method="post" action="">

  <table border="0">
    
    <tr>
      <td>Friend</td>
      <td>
        <input name="friend" type="text" id="friend" /></td>
    </tr>
    <tr>
      <td>zip code</td>
      <td>
       <input name="zip" type="text" id="zip" />
      </td>
    </tr>
    <tr>
      <td>courtesy</td>
      <td><table>
        <tr>
          <td> 
            <input type="radio" name="grade1" value="1" id="RadioGroup1_0" />
            very poor
            <input type="radio" name="grade1" value="2" id="RadioGroup1_1" />
poor
<input type="radio" name="grade1" value="3" id="RadioGroup1_2" />
ok
<input type="radio" name="grade1" value="4" id="RadioGroup1_3" />
good
<input type="radio" name="grade1" value="5" id="RadioGroup1_4" />
excellent
</td>
        </tr>
       
      </table>
      </td>
    </tr>
   
   
    <tr>
      <td>stability</td>
      <td><input type="radio" name="grade2" id="very_poor3" value="1" />
very poor
  <input type="radio" name="grade2" id="poor3" value="2" />
poor
<input type="radio" name="grade2" id="ok3" value="3" />
ok
<input type="radio" name="grade2" id="good3" value="4" />
good
<input type="radio" name="grade2" id="excellent3" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>loyalty</td>
      <td><input type="radio" name="grade3" id="very_poor4" value="1" />
very poor
  <input type="radio" name="grade3" id="poor4" value="2" />
poor
<input type="radio" name="grade3" id="ok4" value="3" />
ok
<input type="radio" name="grade3" id="good4" value="4" />
good
<input type="radio" name="grade3" id="excellent4" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>attitude</td>
      <td><input type="radio" name="grade4" id="very_poor5" value="1" />
very poor
  <input type="radio" name="grade4" id="poor5" value="2" />
poor
<input type="radio" name="grade4" id="ok5" value="3" />
ok
<input type="radio" name="grade4" id="good5" value="4" />
good
<input type="radio" name="grade4" id="excellent5" value="5" />
excellent</td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" name="Submit" value="Submit" /></td>
    </tr>
  </table>
   
</form>


</body>
</html>

kickstart · January 18, 2010

Hi

I am unsure about what you want.

I assume you have a table of people each with a count for attributes. Do you have one row per person? Ie, if 3 people have the same friend then are there 3 seperate rows for that friend or just one row with a total of their ratings?

If the first then just do an update (don't add the revised scores to the existing scores). If the second then you can't really revise the scores as you have no idea what (if any) the previous scores were.

All the best

Keith

silverglade · January 18, 2010

thank you, yes if the persons name is "john doe" i want to be able to update his score based on his name, which is a field. notice i put an update statement in my code, but it doesnt work, all i get is a blank screen now. any more help greatly appreciated. thanks. derek

here is the new code with the update statement, i couldnt get it to update "john doe".

<?php
include("connect1.php");

////////////////////////////////////////
////////////////////////////////////////


// escape username and password for use in SQL//person said on board "looks fine" like this
//to prevent sql injections

$field2 = mysql_real_escape_string($field2);
$field3 = mysql_real_escape_string($field3);


/// query db and loop through rows example

$field2 = $_POST['friend'];
$field3   = $_POST['zip'];
$field4   = $_POST['grade1'];
$field5   = $_POST['grade2'];
$field6   = $_POST['grade3'];
$field7 = $_POST['grade4'];



$field4 = (int)$field4;
$field5 = (int)$field5;
$field6 = (int)$field6;
$field7 = (int)$field7;

    
$total = 4;
$sum = $field4 + $field5 + $field6 + $field7;       
$average = $sum/$total;

$grade = "null";


if ($average <= 1)  {
$grade = "F";
  echo "Your friend has a grade of <strong>F.</strong> ";
  
} else if ($average > 1 && $average <= 2) { 
  $grade = "D";
  echo "Your friend has a grade of <strong>D.</strong> ";

} else if ($average > 2 && $average <= 3) { 
   $grade = "C";
  echo "Your friend has a grade of <strong>C.</strong> ";

} else if ($average > 3 && $average <= 4) {
   $grade = "B"; 
  echo "Your friend has a grade of <strong>B.</strong> ";

} else if ($average > 4 && $average <= 5)  {
   $grade = "A";
  echo "Your friend has a grade of <strong>A.</strong> ";

} 

if(isset($_COOKIE['guest_cookie'])) { 
$cookie_ip = $_COOKIE['guest_cookie']; 
$user_ip = $_SERVER['REMOTE_ADDR']; 
if ($cookie_ip == $user_ip) { 
  echo "you have already voted for this friend"; 
} else { 
  
if(isset($_POST['Submit'])){
/* mysql_query("INSERT INTO friendgrade (grade , friend,zip,loyalty,courtesy , stability, attitude)VALUES('$grade', '$field2','$field3','$field4','$field5','$field6' , '$field7')");*/
UPDATE friendgrade
SET courtesy=courtesy + $field4, stability=stability + $field5, loyalty=loyalty+$field6, 
attitude=attitude+$field7
WHERE $field2=$field2  
}//end isset

  }//end else
  
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update Statement</title>
<style type="text/css">
<!--
.style1 {color: #990000}
.style2 {color: #0066FF}
.style3 {color: #0000CC}
.style4 {color: #993399}
-->
</style>
</head>

<body>

</p>
<div align="center">WELCOME TO THE FRIENDGRADER</div>
<p>Please enter a friend, their zip code, and rate them with the following criteria.<span class="style4"></span><br />
</p>
<form id="form1" name="form1" method="post" action="">

  <table border="0">
    
    <tr>
      <td>Friend</td>
      <td>
        <input name="friend" type="text" id="friend" /></td>
    </tr>
    <tr>
      <td>zip code</td>
      <td>
       <input name="zip" type="text" id="zip" />
      </td>
    </tr>
    <tr>
      <td>courtesy</td>
      <td><table>
        <tr>
          <td> 
            <input type="radio" name="grade1" value="1" id="RadioGroup1_0" />
            very poor
            <input type="radio" name="grade1" value="2" id="RadioGroup1_1" />
poor
<input type="radio" name="grade1" value="3" id="RadioGroup1_2" />
ok
<input type="radio" name="grade1" value="4" id="RadioGroup1_3" />
good
<input type="radio" name="grade1" value="5" id="RadioGroup1_4" />
excellent
</td>
        </tr>
       
      </table>
      </td>
    </tr>
   
   
    <tr>
      <td>stability</td>
      <td><input type="radio" name="grade2" id="very_poor3" value="1" />
very poor
  <input type="radio" name="grade2" id="poor3" value="2" />
poor
<input type="radio" name="grade2" id="ok3" value="3" />
ok
<input type="radio" name="grade2" id="good3" value="4" />
good
<input type="radio" name="grade2" id="excellent3" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>loyalty</td>
      <td><input type="radio" name="grade3" id="very_poor4" value="1" />
very poor
  <input type="radio" name="grade3" id="poor4" value="2" />
poor
<input type="radio" name="grade3" id="ok4" value="3" />
ok
<input type="radio" name="grade3" id="good4" value="4" />
good
<input type="radio" name="grade3" id="excellent4" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>attitude</td>
      <td><input type="radio" name="grade4" id="very_poor5" value="1" />
very poor
  <input type="radio" name="grade4" id="poor5" value="2" />
poor
<input type="radio" name="grade4" id="ok5" value="3" />
ok
<input type="radio" name="grade4" id="good5" value="4" />
good
<input type="radio" name="grade4" id="excellent5" value="5" />
excellent</td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" name="Submit" value="Submit" /></td>
    </tr>
  </table>
   
</form>


</body>
</html>

kickstart · January 18, 2010

Hi

Your update needs to be something like this:-

mysql_query("UPDATE friendgrade SET courtesy = courtesy + $field4 , stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7 WHERE friend = '$field2'");

However your page doesn't really hang together. It will only ever output / update anything if $_COOKIE['guest_cookie'] is set. You appear to have a } missing, but not sure what you want to happen if $_COOKIE['guest_cookie'] isn't set

All the best

Keith

silverglade · January 18, 2010

thanks, i updated it with that update statement, but the data wont go into the database

any more help greatly appreciated thanks for helping. here is the new code

<?php
include("connect1.php");

////////////////////////////////////////
////////////////////////////////////////


// escape username and password for use in SQL//person said on board "looks fine" like this
//to prevent sql injections

$field2 = mysql_real_escape_string($field2);
$field3 = mysql_real_escape_string($field3);


/// query db and loop through rows example

$field2 = $_POST['friend'];
$field3   = $_POST['zip'];
$field4   = $_POST['grade1'];
$field5   = $_POST['grade2'];
$field6   = $_POST['grade3'];
$field7 = $_POST['grade4'];



$field4 = (int)$field4;
$field5 = (int)$field5;
$field6 = (int)$field6;
$field7 = (int)$field7;

    






/*if(isset($_COOKIE['guest_cookie'])) { 
$cookie_ip = $_COOKIE['guest_cookie']; 
$user_ip = $_SERVER['REMOTE_ADDR']; 
}
if ($cookie_ip == $user_ip) { 
  echo "you have already voted for this friend"; 
} else { 
  */
if(isset($_POST['Submit'])){
/*mysql_query("INSERT INTO friendgrade (grade , friend,zip,loyalty,courtesy , stability, attitude)VALUES('$grade', '$field2','$field3','$field4','$field5','$field6' , '$field7')");*/



mysql_query("UPDATE friendgrade SET courtesy = courtesy + $field4 ,  stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7 WHERE friend = '$field2'");


// }//end else
  $total = 4;
$sum = $field4 + $field5 + $field6 + $field7;       
$average = $sum/$total;


if ($average <= 1)  {
$grade = "F";
  echo "$field2 has a grade of <strong>F.</strong> ";
  
} else if ($average > 1 && $average <= 2) { 
  $grade = "D";
  echo "$field2 has a grade of <strong>D.</strong> ";

} else if ($average > 2 && $average <= 3) { 
   $grade = "C";
  echo "$field2 has a grade of <strong>C.</strong> ";

} else if ($average > 3 && $average <= 4) {
   $grade = "B"; 
  echo "$field2 has a grade of <strong>B.</strong> ";

} else if ($average > 4 && $average <= 5)  {
   $grade = "A";
  echo "$field2 has a grade of <strong>A.</strong> ";

} 
}//end isset
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update Statement</title>
<style type="text/css">
<!--
.style1 {color: #990000}
.style2 {color: #0066FF}
.style3 {color: #0000CC}
.style4 {color: #993399}
-->
</style>
</head>

<body>

</p>
<div align="center">WELCOME TO THE FRIENDGRADER</div>
<p>Please enter a friend, their zip code, and rate them with the following criteria.<span class="style4"></span><br />
</p>
<form id="form1" name="form1" method="post" action="">

  <table border="0">
    
    <tr>
      <td>Friend</td>
      <td>
        <input name="friend" type="text" id="friend" /></td>
    </tr>
    <tr>
      <td>zip code</td>
      <td>
       <input name="zip" type="text" id="zip" />
      </td>
    </tr>
    <tr>
      <td>courtesy</td>
      <td><table>
        <tr>
          <td> 
            <input type="radio" name="grade1" value="1" id="RadioGroup1_0" />
            very poor
            <input type="radio" name="grade1" value="2" id="RadioGroup1_1" />
poor
<input type="radio" name="grade1" value="3" id="RadioGroup1_2" />
ok
<input type="radio" name="grade1" value="4" id="RadioGroup1_3" />
good
<input type="radio" name="grade1" value="5" id="RadioGroup1_4" />
excellent
</td>
        </tr>
       
      </table>
      </td>
    </tr>
   
   
    <tr>
      <td>stability</td>
      <td><input type="radio" name="grade2" id="very_poor3" value="1" />
very poor
  <input type="radio" name="grade2" id="poor3" value="2" />
poor
<input type="radio" name="grade2" id="ok3" value="3" />
ok
<input type="radio" name="grade2" id="good3" value="4" />
good
<input type="radio" name="grade2" id="excellent3" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>loyalty</td>
      <td><input type="radio" name="grade3" id="very_poor4" value="1" />
very poor
  <input type="radio" name="grade3" id="poor4" value="2" />
poor
<input type="radio" name="grade3" id="ok4" value="3" />
ok
<input type="radio" name="grade3" id="good4" value="4" />
good
<input type="radio" name="grade3" id="excellent4" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>attitude</td>
      <td><input type="radio" name="grade4" id="very_poor5" value="1" />
very poor
  <input type="radio" name="grade4" id="poor5" value="2" />
poor
<input type="radio" name="grade4" id="ok5" value="3" />
ok
<input type="radio" name="grade4" id="good5" value="4" />
good
<input type="radio" name="grade4" id="excellent5" value="5" />
excellent</td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" name="Submit" value="Submit" /></td>
    </tr>
  </table>
   
</form>


</body>
</html>

kickstart · January 18, 2010

Hi

What is $_COOKIE['guest_cookie'] set to?

Use "or die" on the query to see if there are any errors:-

mysql_query("UPDATE friendgrade SET courtesy = courtesy + $field4 , stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7 WHERE friend = '$field2'") or die(mysql_error());

All the best

Keith

silverglade · January 18, 2010

thanks i added the error statement and got no errors. the only time the data entered the database was when i had this line

/*mysql_query("INSERT INTO friendgrade (grade , friend,zip,loyalty,courtesy , stability, attitude)VALUES('$grade', '$field2','$field3','$field4','$field5','$field6' , '$field7')");*/

but i commented it out. because if i have both the insert statement and the update statement, the data will be entered twice i think, i dont know how to prevent that. any more help GREATLY appreciated. thanks for the help. derek

kickstart · January 18, 2010

Hi

An UPDATE updates and existing record, an INSERT puts in a new record. So no you do not want to use both.

Put a few echo statements around the code and see where it is going.

All the best

Keith

silverglade · January 18, 2010

thanks. but if i dont use both, how will i update my existing row when the second user votes and it needs to be added to the existing row?

kickstart · January 18, 2010

Hi

You need to use one or the other depending on whether there is an existing record. Or possibly use ON DUPLICATE KEY UPDATE syntax if the friends name is a key field.

I have cleaned it up a bit (but not tested):-

<?php
include("connect1.php");

/// query db and loop through rows example

$field2 = $_POST['friend'];
$field3   = $_POST['zip'];
$field4   = $_POST['grade1'];
$field5   = $_POST['grade2'];
$field6   = $_POST['grade3'];
$field7 = $_POST['grade4'];

// escape username and password for use in SQL//person said on board "looks fine" like this
//to prevent sql injections
$field2 = mysql_real_escape_string($field2);
$field3 = mysql_real_escape_string($field3);

$field4 = (int)$field4;
$field5 = (int)$field5;
$field6 = (int)$field6;
$field7 = (int)$field7;

    
$total = 4;
$sum = $field4 + $field5 + $field6 + $field7;       
$average = $sum/$total;

$grade = "null";

$ErrOut = '';

if ($average <= 1) 
{
$grade = "F";
$ErrOut .= "Your friend has a grade of <strong>F.</strong><br /> ";
} 
else if ($average > 1 && $average <= 2) 
{ 
$grade = "D";
$ErrOut .= "Your friend has a grade of <strong>D.</strong><br /> ";
} 
else if ($average > 2 && $average <= 3) 
{ 
$grade = "C";
$ErrOut .= "Your friend has a grade of <strong>C.</strong><br /> ";
} 
else if ($average > 3 && $average <= 4) 
{
$grade = "B"; 
$ErrOut .= "Your friend has a grade of <strong>B.</strong><br /> ";
} 
else if ($average > 4 && $average <= 5)  
{
$grade = "A";
$ErrOut .= "Your friend has a grade of <strong>A.</strong><br /> ";
} 

if(isset($_COOKIE['guest_cookie'])) 
{ 
$cookie_ip = $_COOKIE['guest_cookie']; 
$user_ip = $_SERVER['REMOTE_ADDR']; 
if ($cookie_ip == $user_ip) 
{ 
	$ErrOut .= "you have already voted for this friend<br /> ";
} 
else 
{ 
	if(isset($_POST['Submit']))
	{
		mysql_query("INSERT INTO friendgrade (friend, courtesy,stability,loyalty,attitude) VALUES('$field2', $field4 , $field5, $field6, $field7 )  ON DUPLICATE KEY UPDATE courtesy = courtesy + $field4 ,  stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7");
	}//end isset
}//end else
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update Statement</title>
<style type="text/css">
<!--
.style1 {color: #990000}
.style2 {color: #0066FF}
.style3 {color: #0000CC}
.style4 {color: #993399}
-->
</style>
</head>

<body>
<div align="center">WELCOME TO THE FRIENDGRADER</div>
<?php echo $ErrOut; ?>
<p>Please enter a friend, their zip code, and rate them with the following criteria.<span class="style4"></span><br />
</p>
<form id="form1" name="form1" method="post" action="">
	<table border="0">
		<tr>
			<td>Friend</td>
			<td>
				<input name="friend" type="text" id="friend" />
			</td>
		</tr>
		<tr>
			<td>zip code</td>
			<td>
				<input name="zip" type="text" id="zip" />
			</td>
		</tr>
		<tr>
			<td>courtesy</td>
			<td> 
				<input type="radio" name="grade1" value="1" id="RadioGroup1_0" />very poor
				<input type="radio" name="grade1" value="2" id="RadioGroup1_1" />poor
				<input type="radio" name="grade1" value="3" id="RadioGroup1_2" />ok
				<input type="radio" name="grade1" value="4" id="RadioGroup1_3" />good
				<input type="radio" name="grade1" value="5" id="RadioGroup1_4" />excellent
			</td>
		</tr>

		<tr>
			<td>stability</td>
			<td>
				<input type="radio" name="grade2" id="very_poor3" value="1" />very poor
				<input type="radio" name="grade2" id="poor3" value="2" />poor
				<input type="radio" name="grade2" id="ok3" value="3" />ok
				<input type="radio" name="grade2" id="good3" value="4" />good
				<input type="radio" name="grade2" id="excellent3" value="5" />excellent
			</td>
		</tr>
		<tr>
			<td>loyalty</td>
			<td>
				<input type="radio" name="grade3" id="very_poor4" value="1" />very poor
				<input type="radio" name="grade3" id="poor4" value="2" />poor
				<input type="radio" name="grade3" id="ok4" value="3" />ok
				<input type="radio" name="grade3" id="good4" value="4" />good
				<input type="radio" name="grade3" id="excellent4" value="5" />excellent
			</td>
		</tr>
		<tr>
			<td>attitude</td>
			<td>
				<input type="radio" name="grade4" id="very_poor5" value="1" />very poor
				<input type="radio" name="grade4" id="poor5" value="2" />poor
				<input type="radio" name="grade4" id="ok5" value="3" />ok
				<input type="radio" name="grade4" id="good5" value="4" />good
				<input type="radio" name="grade4" id="excellent5" value="5" />excellent
			</td>
		</tr>
		<tr>
			<td> </td>
			<td><input type="submit" name="Submit" value="Submit" /></td>
		</tr>
	</table>
</form>
</body>
</html>

Note I haven't touched the code checking the cookie as I am not that sure what you are trying to do (you never set the cookie, and even if you did it would appear you are restricting the user to update a single friend rather than updating each friend once).

All the best

Keith

silverglade · January 18, 2010

thanks. but the following code, doesnt update the row for some reason.

mysql_query("INSERT INTO friendgrade (friend, courtesy,stability,loyalty,attitude) VALUES('$field2', $field4 , $field5, $field6, $field7 )  ON DUPLICATE KEY UPDATE courtesy = courtesy + $field4 ,  stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7");

kickstart · January 18, 2010

Hi

Is friend set up as a unique field? That syntax relies on the field being declared to be unique.

All the best

Keith

silverglade · January 18, 2010

HAHA KEITH YOU ARE A GENIUS!!! IT WORKED! HAHHA. however there is no code in there to average the new data and change the grade based on the 2 inputs. but im not going to bother you with that youve already done enough. THANK YOU SO MUCH FOR YOUR TIME AND EFFORT!! derek

kickstart · January 18, 2010

Hi

To be honest I think what you need is a bit of a redesign.

Have a table which contains the info it already does, but also the id of the person who is doing the rating (so each "friend" would have multiple rows on that table). Then if they come back to rerate someone you just give them the option to update what is already there.

The averages, etc, and either be worked out in SQL when required, or possibly for speed fed back into your existing table.

All the best

Keith

silverglade · January 18, 2010

sorry to bother again, i think im too much of a newbie to redesign it, but im working on the average of the "friends" data. but it only works once, to make the average . do you know why? i cant figure it out. notice i divided the total by 5 which is the options of 1-5 to get the average. EDIT : NEVERMIND IT WORKS MORE THAN ONCE

mysql_query("INSERT INTO friendgrade (grade, zip, friend, courtesy,stability,loyalty,attitude) VALUES('$grade' , '$field3','$field2', $field4 , $field5, $field6, $field7 )  ON DUPLICATE KEY UPDATE courtesy = courtesy + $field4/5 ,  stability = stability + $field5/5, loyalty = loyalty + $field6/5, attitude = attitude + $field7/5");

silverglade · January 18, 2010

no, it doesnt work, it just keeps adding the numbers or something

kickstart · January 18, 2010

Hi

Err, not sure why you are using 5 as not sure it is going to give you any kind of average.

courtesy = courtesy + $field4/5

If coutesy was currently (say) 10 and $field4 was 5 then this statement would land up with courtesy being 11.

I would guess what you want is the average rating for courtesy. You cannot do that without knowing either what all the ratings are for courtesy, or at a push the current average and the total number of ratings.

All the best

Keith

silverglade · January 18, 2010

I need to know all the vote values, then divide them by the total, to get average

but where do i store all the vote values? I have to store all the votes for each specific friend. but how? man thats a brain cruncher. then i have to be able to update the grade based on all of the averages.any help greatly appreciated.

here is my current code

<?php
include("connect1.php");

////////////////////////////////////////
////////////////////////////////////////


// escape username and password for use in SQL//person said on board "looks fine" like this
//to prevent sql injections

$field2 = mysql_real_escape_string($field2);
$field3 = mysql_real_escape_string($field3);


/// query db and loop through rows example

$field2 = $_POST['friend'];
$field3   = $_POST['zip'];
$field4   = $_POST['grade1'];
$field5   = $_POST['grade2'];
$field6   = $_POST['grade3'];
$field7 = $_POST['grade4'];



$field4 = (int)$field4;
$field5 = (int)$field5;
$field6 = (int)$field6;
$field7 = (int)$field7;

    
$total = 4;
$sum = $field4 + $field5 + $field6 + $field7;       
$average = $sum/$total;

if(isset($_POST['Submit'])){


if ($average <= 1)  {
$grade = "F";
  echo "$field2 has a grade of <strong>F.</strong> ";
  
} else if ($average > 1 && $average <= 2) { 
  $grade = "D";
  echo "$field2 has a grade of <strong>D.</strong> ";

} else if ($average > 2 && $average <= 3) { 
   $grade = "C";
  echo "$field2 has a grade of <strong>C.</strong> ";

} else if ($average > 3 && $average <= 4) {
   $grade = "B"; 
  echo "$field2 has a grade of <strong>B.</strong> ";

} else if ($average > 4 && $average <= 5)  {
   $grade = "A";
  echo "$field2 has a grade of <strong>A.</strong> ";

} 

///you need to know all the vote values, then divide them by the total, to get average
///where do i store all the vote values? you have to store all the votes for each specific friend. but how? then i have to be able to update the grade based on all the averages.

mysql_query("INSERT INTO friendgrade (grade, zip, friend, courtesy,stability,loyalty,attitude) VALUES('$grade' , '$field3','$field2', $field4 , $field5, $field6, $field7 )  ON DUPLICATE KEY UPDATE courtesy = courtesy + $field4,  stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7");


}//end isset

  
  
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update Statement</title>
<style type="text/css">
<!--
.style1 {color: #990000}
.style2 {color: #0066FF}
.style3 {color: #0000CC}
.style4 {color: #993399}
-->
</style>
</head>

<body>

</p>
<div align="center">WELCOME TO THE FRIENDGRADER</div>
<p>Please enter a friend, their zip code, and rate them with the following criteria.<span class="style4"></span><br />
</p>
<form id="form1" name="form1" method="post" action="">

  <table border="0">
    
    <tr>
      <td>Friend</td>
      <td>
        <input name="friend" type="text" id="friend" /></td>
    </tr>
    <tr>
      <td>zip code</td>
      <td>
       <input name="zip" type="text" id="zip" />
      </td>
    </tr>
    <tr>
      <td>courtesy</td>
      <td><table>
        <tr>
          <td> 
            <input type="radio" name="grade1" value="1" id="RadioGroup1_0" />
            very poor
            <input type="radio" name="grade1" value="2" id="RadioGroup1_1" />
poor
<input type="radio" name="grade1" value="3" id="RadioGroup1_2" />
ok
<input type="radio" name="grade1" value="4" id="RadioGroup1_3" />
good
<input type="radio" name="grade1" value="5" id="RadioGroup1_4" />
excellent
</td>
        </tr>
       
      </table>
      </td>
    </tr>
   
   
    <tr>
      <td>stability</td>
      <td><input type="radio" name="grade2" id="very_poor3" value="1" />
very poor
  <input type="radio" name="grade2" id="poor3" value="2" />
poor
<input type="radio" name="grade2" id="ok3" value="3" />
ok
<input type="radio" name="grade2" id="good3" value="4" />
good
<input type="radio" name="grade2" id="excellent3" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>loyalty</td>
      <td><input type="radio" name="grade3" id="very_poor4" value="1" />
very poor
  <input type="radio" name="grade3" id="poor4" value="2" />
poor
<input type="radio" name="grade3" id="ok4" value="3" />
ok
<input type="radio" name="grade3" id="good4" value="4" />
good
<input type="radio" name="grade3" id="excellent4" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>attitude</td>
      <td><input type="radio" name="grade4" id="very_poor5" value="1" />
very poor
  <input type="radio" name="grade4" id="poor5" value="2" />
poor
<input type="radio" name="grade4" id="ok5" value="3" />
ok
<input type="radio" name="grade4" id="good5" value="4" />
good
<input type="radio" name="grade4" id="excellent5" value="5" />
excellent</td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" name="Submit" value="Submit" /></td>
    </tr>
  </table>
   
</form>


</body>
</html>

silverglade · January 18, 2010

someone gave me this code but i dont understand it, could someone please explain it? please. thanks. derek

select AVG(column1 + column2 + ... + columnN) AS av WHERE userid = NN;

silverglade · January 18, 2010

nevermind they said it wouldnt calculate the average.

silverglade · January 18, 2010

hi again, lol. i added a search to display a row based on the input friend name. but nothing prints out, the row doesnt print out. any help greatly appreciated. thanks. derek here is the code

<?php
include("connect1.php");

////////////////////////////////////////
////////////////////////////////////////


// escape username and password for use in SQL//person said on board "looks fine" like this
//to prevent sql injections

$field2 = mysql_real_escape_string($field2);
$field3 = mysql_real_escape_string($field3);


/// query db and loop through rows example

$field2 = $_POST['friend'];
$field3   = $_POST['zip'];
$field4   = $_POST['grade1'];
$field5   = $_POST['grade2'];
$field6   = $_POST['grade3'];
$field7 = $_POST['grade4'];


$find = $_POST['find'];


$field4 = (int)$field4;
$field5 = (int)$field5;
$field6 = (int)$field6;
$field7 = (int)$field7;

    
$total = 4;
$sum = $field4 + $field5 + $field6 + $field7;       
$average = $sum/$total;

if(isset($_POST['Submit'])){


if ($average <= 1)  {
$grade = "F";
  echo "$field2 has a grade of <strong>F.</strong> ";
  
} else if ($average > 1 && $average <= 2) { 
  $grade = "D";
  echo "$field2 has a grade of <strong>D.</strong> ";

} else if ($average > 2 && $average <= 3) { 
   $grade = "C";
  echo "$field2 has a grade of <strong>C.</strong> ";

} else if ($average > 3 && $average <= 4) {
   $grade = "B"; 
  echo "$field2 has a grade of <strong>B.</strong> ";

} else if ($average > 4 && $average <= 5)  {
   $grade = "A";
  echo "$field2 has a grade of <strong>A.</strong> ";

} 

///you need to know all the vote values, then divide them by the total, to get average
///where do i store all the vote values? you have to store all the votes for each specific friend. but how? then i have to be able to update the grade based on all the averages.
/*Quick suggestion without too much thought, would be to have all the "grades" in a separate table and join them on the userid.

id - numeric - [record number]
uid - numeric [FK for user table]
type - char - [code to identify which grade]
value - numeric - [value for the grading]
vid - numeric - [id of voter]


that covers another of your questions. How to stop fake voting.*/

mysql_query("INSERT INTO friendgrade (grade, zip, friend, courtesy,stability,loyalty,attitude) VALUES('$grade' , '$field3','$field2', $field4 , $field5, $field6, $field7 )  ON DUPLICATE KEY UPDATE courtesy = courtesy + $field4,  stability = stability + $field5, loyalty = loyalty + $field6, attitude = attitude + $field7");


}//end isset

  
  
  
  
  if(isset($_POST['submit'])){
  $query="SELECT * FROM friendgrade WHERE name='$find' LIMIT 1";
$result=mysql_query($query);

if(mysql_num_rows($result) > 0)
{
$userinfo = mysql_fetch_array($result);
echo $userinfo;
}
else
{
echo "Invalid username"; // or whatever 
  }

  }//end isset
    
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update Statement</title>
<style type="text/css">
<!--
.style1 {color: #990000}
.style2 {color: #0066FF}
.style3 {color: #0000CC}
.style4 {color: #993399}
-->
</style>
</head>

<body>

</p>
<div align="center">WELCOME TO THE FRIENDGRADER</div>
<p>Please enter a friend, their zip code, and rate them with the following criteria.<span class="style4"></span><br />
</p>
<form id="form1" name="form1" method="post" action="">

  <table border="0">
    
    <tr>
      <td>Friend</td>
      <td>
        <input name="friend" type="text" id="friend" /></td>
    </tr>
    <tr>
      <td>zip code</td>
      <td>
       <input name="zip" type="text" id="zip" />
      </td>
    </tr>
    <tr>
      <td>courtesy</td>
      <td><table>
        <tr>
          <td> 
            <input type="radio" name="grade1" value="1" id="RadioGroup1_0" />
            very poor
            <input type="radio" name="grade1" value="2" id="RadioGroup1_1" />
poor
<input type="radio" name="grade1" value="3" id="RadioGroup1_2" />
ok
<input type="radio" name="grade1" value="4" id="RadioGroup1_3" />
good
<input type="radio" name="grade1" value="5" id="RadioGroup1_4" />
excellent
</td>
        </tr>
       
      </table>
      </td>
    </tr>
   
   
    <tr>
      <td>stability</td>
      <td><input type="radio" name="grade2" id="very_poor3" value="1" />
very poor
  <input type="radio" name="grade2" id="poor3" value="2" />
poor
<input type="radio" name="grade2" id="ok3" value="3" />
ok
<input type="radio" name="grade2" id="good3" value="4" />
good
<input type="radio" name="grade2" id="excellent3" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>loyalty</td>
      <td><input type="radio" name="grade3" id="very_poor4" value="1" />
very poor
  <input type="radio" name="grade3" id="poor4" value="2" />
poor
<input type="radio" name="grade3" id="ok4" value="3" />
ok
<input type="radio" name="grade3" id="good4" value="4" />
good
<input type="radio" name="grade3" id="excellent4" value="5" />
excellent</td>
    </tr>
    <tr>
      <td>attitude</td>
      <td><input type="radio" name="grade4" id="very_poor5" value="1" />
very poor
  <input type="radio" name="grade4" id="poor5" value="2" />
poor
<input type="radio" name="grade4" id="ok5" value="3" />
ok
<input type="radio" name="grade4" id="good5" value="4" />
good
<input type="radio" name="grade4" id="excellent5" value="5" />
excellent</td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" name="Submit" value="Submit" /></td>
    </tr>
  </table>
   
  <table width="335" border="1">
    <tr>
      <td width="325"> search friends
          <input type="text" name="find" id="find" />     </td>
    </tr>
    <tr>
      <td><input type="submit" name="search" id="search" value="search" /></td>
    </tr>
  </table>
</form>


</body>
</html>

kickstart · January 19, 2010

Hi

I think you need to change your table to get the averages. Have a column for whose friend they are and then you can just insert a new row / update a row for each persons friend (so a particular friend could have 10 rows, one for each person they are a friend of).

Then you can use the AVG function to get the average.

All the best

Keith

silverglade · January 19, 2010

awesome thanks very much.. also, do you know why my "display row" search isnt displaying the row for the friend searched? please.

kickstart · January 19, 2010

Hi

Main problem is that you are doing a mysql_fetch_array into $userinfo, so $userinfo will be an array. You can't just echo out an array. You need to either specify the array items to print or use print_r.

Also you should use mysql_real_escape_string on $_POST['find'] / $find to help prevent people wrecking you database using sql injection attacks.

All the best

Keith

silverglade · January 19, 2010

great thanks again, also if i have the following information like you said

dave derek D 010 1 1 1 1

tom derek A 010 5 5 5 5

how would i code that i have no idea, to average the 1 1 1 1 and 5 5 5 5 .

also the following code just prints out INVALID USERNAME.

 if(isset($_POST['submit'])){

  $query = "SELECT grade,friend,zip, loyalty, courtesy, stability, attitude FROM friendgrade WHERE name='$find' LIMIT 1"; 
$result=mysql_query($query);

if(mysql_num_rows($result) > 0)
{
$userinfo = mysql_fetch_array($result);

print_r($array);

}
else
{
echo "Invalid username"; // or whatever 
  }

  }//end if

Sign In

incrementing by number a row with a specific user name in a table row

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

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

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