What's wrong with this code???

[Richzilla]

I'm trying to get this page to automatically divide one of the columns by another for all values. I want it to scroll through each row and do the calculation and finally add this value to a new column. So far I haven't managed it.

 

Any help would be great. I've been trying to get this to work ofr ages now!!

 

$query = "SELECT * FROM Mixes";
$result=mysql_query($query);
$num=mysql_numrows($result);
if ($rating_total != 0){ 
$i=0;
while ($i < num) {
$ratings=mysql_result($result,$i,"total_votes");
$ratings_made=mysql_result($result,$i,"total_voted");
$rating = ($ratings / $ratings_made);
$query = "UPDATE * Mixes SET rating = '$rating' ";
$result=mysql_query($query);

$i++;
}}
?>

[Garath531]

 

 

$query = "SELECT * FROM Mixes";
$result=mysql_query($query);
$num=mysql_numrows($result);
if ($rating_total != 0){ 
$i=0;
while ($i < $num) {
$ratings=mysql_result($result,$i,"total_votes");
$ratings_made=mysql_result($result,$i,"total_voted");
$rating = ($ratings / $ratings_made);
$query = "UPDATE * Mixes SET rating = '$rating' ";
$result=mysql_query($query);

$i++;
}}
?>

 

You forgot the dollar sign in the while statement.

[Richzilla]

I can't see it? Are you sure?

[Garath531]

Yours.

$query = "SELECT * FROM Mixes";
$result=mysql_query($query);
$num=mysql_numrows($result);
if ($rating_total != 0){ 
$i=0;
while ($i < num) {
$ratings=mysql_result($result,$i,"total_votes");
$ratings_made=mysql_result($result,$i,"total_voted");
$rating = ($ratings / $ratings_made);
$query = "UPDATE * Mixes SET rating = '$rating' ";
$result=mysql_query($query);

$i++;
}}
?>

 

Mine.

$query = "SELECT * FROM Mixes";
$result=mysql_query($query);
$num=mysql_numrows($result);
if ($rating_total != 0){ 
$i=0;
while ($i < $num) {
$ratings=mysql_result($result,$i,"total_votes");
$ratings_made=mysql_result($result,$i,"total_voted");
$rating = ($ratings / $ratings_made);
$query = "UPDATE * Mixes SET rating = '$rating' ";
$result=mysql_query($query);

$i++;
}}
?>

[kenrbnsn]

I would use one of the mysql_fetch function to make your code cleaner:

<?php
$query = "SELECT * FROM Mixes";
$result=mysql_query($query) or die("Problem selecting, query: $query<br>" . mysql_error());
if ($rating_total != 0){ 
  while ($rw = mysql_fetch_assoc($result)) {
      $ratings=$rw['total_votes'];
      $ratings_made=$rw['total_voted'];
      $rating = ($ratings / $ratings_made);
      $query = "UPDATE Mixes SET rating = '$rating' "; // this is going to set all the "rating" field of all the rows in your table to the same number.
      $rs2=mysql_query($query) or die("Problem updating, query: $query<br>" . mysql_error());
  }
}
?>

 

Ken

[Richzilla]

sadly neither version works for me. How can i get each row in the table to have the calculation for that row added to the rating column?

[kenrbnsn]

Do you have a field whose value uniquely identifies each row?

 

Ken

[boo_lolly]

sadly neither version works for me. How can i get each row in the table to have the calculation for that row added to the rating column?

 

you need an AUTO_INCREMENTing 'id' column in your table. you can then do this:

<?php
        $sql = "
                SELECT
                        *
                FROM
                        Mixes
        ";
        $query = mysql_query($sql) or die("Problem selecting, query: $query<br>" . mysql_error());

         while ($row = mysql_fetch_array($query)){
                $avg = ($row['total_votes'] / $row['total_voted']);
                $update = "
                        UPDATE
                                Mixes
                        SET
                                rating = '{$avg}'
                        WHERE
                                id = '{$row['id']}
                ";
                mysql_query($update) or die("Problem updating, query: $query<br>" . mysql_error());
        }
?>

[Richzilla]

Now that looks much more like what i need. I do have an autoincrement id tag for all items in the database. the only problem now is that some of the ratings are blank, so the script is trying to divide by zero, which freak it out. I have ammended the code to this, but it doesn't work -

 

        $sql = "SELECT * FROM Mixes";
        $query = mysql_query($sql) or die("Problem selecting, query: $query<br>" . mysql_error());
if ($rating_total != 0){ 
         while ($row = mysql_fetch_array($query)){
                $avg = ($row['total_votes'] / $row['total_voted']);
                $update = "
                        UPDATE
                                Mixes
                        SET
                                rating = '{$avg}'
                        WHERE
                                id = '{$row['id']}
                ";
                mysql_query($update) or die("Problem updating, query: $query<br>" . mysql_error());
        }}
?>

[boo_lolly]

there's a problem with your code. first, you're not declaring what $rating_total is. second, you're not going to get the value you need unless you're inside the while loop. i think this is what you're looking for:

<?php
        $sql = "
                SELECT
                        *
                FROM
                        Mixes
        ";
        $query = mysql_query($sql) or die("Problem selecting, query: $query<br>" . mysql_error());

         while ($row = mysql_fetch_array($query)){
                if($row['total_votes'] !== 0){
                        $avg = ($row['total_votes'] / $row['total_voted']);
                        $update = "
                                UPDATE
                                        Mixes
                                SET
                                        rating = '{$avg}'
                                WHERE
                                        id = '{$row['id']}
                        ";
                        mysql_query($update) or die("Problem updating, query: $query<br>" . mysql_error());
                }
        }
?>

[Richzilla]

really appreciate your help here boo. I bow to your superior programming. I'm still very new to all thsi and am quite limited in my use of the corect syntaxes. The code you have supplied is giving me a problem -

 

Problem updating, query: Resource id #3

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 ''1' at line 1

 

<?php
$user="xxxxx";
$pass="xxxx";
$host = "xxxx";
$dbase="xxxx";
$table = "Mixes";
mysql_connect($host,$user,$pass);
@mysql_select_db($dbase) or die("Unable to select database");
        $sql = "SELECT * FROM Mixes";
        $query = mysql_query($sql) or die("Problem selecting, query: $query<br>" . mysql_error());

         while ($row = mysql_fetch_array($query)){
                if($row['total_votes'] !== 0){
                        $avg = ($row['total_votes'] / $row['total_voted']);
                        $update = "UPDATE Mixes SET rating = '{$avg}' WHERE id = '{$row['id']}";
                        mysql_query($update) or die("Problem updating, query: $query<br>" . mysql_error());
                }
        }
?>

[boo_lolly]

change it to this:

<?php
$user="xxxxx";
$pass="xxxx";
$host = "xxxx";
$dbase="xxxx";
$table = "Mixes";
mysql_connect($host,$user,$pass);
@mysql_select_db($dbase) or die("Unable to select database");
        $sql = "SELECT * FROM Mixes";
        $query = mysql_query($sql) or die("Problem selecting, query: $sql<br>" . mysql_error());

         while ($row = mysql_fetch_array($query)){
                if($row['total_votes'] !== 0){
                        $avg = ($row['total_votes'] / $row['total_voted']);
                        $update = "UPDATE Mixes SET rating = '{$avg}' WHERE id = '{$row['id']}";
                        mysql_query($update) or die("Problem updating, query: $update<br>" . mysql_error());
                }
        }
?>

 

that won't solve the problem, but it will give you the correct error. and then we can go from there.

[Richzilla]

I have  had a good play around with the code and i'm still having issues. i have added a little more formatting to aviod problems, but it still is not giving me any love. Here's the error code -

 

Problem updating, query: UPDATE Mixes SET rating = '4.50' WHERE id = '24

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 ''24' at line 1

 

Why is not updating my database? It has the correct value, but it's not uploading it.

 

Here's the code below

 

<?php
$user="xxxx";
$pass="xxxx";
$host = "xxxx";
$dbase="jungletekn";
$table = "Mixes";
mysql_connect($host,$user,$pass);
@mysql_select_db($dbase) or die("Unable to select database");
        $sql = "SELECT * FROM Mixes ORDER BY total_voted DESC";
        $query = mysql_query($sql) or die("Problem selecting, query: $sql<br>" . mysql_error());

         while ($row = mysql_fetch_array($query)){
                if($row['total_voted'] !== 0){
                        $avg = ($row['total_votes'] / $row['total_voted']);
					$rnd_avg = number_format($avg, 2, '.', ''); 
                        $update = "UPDATE Mixes SET rating = '{$rnd_avg}' WHERE id = '{$row['id']}";
                        mysql_query($update) or die("Problem updating, query: $update<br>" . mysql_error());
                }
        }
?>

[boo_lolly]

the problem is this line:

 $update = "UPDATE Mixes SET rating = '{$rnd_avg}' WHERE id = '{$row['id']}"

 

i missed a close single quote at the end. change it to this:

 $update = "UPDATE Mixes SET rating = '{$rnd_avg}' WHERE id = '{$row['id']}"'

[Richzilla]

That code is not quite right as the quote is in the wrong place. I've changed it to this, however it has issues.

 

Warning: Division by zero

 

 $update = "UPDATE Mixes SET rating = '{$rnd_avg}' WHERE id = '{$row['id']}'";

[boo_lolly]

That code is not quite right as the quote is in the wrong place. I've changed it to this, however it has issues.

 

Warning: Division by zero

 

 $update = "UPDATE Mixes SET rating = '{$rnd_avg}' WHERE id = '{$row['id']}'";

 

then the problem is with your if statement. the if statement is supposed to check for a zero before it divides. which value is supposed to be divided by another value?