Update Database Problems

[Guest_imported]

Hi can anyone see what's wrong with the script below. I thought it used to work but I guess not. The variables $rate and $id are sent from a form and then the script below detects whether the user is logged in. If so it then carries out the relevant coding. The problem is that when the database is updated it doesn't update the correct information.

If anyone could help, i'd be greatful. Thanx

<?php
require(&quot;../config.inc.php&quot;
$pagetitle = &quot;New Found Punk - Band Database - Rate Tab&quot;;
require(&quot;../style.php&quot;

$db = mysql_connect($host,$login,$pass);
mysql_select_db($base,$db);

if($xmbuser) {
$sql = &quot;UPDATE tabs_tbl set nbr_votes = nbr_votes+1 where id = $id&quot;;
$req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

$sql = &quot;UPDATE tabs_tbl set vote_tot = vote_tot+'.$rate.' where id = $id&quot;;
$req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

$sql = &quot;SELECT id,vote_avg,vote_tot,nbr_votes FROM tabs_tbl where id = $id&quot;;
$req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
while($data = mysql_fetch_array($req))

$vote_avg = round($data['vote_tot']/$data['nbr_votes'],3);

$sql = &quot;UPDATE tabs_tbl set vote_avg = '.$vote_avg.' where id = $id&quot;;
$req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

{
echo '<table border=&quot;0&quot; width=&quot;100%&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot; height=&quot;100%&quot;><tr><td width=&quot;100%&quot; valign=&quot;middle&quot; align=&quot;center&quot;><font face=&quot;Verdana&quot; size=&quot;2&quot;>Thankyou for your vote. Click <a href=&quot;javascript: window.close();&quot;>here</a> to close this window</font></td></tr></table>';
exit;
}

} else {
echo '<table border=&quot;0&quot; width=&quot;100%&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot; height=&quot;100%&quot;><tr><td width=&quot;100%&quot; valign=&quot;middle&quot; align=&quot;center&quot;><font face=&quot;Verdana&quot; size=&quot;2&quot;>You\'re not a registered member of the site. Click <a href=&quot;javascript: window.close();&quot;>here</a> to close this window</font></td></tr></table>';
exit;
}

mysql_close($db);
?>

 

[sleipnir214]

Could you give more details about the script's unexpected behavior, please? ______________________________________________________________________
TANSTAAFL!

 

[Guest_imported]

Yep sure

$rate is a value from (1-10).

bands_tbl:
id = the id
vote_tot = the total of all the votes
nbr_votes = the number of votes
vote_avg = the average score per vote

The nbr_votes updates correctly but the vote_tot also just adds 1 when it should be doing the current vote_tot + the variable $rate sent from the form. The vote_avg is currently being updated as 0 when that should be the vote_tot devided by the nbr_votes.

I don't know why it's doing it because it looks alright to me. If you could help, i'd appreciate it cheers

 

[Guest_imported]

one mistake on the top bit should have put tabs_tbl not bands_tbl.

 

[sleipnir214]

The problem with your code is that your quote marks don't match in your statements where you are concatentating your strings

In this line:
$sql = &quot;UPDATE tabs_tbl set vote_tot = vote_tot+'.$rate.' where id = $id&quot;;

The quotes near $rate don't match the quotes at the beginning and end of the string, so PHP doesn't perform the concatenation you think it does.

Print out your $sql variable before you use it in your mysql_query() invokation to make sure it looks like what you expect.


I have a strong recommendation about your SQL statements in general: Combine all the UPDATE statements into one statement. Otherwise, you might end up with a concurrency issues:

$sql =
'UPDATE
	tabs_tbl
 SET
	nbr_votes = nbr_votes + 1,
	vote_tot = vote_tot + ' . $rate . ',
	vote_avg = if(vote_tot = 0, 0, nbr_votes/vote_tot)
 WHERE
    id = $id';

______________________________________________________________________
TANSTAAFL!

 

[danielhozac]

Change

   $sql = "UPDATE tabs_tbl set nbr_votes = nbr_votes+1 where id = $id";
    $req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

    $sql = &quot;UPDATE tabs_tbl set vote_tot = vote_tot+'.$rate.' where id = $id&quot;;
    $req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

to

   $sql = "UPDATE tabs_tbl set nbr_votes = nbr_votes+1, vote_tot = vote_tot + " .$rate." where id = $id";
    $req = mysql_query($sql) or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());

This will
1) be more efficient
2) fix an error in your query
The error was that you used '.$rate.' which was added to vote_tot as '.value of rate.', which, of course, would fail. //Daniel

 

[Guest_imported]

Cheers, I've fixed that now, I think the problem with the vote_avg may be because of the properties I have for that field in my table. It's currently set to Decimal (10,0)

I don't know what that means because someone else did my table structure for me. But could you tell me the correct settings. Thanx